SQL example - certificates expiring in 90 days

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')