I’m using the cert sql query from here and modified it to include the serial #, but when it exports to as a csv file, it doesn’t matter what I use to open it, it’s numbers and not alphanumeric.
Any advice?
SELECT
DISTINCT da.last_assessed_for_vulnerabilities AS “Last Scan Date”,
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.serial.number’ AS “Serial”,
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)