Still, this type of oversight seems all too common even in large companies. I remember several cases from Fortune 500 companies in the past few years alone. What would be a good way to automate checking for them? Has anyone developed a tool designed specifically to avoid certificate expiry disasters?
https://pypi.org/project/check-tls-certs/
I run one daily from cron and have it email me a report with the days to expiration for the certs I’m responsible for, even for certs that auto renew. I don’t filter the email. Daily is not too frequent for it to go to my inbox, but frequent enough that I’ll notice if it doesn’t mail me. YMMV.
Certificates that aren't from the Web PKI almost invariably won't be logged. Most logs explicitly refuse everything except certs from the Web PKI so as not to be burdened storing garbage. So this won't find certs issued by the custom OpenSSL CA on that one guys Linux laptop.
Not all Web PKI certs are logged. There is no BR obligation and no root store programme rule that requires logging. The only things in place that strongly encourage logging are the Chrome and Safari policies. For systems that aren't designed to be accessed with a web browser or, much more rarely, enterprises that have persuaded themselves only IE is authorised anyway, the certs might deliberately not be logged. Yes there are (small) CAs doing this in the Web PKI, on purpose, in 2019.