How to find certificates that are not associated to any application objects

Applies:

Venafi Encryption Director 8.0 and above

Microsoft SQL 2010 and above

Summary:

Identifying certificates that do not have an application object associated to them can be necessary at time.  Below are two methods that exist.

Method #1:

1. Login to WebAdmin and go the Reports tree.
2. Click add and select the report "Certificate Association Expiration Report"
   
3. Fill out all the desired fields to included the certificates you want.
4. Click "Run Now".
5. Download the finished report in CSV format
6. Open the downloaded CSV file in Excel.
7. Delete the first 6 rows.
8. Column Q lists the "Application DN".  The blank columns specified are those certificates that do not have an Application associated to the certificate.

Method #2

1. Get access to a SQL prompt with enough rights to do a query.
2. Run the following query:
   select * from config_objects where GUID not in (select GUID from config_contains where Attribute = 'Consumers') and ClassName = 'x509 certificate'