tem_tlsx_certificate

Column	Type
uniform_resource_id	TEXT
tenant_id	TEXT
tenant_name	TEXT
ur_ingest_session_id	TEXT
uri	TEXT
observed_at
host
ip_address
port
probe_status
tls_version
cipher_suite
is_self_signed
is_mismatched
valid_from
valid_until
subject_dn
subject_cn
subject_alt_names
serial_number
issuer_dn
issuer_cn
fingerprint_md5
fingerprint_sha1
fingerprint_sha256
tls_connection
sni

SQL DDL

CREATE VIEW tem_tlsx_certificate AS
SELECT
    ur.uniform_resource_id,
    t.tenant_id,
    t.tenant_name,
    ts.ur_ingest_session_id,
    ur.uri,
    json_extract(ur.content, '$.timestamp')                 AS observed_at,
    json_extract(ur.content, '$.host')                      AS host,
    json_extract(ur.content, '$.ip')                        AS ip_address,
    json_extract(ur.content, '$.port')                      AS port,
    json_extract(ur.content, '$.probe_status')              AS probe_status,
    json_extract(ur.content, '$.tls_version')               AS tls_version,
    json_extract(ur.content, '$.cipher')                    AS cipher_suite,
    json_extract(ur.content, '$.self_signed')               AS is_self_signed,
    json_extract(ur.content, '$.mismatched')                AS is_mismatched,
    json_extract(ur.content, '$.not_before')                AS valid_from,
    json_extract(ur.content, '$.not_after')                 AS valid_until,
    json_extract(ur.content, '$.subject_dn')                AS subject_dn,
    json_extract(ur.content, '$.subject_cn')                AS subject_cn,
    json_extract(ur.content, '$.subject_an')                AS subject_alt_names,
    json_extract(ur.content, '$.serial')                    AS serial_number,
    json_extract(ur.content, '$.issuer_dn')                 AS issuer_dn,
    json_extract(ur.content, '$.issuer_cn')                 AS issuer_cn,
    json_extract(ur.content, '$.fingerprint_hash.md5')      AS fingerprint_md5,
    json_extract(ur.content, '$.fingerprint_hash.sha1')     AS fingerprint_sha1,
    json_extract(ur.content, '$.fingerprint_hash.sha256')   AS fingerprint_sha256,
    json_extract(ur.content, '$.tls_connection')            AS tls_connection,
    json_extract(ur.content, '$.sni')                       AS sni
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.uri LIKE '%tlsx%'
  AND ur.nature = 'jsonl'