1 contributor
Table: dhcp_leases
Stores observed DHCP lease/reservation data.
Columns
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
lease_key |
TEXT |
no | none | Stable lease observation key. Primary key. |
worker_id |
TEXT |
no | none | Collector worker. References data_workers(worker_id). |
host_fqdn |
TEXT |
yes | NULL |
Matched host if known. References hosts(fqdn). |
observed_name |
TEXT |
no | '' |
Name reported by DHCP. |
ip_address |
TEXT |
no | none | Observed IP address. |
mac_address |
TEXT |
no | '' |
Observed MAC address. |
lease_state |
TEXT |
no | '' |
DHCP state, if known. |
first_seen |
TEXT |
no | none | First observation timestamp. |
last_seen |
TEXT |
no | none | Last observation timestamp. |
raw |
TEXT |
no | '' |
Raw source payload or diagnostic text. |
Keys And Indexes
- Primary key:
lease_key idx_dhcp_leases_iponip_addressidx_dhcp_leases_maconmac_addressidx_dhcp_leases_worker_last_seenon(worker_id, last_seen)
Relationships
worker_idreferencesdata_workers(worker_id)withON UPDATE CASCADE ON DELETE RESTRICThost_fqdnreferenceshosts(fqdn)withON UPDATE CASCADE ON DELETE SET NULL
Definition
CREATE TABLE IF NOT EXISTS dhcp_leases (
lease_key TEXT PRIMARY KEY,
worker_id TEXT NOT NULL,
host_fqdn TEXT,
observed_name TEXT NOT NULL DEFAULT '',
ip_address TEXT NOT NULL,
mac_address TEXT NOT NULL DEFAULT '',
lease_state TEXT NOT NULL DEFAULT '',
first_seen TEXT NOT NULL,
last_seen TEXT NOT NULL,
raw TEXT NOT NULL DEFAULT '',
FOREIGN KEY (worker_id) REFERENCES data_workers(worker_id) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_dhcp_leases_ip ON dhcp_leases(ip_address);
CREATE INDEX IF NOT EXISTS idx_dhcp_leases_mac ON dhcp_leases(mac_address);
CREATE INDEX IF NOT EXISTS idx_dhcp_leases_worker_last_seen
ON dhcp_leases(worker_id, last_seen);