Use Custom Reports as Your Secret Weapon

10 12 2013

Custom Reports

While Secret Server contains a number of reports addressing Secrets, folders, users, activity and more, having the flexibility to create your own reports may be necessary to address your organization’s unique requirements. With the Custom Reports feature of Enterprise and Enterprise Plus editions (and a little knowledge of SQL), you can do just that.

When creating a custom report, you can either write your own SQL query or customize a SQL query from an existing report.

Create a New Custom Report

To create a new custom report, click the Create it link at the bottom-right corner of the Reports page in Secret Server. The resulting page contains a few fields that are present to customize the name, descriptions and other aspects of the report, and a large text box for the SQL query. At the bottom of the page, clicking Show Secret Server SQL database information will provide a drop-down menu and grid that allow you to take a look at the tables and table columns available for use in reporting. Clicking Preview will provide you with the results of your custom report below, so you can check the accuracy of your report.

reportsql1

Reference Custom Secret Fields

With version 8.2.000000, the ability to expose fields for display was introduced along with custom columns for the Dashboard. This means that certain Secret fields can be left unencrypted, and can therefore be used in custom reporting as well. This change can be made at the Secret Template level, and will present a message warning that the fields will be left unencrypted in the database. For this reason, it is important to not mark any fields as exposed for display if they contain sensitive information that should remain encrypted.

report2

report3

Once fields are marked to be exposed for display, they can be referenced in reports as any other field in the database. For example, the following SQL with display Secrets containing a custom field value called “Account Used By”:

SELECT

s.SecretName AS [Secret Name]

,si.ItemValue AS [Account Used By:]

FROM

tbSecret s

JOIN

tbSecretItem si

ON    s.SecretID = si.SecretID

JOIN

tbSecretField sf

ON    sf.SecretFieldID = si.SecretFieldID

WHERE

s.SecretTypeID = 6001

AND

sf.SecretFieldDisplayName = ‘Account Used By:’

This report will return results in the following manner:

report4

Dynamic Parameters

Secret Server also supports the use of several dynamic parameters that will allow report users to select a variable to apply to a report. These can be parameters such as user, group or date range. For more information on using dynamic parameters, see our KB article on the topic. A good example of dynamic parameters can be seen in the preconfigured report “What Secrets have been accessed by a user?”

report5

report6

Reports Gallery

To see custom reports that other Secret Server users have created and to share your own, you can take a look at the Custom Reports Gallery.

Want to learn even more about creating custom reports? Join us this Thursday, December 12th, at  11:30 AM EST for our Deep Dive: Secret Server – Get the most out of Reporting Webinar. Register today!  

For any questions or assistance with custom reports, contact Thycotic Support.








Follow

Get every new post delivered to your Inbox.

Join 30 other followers