White Papers
Abstract
Creating a scheduled report with a rolling week
Content
A report with a rolling week allows you to schedule a report for daily delivery that will show data from the previous 7 days.
Secret Server reports can use the variables #STARTDATE and #ENDDATE in the sql. When a report is run on a schedule the #STARTDATE and #ENDDATE are replaced with schedule values. For example, the schedule can be set to use the 'Start of the Week' and the 'End of the Week' for the #STARTDATE and #ENDDATE respectively. 'Start of the Week' is the Monday on or before the date the report is running on. So, this value only changes every Monday. However, it is possible to create a custom report that uses a rolling week.
To create a report with a rolling week (past seven days) we can do the following:
1. Go to an existing report that you would like to customize to use a rolling week.
2. Click edit and copy the sql used by the report.
3. Go back to the reports page.
4. Create a custom report by using the 'Create It' link at the bottom of the reports page.
5. Give the report a name and description.
6. Paste the copied sql into the Report SQL.
7. Replace #STARTDATE in the sql with DATEADD (day , -7 , #ENDDATE ).
8. Leave #ENDDATE unchanged.
9. Click save and then click schedule -> create new.
10. Choose Daily for the recurrence schedule.
11. Choose 'Start of Day' for the Start Date Parameter Value and 'End of Day' for the End Date Parameter Value.
You now have a custom report that will run every day and show data from the previous seven days.
(note: STARTDATE,ENDDATE should be all upper case)
To create a report with a rolling week (past seven days) we can do the following:
1. Go to an existing report that you would like to customize to use a rolling week.
2. Click edit and copy the sql used by the report.
3. Go back to the reports page.
4. Create a custom report by using the 'Create It' link at the bottom of the reports page.
5. Give the report a name and description.
6. Paste the copied sql into the Report SQL.
7. Replace #STARTDATE in the sql with DATEADD (day , -7 , #ENDDATE ).
8. Leave #ENDDATE unchanged.
9. Click save and then click schedule -> create new.
10. Choose Daily for the recurrence schedule.
11. Choose 'Start of Day' for the Start Date Parameter Value and 'End of Day' for the End Date Parameter Value.
You now have a custom report that will run every day and show data from the previous seven days.
(note: STARTDATE,ENDDATE should be all upper case)
An example of a report that shows failed logins for the previous 7 days:
SELECT daterecorded AS [Date Recorded],
udn.displayname AS [User],
ipaddress AS [IP Address]
FROM tbaudituser au WITH (nolock)
INNER JOIN tbuser u WITH (nolock)
ON au.useridaffected = u.userid
AND u.organizationid = #organization
INNER JOIN vuserdisplayname udn WITH (nolock)
ON u.userid = udn.userid
WHERE action = 'Login Failed'
AND daterecorded BETWEEN Dateadd (day, -7, #startdate) AND #enddate
ORDER BY 1 DESC,
2,
3
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSWHLP","label":"IBM Security Secret Server"},"Component":"Creating a scheduled report with a rolling week","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.x","Edition":"","Line of Business":{"code":"LOB24","label":"Security Software"}}]
Was this topic helpful?
Document Information
Modified date:
19 November 2019
UID
ibm11108623