Newer
■
■
■
■
■
■
■
■
■
■
Older
56 lines
|
2.285kb
| 1 |
# Table: `dhcp_leases` |
|
| 2 | ||
| 3 |
Stores observed DHCP lease/reservation data. |
|
| 4 | ||
| 5 |
Rows are populated by the DHCP push collector endpoint `POST /api/collect/dhcp-leases`, normally called from the MikroTik `lease-script` on `192.168.2.1`. |
|
| 6 | ||
| 7 |
## Columns |
|
| 8 | ||
| 9 |
| Column | Type | Null | Default | Notes | |
|
| 10 |
|--------|------|------|---------|-------| |
|
| 11 |
| `lease_key` | `TEXT` | no | none | Stable lease observation key. Primary key. | |
|
| 12 |
| `worker_id` | `TEXT` | no | none | Collector worker. References `data_workers(worker_id)`. | |
|
| 13 |
| `host_fqdn` | `TEXT` | yes | `NULL` | Matched host if known. References `hosts(fqdn)`. | |
|
| 14 |
| `observed_name` | `TEXT` | no | `''` | Name reported by DHCP. | |
|
| 15 |
| `ip_address` | `TEXT` | no | none | Observed IP address. | |
|
| 16 |
| `mac_address` | `TEXT` | no | `''` | Observed MAC address. | |
|
| 17 |
| `lease_state` | `TEXT` | no | `''` | DHCP state, if known. | |
|
| 18 |
| `first_seen` | `TEXT` | no | none | First observation timestamp. | |
|
| 19 |
| `last_seen` | `TEXT` | no | none | Last observation timestamp. | |
|
| 20 |
| `raw` | `TEXT` | no | `''` | Raw source payload or diagnostic text. | |
|
| 21 | ||
| 22 |
## Keys And Indexes |
|
| 23 | ||
| 24 |
- Primary key: `lease_key` |
|
| 25 |
- `idx_dhcp_leases_ip` on `ip_address` |
|
| 26 |
- `idx_dhcp_leases_mac` on `mac_address` |
|
| 27 |
- `idx_dhcp_leases_worker_last_seen` on `(worker_id, last_seen)` |
|
| 28 | ||
| 29 |
## Relationships |
|
| 30 | ||
| 31 |
- `worker_id` references `data_workers(worker_id)` with `ON UPDATE CASCADE ON DELETE RESTRICT` |
|
| 32 |
- `host_fqdn` references `hosts(fqdn)` with `ON UPDATE CASCADE ON DELETE SET NULL` |
|
| 33 | ||
| 34 |
## Definition |
|
| 35 | ||
| 36 |
```sql |
|
| 37 |
CREATE TABLE IF NOT EXISTS dhcp_leases ( |
|
| 38 |
lease_key TEXT PRIMARY KEY, |
|
| 39 |
worker_id TEXT NOT NULL, |
|
| 40 |
host_fqdn TEXT, |
|
| 41 |
observed_name TEXT NOT NULL DEFAULT '', |
|
| 42 |
ip_address TEXT NOT NULL, |
|
| 43 |
mac_address TEXT NOT NULL DEFAULT '', |
|
| 44 |
lease_state TEXT NOT NULL DEFAULT '', |
|
| 45 |
first_seen TEXT NOT NULL, |
|
| 46 |
last_seen TEXT NOT NULL, |
|
| 47 |
raw TEXT NOT NULL DEFAULT '', |
|
| 48 |
FOREIGN KEY (worker_id) REFERENCES data_workers(worker_id) ON UPDATE CASCADE ON DELETE RESTRICT, |
|
| 49 |
FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE SET NULL |
|
| 50 |
); |
|
| 51 | ||
| 52 |
CREATE INDEX IF NOT EXISTS idx_dhcp_leases_ip ON dhcp_leases(ip_address); |
|
| 53 |
CREATE INDEX IF NOT EXISTS idx_dhcp_leases_mac ON dhcp_leases(mac_address); |
|
| 54 |
CREATE INDEX IF NOT EXISTS idx_dhcp_leases_worker_last_seen |
|
| 55 |
ON dhcp_leases(worker_id, last_seen); |
|
| 56 |
``` |