SQL Query Export: Detailed Certificate Information

Document created by Patrick Noyes Employee on Sep 29, 2016Last modified by Patrick Noyes Employee on Mar 17, 2017
Version 3Show Document
  • View in full screen mode

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)

Example:

Host IP AddressHostnameMAC AddressPortIssuer DNSubject DNAlgorithmAlgorithm SignatureKey SizeInvalid BeforeInvalid AfterExpires in (days)
10.0.2.133machine.108011:11:11:11:11:112381CN=*, OU=*, O=*, L=*, ST=*CN=*, OU=*, O=*, L=*, ST=*RSASHA1withRSA2048Sat, 07 Oct 2006 16:54:00 CSTThu, 06 Oct 2016 16:54:00 CST7

 

 

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

3 people found this helpful

Attachments

    Outcomes