Creating Custom Reports in Secret Server

Secret Server contains robust reporting capabilities, as mentioned in on the Secret Server Report Features Page.  In addition to the default reports included with Secret Server (see Figure 1), additional reports are available for download in the Online Reports Gallery.  Beyond these options, users who aren’t familiar with SQL reporting, may also make a Custom Report Request from Thycotic Support.

Figure 1

One of Secret Server’s most popular features is the ability for users to create custom reports.  This allows users to build the reporting their organization requires.  To make a custom report, users will need some experience with SQL commands and reporting.  If you have experience, the following steps guide you through this process:

First, you need the code for the report you want to build.  The guide shows a report that allows users to see “What types of Secrets have expired?”  The SQL code is shown below:

-Begin SQL Code-

SELECT 
   st.SecretTypeName AS 
   ,COUNT(*) AS  
  FROM tbSecret s WITH (NOLOCK) 
   INNER JOIN tbSecretType st WITH (NOLOCK) 
    ON s.SecretTypeId = st.SecretTypeId
  WHERE 
   st.ExpirationFieldId IS NOT NULL
   AND s.ExpiredFieldChangedDate + st.ExpirationDays < GETDATE()
   AND s.Active = 1
   AND st.OrganizationId = #ORGANIZATION
GROUP BY
 st.SecretTypeName
ORDER BY
 COUNT(*) DESC

-End SQL Code-

Next, login to Secret Server with a user that has the Administer Reports role permission. Click on Reports -> Create it (located on the bottom right of the window). Assign a Report Name, a Report Description, and choose a Report Category in the dropdown menu (this is where the report appears). If your report should have a chart, choose the appropriate Chart Type in the dropdown menu. If you want your chart to appear in 3 dimensions, put a checkmark in the 3D Report box. Lastly, you’ll want to select your Page Size followed by pasting your SQL Code in the text box. Now, you can Preview the report and it will appear on the bottom of the same page. If you’re happy with the result, you can click Save and your report will appear on the main Reports page under the Report Category you selected.