How To: Find out historically who has downloaded a certificate without the Root Chain

Applies to:

• Venafi Encryption Director 6.1 through Director 11
• Venafi TrustProtection Platform 14.1 and up

About:

When downloading a certificate from the Web Administration Console or Aperture Console, customers have the choice to include the Root Chain and, with proper rights, the Private Key.  This article provides instructions on how to access the information from either the Log Viewer in the Web Administration Console OR by a SQL Query against your log table in your database (see last section: Accessing Information from a Database Query).

Accessing Information from a Web Administration Console

1.  Log into the Web Administration Console as someone who has access to the "Default SQL Channel" under the Logging Tree
2. Navigate to the Logging tree
   
   
   
3. Expand the "Channels" object and select the "Default SQL Channel"
   
   
   
4. Click on the "General" tab of the "Default SQL Channel" object.
   
   
   
5. Use your mouse to hover over the "Client Time" column header.  A down arrow should appear on the right hand side to show the log viewing options.
   
   
   
6. Click on the down arrow and go to "Columns" and then enable "Value 2"
   
   
   The "Value 2" column should now be visible in the log view.
   
   
7. From the "Events" column header, go to "FIlters" and click on "Select Criteria"
   
   
   
8. In the Event Selector, type in "Certificate Download" for your search, and click the search icon (magnifying glass) to start the search.
   
   
9. Depending upon your version of the product, you will get 1-3 results.  Highlight "Admin UI - Certificate Downloaded" and if available select "Aperture - Certificate Downloaded". Click on the right arrow to move the selected event to the right side of the Event Selector.
   
   
   
   After you click on the right arrow it should look like this:
   
   
   
10. Click the "Select" button at the bottom of the window to save your selection and close the window.
    
    
11. ON the "Value2" column, go to Filters and enter a 0 in the = (Equals to) column as shown in the screenshot below:
    
    
    
12. You now have a filtered list that shows ALL certificate downloads that happened in either the Web Administration Console or Aperture Console and the certificate trust chain (root chain) was not included.
    
    
13. If you notice that your results are greater than 200 then increase your log limit to 1,000
    
    
    
14. If there are more than 1,000 results then use the Database Query option (provided below) to return all results.

Accessing Information from a Database Query

You can use the following Database Query to search your logs:

Note: This query has been tested against MS SQL and Oracle

SELECT ServerTimestamp, Component, Text1, Value1, Value2, Text2 FROM log where EventID IN (786442,1769475) and Value2 = 0

Sample Output:

• ServerTimestamp = The time it happened, stored in Epic time format.
  See http://www.epochconverter.com
• Component = The full path to the certificate object that the download occured from
• Text1 = The Identity Prefix and Username of the user that downloaded the certificate
• Value1 = Specifies if a Private Key was included in the download
  1 = Private key was included
  0 = Private key was NOT included
• Value2 = Specifies if a Root Chain was included in the download
  1 = Root chain was included
  0 = Root chain was NOT included
  
• Text2 = Specifies the serial number of the certificate that was downloaded