tem_nmap

Column	Type
uniform_resource_id	TEXT
tenant_id	TEXT
tenant_name	TEXT
ur_ingest_session_id	TEXT
host_ip
protocol
port
state
service_name
service_product
service_version
service_extrainfo
tool_name
uri	TEXT

SQL DDL

CREATE VIEW tem_nmap AS
SELECT
    ur.uniform_resource_id,
    t.tenant_id,
    t.tenant_name,
    ts.ur_ingest_session_id,
    json_extract(urt.content, '$.nmaprun.host[0].address.@addr') AS host_ip,
    json_extract(urt.content, '$.nmaprun.host[0].ports.port[0].@protocol') AS protocol,
    json_extract(urt.content, '$.nmaprun.host[0].ports.port[0].@portid') AS port,
    json_extract(urt.content, '$.nmaprun.host[0].ports.port[0].state.@state') AS state,
    json_extract(urt.content, '$.nmaprun.host[0].ports.port[0].service.@name') AS service_name,
    json_extract(urt.content, '$.nmaprun.host[0].ports.port[0].service.@product') AS service_product,
    json_extract(urt.content, '$.nmaprun.host[0].ports.port[0].service.@version') AS service_version,
    json_extract(urt.content, '$.nmaprun.host[0].ports.port[0].service.@extrainfo') AS service_extrainfo,
    'nmap' AS tool_name,
    ur.uri
FROM uniform_resource ur
INNER JOIN uniform_resource_transform urt
       ON ur.uniform_resource_id = urt.uniform_resource_id
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.uri LIKE '%nmap%'
  AND ur.uri NOT LIKE '%nmap_targets%'