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
12WITH34cert_expiration_dates AS (56SELECT DISTINCT asset_id, service_id, name,value78FROM dim_asset_service_configuration910WHERE lower(name) LIKE '%ssl.cert.not.valid.after'1112)1314SELECT ip_address, host_name, mac_address, ced.value1516FROM dim_asset1718JOIN cert_expiration_dates AS ced USING (asset_id)1920WHERE (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 |
Did this page help you?