SQL example - certificates expiring in 90 days

This query example shows all certificates that are expiring in 90 days. You can adjust the expiration time to what makes the most sense for your operations - 60 days, 180 days. etc.

sql
1
2
  WITH 
3
    cert_expiration_dates AS (
4
      SELECT DISTINCT asset_id, service_id, name, value, port
5
      FROM dim_asset_service_configuration
6
      WHERE lower(name) LIKE '%ssl.cert.not.valid.after'   ),
7
8
    cert_cn AS (
9
      SELECT DISTINCT asset_id, service_id, name, value      
10
      FROM dim_asset_service_configuration      
11
      WHERE lower(name) LIKE '%ssl.cert.subject.dn'   ),
12
13
    cert_start_dates AS (
14
      SELECT DISTINCT asset_id, service_id, name, value      
15
      FROM dim_asset_service_configuration     
16
      WHERE lower(name) LIKE '%ssl.cert.not.valid.before'  ),
17
18
    cert_issuer AS (
19
      SELECT DISTINCT asset_id, service_id, name, value      
20
      FROM dim_asset_service_configuration      
21
      WHERE lower(name) LIKE '%ssl.cert.issuer.dn'   )
22
      
23
  SELECT ip_address AS "IP ADDRESS", host_name AS "HOSTNAME", mac_address AS "MAC_ADDRESS", cc.value AS "SUBJECT DN", ci.value AS "ISSUER DN", ccsd.value AS "NOT VALID BEFORE", ced.value AS "NOT VALID AFTER", ced.port AS "PORT"
24
      FROM dim_asset   
25
        JOIN cert_expiration_dates AS ced USING (asset_id)   
26
        JOIN cert_cn AS cc USING (asset_id)  
27
        JOIN cert_start_dates AS ccsd USING (asset_id)   
28
        JOIN cert_issuer AS ci USING (asset_id)
29
      WHERE (cast(ced.value AS DATE) - CURRENT_TIMESTAMP  <= INTERVAL '90 days') AND (cast(ced.value AS DATE) - CURRENT_TIMESTAMP  > INTERVAL '0 days')
30

Example Output: