REVIEWED by Sophos
This query checks the certificates table and calculates if any certificates are going to be expiring in the next N days.
If you are like me you have a story of when the business was impacted by some certificate expiring on a product or web server. This query will help you stay ahead of such problems going forward.
If you are in the situation where the certificate already expired it will also inform you of certificates that expired in last 7 days.
We use a variable for the number of days you want to check for certificate expiration.
SELECT
datetime(not_valid_after,'unixepoch') Expiration_date,
CAST((not_valid_after - strftime('%s','Now'))/86400 + 0.9 AS INT) Days_to_expiration,
store,
store_location,
username,
common_name,
subject,
issuer,
path,
datetime(not_valid_before, 'unixepoch') Not_Valid_Before
FROM certificates
WHERE not_valid_after < $$Expiration in N Days$$ * 86400 + strftime('%s','now') AND not_valid_after > strftime('%s','now','-7 days')
ORDER by Days_to_expiration DESC
Folks will probably want to edit this script to exclude the Intermediate Certificate Store. IE change the WHERE clause to include
AND store NOT LIKE 'Intermediate Cert%'
You may have other edits you find make it better and I would love to see what you change to make it fit your needs.
