tem_testssl_general

Column	Type
uniform_resource_id	TEXT
tenant_id	TEXT
tenant_name	TEXT
ur_ingest_session_id	TEXT
invocation
version
openssl
start_time
host
ip
port
rdns
service
uri	TEXT

SQL DDL

CREATE VIEW tem_testssl_general AS
SELECT
    ur.uniform_resource_id,
    t.tenant_id,
    t.tenant_name,
    ts.ur_ingest_session_id,
    json_extract(ur.content, '$.Invocation') AS invocation,
    json_extract(ur.content, '$.version') AS version,
    json_extract(ur.content, '$.openssl') AS openssl,
    json_extract(ur.content, '$.startTime') AS start_time,
    json_extract(s.value, '$.targetHost') AS host,
    json_extract(s.value, '$.ip')         AS ip,
    json_extract(s.value, '$.port')       AS port,
    json_extract(s.value, '$.rDNS')       AS rdns,
    json_extract(s.value, '$.service')    AS service,
    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
  WHERE ur.uri LIKE '%testssl%' AND ur.nature = 'json' AND json_extract(s.value, '$.targetHost') NOT NULL