Hello, I’m wondering if there are additional attributes that can be added to this report other than what’s listed below.
I’m looking for “Friendly Name” and also “Certificate Template” fields.
SELECT
da.ip_address AS “Host IP Address”,
da.host_name AS “Hostname”,
da.mac_address AS “MAC Address”,
json_certs.port AS “Port”,
json_certs.cert->>‘ssl.cert.issuer.dn’ AS “Issuer”,
json_certs.cert->>‘ssl.cert.subject.dn’ AS “Subject”,
json_certs.cert->>‘ssl.cert.key.alg.name’ AS “Algorithm”,
json_certs.cert->>‘ssl.cert.sig.alg.name’ AS “Algorithm Signature”,
json_certs.cert->>‘ssl.cert.key.rsa.modulusBits’ AS “Key Size”,
json_certs.cert->>‘ssl.cert.not.valid.before’ AS “Invalid Before”,
json_certs.cert->>‘ssl.cert.not.valid.after’ AS “Invalid After”,
(CAST(json_certs.cert->>‘ssl.cert.not.valid.after’ AS DATE) - CURRENT_DATE) AS “Expires In (days)”
FROM (
SELECT asset_id, service_id, port, json_object_agg(name, replace(value::text, ‘"’, ‘’)) as cert
FROM dim_asset_service_configuration
WHERE lower(name) LIKE ‘ssl.cert.%’
GROUP BY 1, 2, 3
) AS json_certs
JOIN dim_asset AS da USING (asset_id)