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
4
cert_expiration_dates AS (
5
6
SELECT DISTINCT asset_id, service_id, name,value
7
8
FROM dim_asset_service_configuration
9
10
WHERE lower(name) LIKE '%ssl.cert.not.valid.after'
11
12
)
13
14
SELECT ip_address, host_name, mac_address, ced.value
15
16
FROM dim_asset
17
18
JOIN cert_expiration_dates AS ced USING (asset_id)
19
20
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_address

host_name

mac_address

value

10.4.27.187

exchange-2010.exchange-2010.mail.foo

52:37:00:cf:71:a4

Sun, 12 Apr 2015 20:10:29 PDT