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

Feedback (0)