SQL Query Export: Detailed Certificate Information

Looking to report on the certificates in your environment? This query will provide you with information on the Valid Before/After Dates, Algorithm, Issuer, # of Days Until Expiration, and more:

SELECT DISTINCT
    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)

For example:

Host IP Address
Hostname
MAC Address
Port
Issuer DN
Subject DN
Algorithm
Algorithm Signature
Key Size
Invalid Before
Invalid After
Expires in (days)

10.0.2.133

machine.1080

11:11:11:11:11:11

2381

CN=*, OU=*, O=*, L=*, ST=*

CN=*, OU=*, O=*, L=*, ST=*

RSA

SHA1withRSA

2048

Sat, 07 Oct 2006 16:54:00 CST

Thu, 06 Oct 2016 16:54:00 CST

7

If you'd only like to see Expired and Expiring in 90 Day certificates, you can add the last line item in the below example:

SELECT DISTINCT
    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)
WHERE (CAST(json_certs.cert->>'ssl.cert.not.valid.after' AS DATE) - CURRENT_DATE) <= 90

SQL Query Export: Detailed Certificate Information