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.





Creating Custom Reports in Secret Server

30 08 2012

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 [Secret Template]
   ,COUNT(*) AS  [Number Of Secrets]
  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.








Follow

Get every new post delivered to your Inbox.

Join 30 other followers