tem_testssl_server_default

Column	Type
uniform_resource_id	TEXT
tenant_id	TEXT
tenant_name	TEXT
ur_ingest_session_id	TEXT
host
id
severity
finding
uri	TEXT

SQL DDL

CREATE VIEW tem_testssl_server_default AS
SELECT
    ur.uniform_resource_id,
    t.tenant_id,
    t.tenant_name,
    ts.ur_ingest_session_id,
    json_extract(sd.value, '$.targetHost')   AS host,
    json_extract(sd.value, '$.id')           AS id,
    json_extract(sd.value, '$.severity')     AS severity,
    json_extract(sd.value, '$.finding')      AS finding,
    ur.uri
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
JOIN json_each(ur.content, '$.scanResult') s
JOIN json_each(s.value, '$.serverDefaults') sd
WHERE ur.uri LIKE '%testssl%'
  AND ur.nature = 'json' AND json_extract(s.value, '$.targetHost') NOT NULL