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
12WITH3cert_expiration_dates AS (4SELECT DISTINCT asset_id, service_id, name, value, port5FROM dim_asset_service_configuration6WHERE lower(name) LIKE '%ssl.cert.not.valid.after' ),78cert_cn AS (9SELECT DISTINCT asset_id, service_id, name, value10FROM dim_asset_service_configuration11WHERE lower(name) LIKE '%ssl.cert.subject.dn' ),1213cert_start_dates AS (14SELECT DISTINCT asset_id, service_id, name, value15FROM dim_asset_service_configuration16WHERE lower(name) LIKE '%ssl.cert.not.valid.before' ),1718cert_issuer AS (19SELECT DISTINCT asset_id, service_id, name, value20FROM dim_asset_service_configuration21WHERE lower(name) LIKE '%ssl.cert.issuer.dn' )2223SELECT 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"24FROM dim_asset25JOIN cert_expiration_dates AS ced USING (asset_id)26JOIN cert_cn AS cc USING (asset_id)27JOIN cert_start_dates AS ccsd USING (asset_id)28JOIN cert_issuer AS ci USING (asset_id)29WHERE (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_ADDRESS | HOST_NAME | MAC_ADDRESS | SUBJECT_DN | ISSUER_DN | NOT_VALID_BEFORE | NOT_VALID_AFTER | PORT |
---|---|---|---|---|---|---|---|
10.4.27.187 | CN=ivm.production.dev | 52:37:00:cf:71:a4 | CN=ivm.production.dev | CN=R3, O=Let's Encrypt, C=US | Mon, 09 Aug 2021 12:39:05 UTC | Sun, 07 Nov 2021 12:39:05 UTC | 3780 |
Did this page help you?