1 contributor
Table: mdns_observations
Stores observed mDNS records.
Columns
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
observation_key |
TEXT |
no | none | Stable 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 | none | Observed mDNS name. |
ip_address |
TEXT |
no | none | Observed IP address. |
rr_type |
TEXT |
no | 'A' |
DNS record type. |
ttl |
INTEGER |
no | 0 |
Observed TTL. |
first_seen |
TEXT |
no | none | First observation timestamp. |
last_seen |
TEXT |
no | none | Last observation timestamp. |
seen_count |
INTEGER |
no | 1 |
Number of times observed. |
last_peer |
TEXT |
no | '' |
Last sender peer. |
raw |
TEXT |
no | '' |
Raw source payload or diagnostic text. |
Keys And Indexes
- Primary key:
observation_key idx_mdns_observations_nameonobserved_nameidx_mdns_observations_iponip_addressidx_mdns_observations_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 mdns_observations (
observation_key TEXT PRIMARY KEY,
worker_id TEXT NOT NULL,
host_fqdn TEXT,
observed_name TEXT NOT NULL,
ip_address TEXT NOT NULL,
rr_type TEXT NOT NULL DEFAULT 'A',
ttl INTEGER NOT NULL DEFAULT 0,
first_seen TEXT NOT NULL,
last_seen TEXT NOT NULL,
seen_count INTEGER NOT NULL DEFAULT 1,
last_peer TEXT NOT NULL DEFAULT '',
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_mdns_observations_name ON mdns_observations(observed_name);
CREATE INDEX IF NOT EXISTS idx_mdns_observations_ip ON mdns_observations(ip_address);
CREATE INDEX IF NOT EXISTS idx_mdns_observations_worker_last_seen
ON mdns_observations(worker_id, last_seen);