tem_dirsearch

Column	Type
uniform_resource_id	TEXT
device_id	TEXT
tenant_id	TEXT
tenant_name	TEXT
ur_ingest_session_id	TEXT
uri	TEXT
observed_at
status_code
content_type
content_length
redirect_url
discovered_url

SQL DDL

CREATE VIEW tem_dirsearch AS
SELECT
    ur.uniform_resource_id,
    ur.device_id,
    t.tenant_id,
    t.tenant_name,
    ts.ur_ingest_session_id,
    ur.uri,
    json_extract(ur.content,   '$.info.time')      AS observed_at,
    json_extract(result.value, '$.status')         AS status_code,
    json_extract(result.value, '$.content-type')   AS content_type,
    json_extract(result.value, '$.content-length') AS content_length,
    json_extract(result.value, '$.redirect')       AS redirect_url,
    json_extract(result.value, '$.url')            AS discovered_url
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
     -- Expand `results` array into individual rows
     JOIN json_each(json_extract(ur.content, '$.results')) AS result
WHERE ur.uri LIKE '%dirsearch%'
  AND ur.nature = 'json'