SQL Query Export Example: Certificates Expiring in 90 Days

Document created by synapsr on Feb 2, 2015
Version 1Show Document
  • View in full screen mode

Wouldn't it be nice to get ahead of expiring SSL certs if only it was easy to know where the expiring ones are. 

Nexpose already collects this information and this SQL Query will show all certs expiring in 90 days so you can have all certs updated.

You can adjust the expiration time to what makes the most sense for your operations - 60 days, 180 days. etc.

 

 

WITH

   cert_expiration_dates AS (

      SELECT DISTINCT asset_id, service_id, name,value

      FROM dim_asset_service_configuration

      WHERE lower(name) LIKE '%ssl.cert.not.valid.after'

   )

SELECT ip_address, host_name, mac_address, ced.value

FROM dim_asset

   JOIN cert_expiration_dates AS ced USING (asset_id)

WHERE (cast(ced.value AS DATE) - CURRENT_TIMESTAMP  <= INTERVAL '90 days') AND (cast(ced.value AS DATE) - CURRENT_TIMESTAMP  > INTERVAL '0 days')

 

Example Output:

 

ip_addresshost_namemac_addressvalue

10.4.27.187

exchange-2010.exchange-2010.mail.foo

52:37:00:cf:71:a4

Sun, 12 Apr 2015 20:10:29 PDT

3 people found this helpful

Attachments

    Outcomes