Using Dynamic Parameters in Reports

As version 7.0, Secret Server allows creation of Reports using custom SQL.

Reporting supports embedding certain parameters into the SQL to give the viewer controls to dynamically change the report. The supported parameters are:

1) #STARTDATE - displays a calendar picker on view and returns a date. This defaults to beginning of the year and truncates the hours and minutes to 12:00 AM.

Ex: Display all users who have logged in after a certain date

SELECT
    Domain,
    Username,
    LastLogin
FROM tbUser
    LEFT JOIN tbDomain   
    ON tbUser.DomainId = tbDomain.DomainId
WHERE
    LastLogin > #STARTDATE
   
2) #ENDDATE - displays a calendar picker on view and returns a date. This defaults the current day and truncates the hours and minutes to 11:59 PM.

Ex: Display all users who have logged on a certain date

SELECT
    Domain,
    Username,
    LastLogin
FROM tbUser
    LEFT JOIN tbDomain   
    ON tbUser.DomainId = tbDomain.DomainId
WHERE
    LastLogin > #STARTDATE
AND
    LastLogin < #ENDDATE


3) #USER - displays a user dropdown list with all active users on view and returns an user id. This defaults to the current logged in user.

Ex: Display all secrets created by a certain user

SELECT
   SecretId,
   SecretName,
   Created
FROM tbSecret
WHERE
   UserId = #USER

4) #ORGANIZATION - this is an internal parameter used for determining the current instance's organization code. This is only useful for Secret Server online edition.  There is no need to use this parameter in your reports for Installed Edition.

(As of Secret Server 7.8.000048 the #GROUP parameter is also available.)
5) #GROUP - displays a group dropdown list with all active groups on view and returns a group id.  This defaults to the Everyone group.

Ex: Displays the Group details of the selected Group

SELECT
    GroupID,
    GroupName,
    Active
FROM tbGroup
WHERE GroupID = #GROUP

6) The following special parameters can be used to make your report more dynamic.  For example, the following script would give you a list of all users who have logged on during the last calendar month:

SELECT
    Domain,
    Username,  
   LastLogin
FROM tbUser
    LEFT JOIN tbDomain   
    ON tbUser.DomainId = tbDomain.DomainId
WHERE
    LastLogin BETWEEN #STARTPREVIOUSMONTH AND #ENDPREVIOUSMONTH

 

Parameter Name	Description
#STARTTODAY	Beginning of today at 12:00 AM
#ENDTODAY	End of today at 11:59:59 PM
#STARTYESTERDAY	Beginning of yesterday at 12:00 AM
#ENDYESTERDAY	End of Yesterday at 11:59:59 PM
#STARTPREVIOUSMONTH	The first day of the previous month at 12:00 AM
#ENDPREVIOUSMONTH	The last day of the previous month at 11:59:59 PM
#STARTCURRENTMONTH	The first day of current month
#ENDCURRENTMONTH	The last day of current month
#STARTPREVIOUSYEAR	January 1st of the previous year
#ENDPREVIOUSYEAR	December 31st of the previous year
#STARTCURRENTYEAR	January 1st of the current year
#ENDCURRENTYEAR	December 31st of the current year
#STARTWEEK	Beginning of the current week (Monday) at 12:00 AM
#ENDWEEK	End of the current week (Sunday) at 11:59:59 PM

 (As of Secret Server 7.8.000048 the #STARTWEEK and #ENDWEEK parameters are available.)

COLORING YOUR REPORTS

Another option when creating reports is to include a Column in your SQL query called "Color" this will give the row that particular color.  See here for HTML color names. 

For example:  To show users who haven't logged in within 90 days in Red...

SELECT    DisplayName
        ,CASE
            WHEN LastLogin < GetDate() - 90 THEN 'Red'
            ELSE 'White'
        END AS Color
FROM        tbUser

Article ID: 70, Created On: 1/11/2010, Modified: 7/11/2012