tem_dnsx_result

Column	Type
uniform_resource_id	TEXT
tenant_id	TEXT
tenant_name	TEXT
ur_ingest_session_id	TEXT
host
ttl
resolver
ip_address
status_code
timestamp

SQL DDL

CREATE VIEW tem_dnsx_result AS
SELECT
    ur.uniform_resource_id,
    t.tenant_id,
    t.tenant_name,
    ts.ur_ingest_session_id,
    json_extract(ur.content, '$.host') AS host,
    json_extract(ur.content, '$.ttl') AS ttl,
    json_extract(ur.content, '$.resolver[0]') AS resolver,
    json_extract(ur.content, '$.a[0]') AS ip_address,
    json_extract(ur.content, '$.status_code') AS status_code,
    json_extract(ur.content, '$.timestamp') AS timestamp
FROM uniform_resource ur
INNER JOIN tem_tenant t ON t.device_id = ur.device_id
INNER JOIN tem_session ts ON ur.device_id = ts.device_id
WHERE ur.nature = 'jsonl'
AND ur.uri LIKE '%/dnsx/%'