One question I get asked often is “How to determine what certificates are expiring?”. This is especially critical for certificates that are not enrolled for with autoenrollment. This is due to the fact that autoenrollment will renew certificates. However, when requesting a certificate for a server, often the Subject or SAN are supplied in the request, limiting the ability to use autoenrollment to renew the certificate. Also, since servers generally host services that are critical to the environment, it is often better to actually enroll for the certificates manually as well as renew manually to ensure this gets completed successfully.
The following URL has a script that can be run to determine what certificates are expiring. The script allows you to specify the number of days to expiration as well as whether to exclude autoenrolled certificates from the output.
Here is the link: http://blog.crayon.no/blogs/ragnar/archive/2009/05/28/listing-certificates-that-expire-soon.aspx
If anyone has any better solutions for this, feel free to post a comment.