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:

IP_ADDRESSHOST_NAMEMAC_ADDRESSSUBJECT_DNISSUER_DNNOT_VALID_BEFORENOT_VALID_AFTERPORT
10.4.27.187CN=ivm.production.dev52:37:00:cf:71:a4CN=ivm.production.devCN=R3, O=Let's Encrypt, C=USMon, 09 Aug 2021 12:39:05 UTCSun, 07 Nov 2021 12:39:05 UTC3780