This is the 4th part of the SSRSDB blog series. In this part we will be looking at how we can use data that we are collecting to automate contact to our users. Have you ever been asked to create a new dashboard that replace current reports, then asked if you can remove those replaced reports and been told “no, we still need those”. This happens a lot. The problem it causes is repetition of reporting, when there is repetition it can lead to multiple reports with slightly different business rules, this leads to confusion and misleading information.
How do we avoid this happening? One method is to analyse how frequency of the report use and compare it to a predefined limit of usage. In this section I want to look at this approach.
In the last section we looked at the additional metadata which holds additional data to support monitoring the report, this includes who owns the report and their email address. The metadata also contains the following additional columns:
Inactive Period – The amount of days that is allowed to elapse without usage. This needs to be set per report, the reason for this is that you will likely have some reports that are run daily and other that are only ran one every quarter.
Inactive Action – There are 2 outcomes at the stage:
NOTHING – Do Nothing
EMAIL – Send a friendly email to the user.
Final Period – The amount of days that can elapse for the final action.
Final Action – This is the action that takes place once the inactive period has elapsed. At current you’re limited to the following actions:
NOTHING – Do nothing
HIDE – Hide the report*
DELETE – Trigger the ReportServer to delete the report*
*Microsoft do not want you to do this, this is making a change to the ReportServer without using the ReportServer, this is a big no-no. However the process to hide and the process to delete is triggering the same stored procedures that the ReportServer GUI uses.
A typical set-up might be to set a report to email after 7 days, the report is then still available to users if they want to run the report, after a further 7 days the report is set to either hide or it is deleted. If you’re working in an environment that does not source control your reports then you can still get a version of the report back from dim_catalog in the event it is deleted in error – On a side note you should be source controlling your reports, use an SVN and source control solution.
With this data we can start looking at a process to automate contact with our users. Ok let’s look at the SQL to do this. What we have is a query to check if a user needs to be contacted and then we are going to loop over these with a cursor and send them a friendly email to say “Hey! You are not using the report I worked on!”.
To allow this to work you need to have configured database mail on your server. You can find a short guide on configuring database mail using this link – http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/. Once you have this configured then you can apply the following code to contact your users*.
*If you have not updated your metadata then this will not make contact.
Notify the user
DECLARE DeprecatedReportsCursor CURSOR FOR WITH LastExecution AS ( SELECT [Item_Skey] , MAX([ExecutionStartDate]) maxdate FROM [dbo].[Fact_Transaction_Executions] GROUP BY [Item_Skey] ) SELECT c.Item_Name , COALESCE(CONVERT(NVARCHAR(50),MAXDATE),'**NEVER RUN**') AS 'Execution_Date' , M.Item_Owner , CASE WHEN maxdate IS NULL THEN 999 ELSE DATEDIFF(DAY,MAXDATE,GETDATE()) END AS 'Days_Since_Last_Execution' ,C.item_URL ,m.Item_In_Active_Period ,CASE WHEN (m.Item_Final_Period - DATEDIFF(DAY,MAXDATE,GETDATE())) < 0 THEN 0 ELSE m.Item_Final_Period - DATEDIFF(DAY,MAXDATE,GETDATE()) END AS 'Days_To_Remove' ,M.Item_Owner_Email AS 'Email_Address' FROM dbo.Dim_Catalog C LEFT JOIN LastExecution LE ON LE.[item_skey] = C.item_Skey LEFT JOIN Support.Item_Metadata M ON C.Item_BusinessKey = M.Item_BusinessKey WHERE c.SCD_Is_Current = 1 AND c.Item_Type = 2 AND c.Item_Is_Hidden = 0 AND M.Item_Type = 'Main' AND M.Item_In_Active_Action = 'EMAIL' AND DATEDIFF(DAY,MAXDATE,GETDATE()) >= CONVERT(INT,m.Item_In_Active_Period) ORDER BY c.item_Name ; DECLARE @Date varchar(100) DECLARE @MainBody varchar(MAX) DECLARE @Link varchar(500) DECLARE @ReportName VARCHAR(500) DECLARE @ExecutionDate VARCHAR(500) DECLARE @ReportRequestedBy VARCHAR(500) DECLARE @DSE INT DECLARE @InActivePeriod VARCHAR(5) DECLARE @DaysToRemove VARCHAR(5) DECLARE @EmailSubject VARCHAR(500) DECLARE @OwnerEmailAddress VARCHAR(100) OPEN DeprecatedReportsCursor FETCH NEXT FROM DeprecatedReportsCursor INTO @ReportName, @ExecutionDate, @ReportRequestedBy, @DSE, @Link,@InActivePeriod,@DaysToRemove, @OwnerEmailAddress WHILE @@FETCH_STATUS = 0 BEGIN SET @MainBody = ' <html> <head> <title>Notification Email</title> </head> <body> <table width="800" border="0" cellpadding="0" cellspacing="0" bgcolor="#ffffff" style="font-size: 13"> <tr bgcolor="#008a9f" width="734px"><td><font color="#008a9f">h</font></td></tr> <tr bgcolor="#00c3e1" width="734px"> <td><font color="#00c3e1">h</font></td> </tr> <tr bgcolor="#FFFFFF" width="734px"> <td><font color="#FFFFFF">h</font></td> </tr> <tr valign="top" align="center"> <td style="border-width : 0px;"><img src="http://www.hyperbi.co.uk/wp-content/uploads/2015/01/HyperBi.png" alt="" title="" border="0" height="150" /> </td> </tr> <tr bgcolor="#FFFFFF" width="734px"> <td><font color="#FFFFFF">h</font></td> </tr> <tr valign="top"> <td style="border-width : 0px; font-family:Arial;"> <h1 style="color:#E33030; text-align: center; ">Report due for depreciation. <u>Act now!</u></h1> <p>Hello,</p> <p><b>' + @ReportName + '</b> - Has not been used for more than ' + @InActivePeriod + ' days. </p> <p>This report is now scheduled for depreciation and will be removed <strong>' + CASE WHEN @DaysToRemove = 0 THEN 'Today' ELSE 'in ' + @DaysToRemove + ' days' END + '</strong>.</p> <p> Please click the link below to avoid this action. <br /> </p> <p> <a href="' + @Link + '">' + @ReportName + ' </a> <br /> </p> </BR> <p>Regards,</p> <p><i>Management Information Team.</i></p> </br></br> </td> </tr> <tr bgcolor="#ffffff" width="734px"> <td><font color="#ffffff">h</font></td> </tr> <tr bgcolor="#00c3e1" width="734px"> <td><font color="#00c3e1">h</font></td> </tr> <tr bgcolor="#008a9f" width="734px"><td><font color="#008a9f">h</font></td></tr> <tr bgcolor="#000000"><td style="padding: 10px; font-family:Arial"><font size="1" color="#FFFFFF"> <p style="font-size: 12; font-family:Arial"></br> Please send any feedback to <a style="text-decoration: none; color: #FFFFFF" href="mailto:firstname.lastname@example.org">email@example.com</a> </p> <p style="font-size: 12; font-family:Arial"> This email was auto generated. Please do not reply directly. </p> <p></p></br> </font></td></tr> </table> </body> </html> ' SET @EmailSubject = 'Important: Report due for depreciation - ' + @ReportName; SET @ReportRequestedBy = @OwnerEmailAddress EXEC msdb.dbo.sp_send_dbmail @profile_name = 'hyperbi', @recipients = @OwnerEmailAddress, @subject = @EmailSubject, @body = @MainBody, @body_format = 'HTML' PRINT @MainBody FETCH NEXT FROM DeprecatedReportsCursor INTO @ReportName, @ExecutionDate, @ReportRequestedBy, @DSE, @Link,@InActivePeriod,@DaysToRemove, @OwnerEmailAddress END CLOSE DeprecatedReportsCursor DEALLOCATE DeprecatedReportsCursor
Working our way from the top we have a common table expressions which is calculating when a report was last accessed (successfully – non-successful runs are not stored in the ExectionStorageLog table). This is fed in to a query which is comparing the report with the metadata to calculate if the user should be contacted by email – this must be expressed in the metadata. This is all loaded in to the a CURSOR statement (typically I would say that CURSORs are evil and should be avoided at all costs, however much like all questions related to database administration it depends, looping to send emails is a great reason to use a CURSOR).
From here we load a set of variables from the cursor, these variables are loaded in to an HTML block which is used for the emails. You will notice that I have used quite an old style of HTML, this isnt because I am old school, but because these emails are typically going to Outlook, which has very basic HTML processing. Once the HMTL is built it is being passed over to database mail, EXEC msdb.sp_send_dbmail handles this for us. Once executed this will fire an email for every report which has not been accessed beyond the in active period. The user will receive an email that looks like this:
This email is completely customisable with a little knowledge of HTML. It features your company’s logo (1), a “call to action” prompting the user that they need to decide what to do with their report, all the details about their report are listed (3), a link to the report is included (4)(this link allows the user to click on the link and run the report, running the report will reset the counter) and lastly it contains all the contact information for your report writing team.
Personally I have an agent job scheduled to run once a week on a Monday morning.
Sending an email is all well and good however what happens if no one responds, or the only person who used that report left the company months ago. We need an automated solution to remove/hide these based on the final action. To do that we have another agent job.
DECLARE DeprecatedReportsCursor CURSOR FOR WITH LastExecution AS ( SELECT [Item_Skey] , MAX([ExecutionStartDate]) maxdate FROM [dbo].[Fact_Transaction_Executions] GROUP BY [Item_Skey] ) SELECT c.Item_BusinessKey ,m.Item_Final_action ,c.Item_Path FROM dbo.Dim_Catalog C LEFT JOIN LastExecution LE ON LE.[item_skey] = C.item_Skey LEFT JOIN Support.Item_Metadata M ON C.Item_BusinessKey = M.Item_BusinessKey WHERE c.SCD_Is_Current = 1 AND c.Item_Type = 2 AND M.Item_Type = 'Main' AND DATEDIFF(DAY,MAXDATE,GETDATE()) >= CONVERT(INT,m.Item_Final_Period) ORDER BY c.item_Name ; DECLARE @Item_ID varchar(100) DECLARE @SQL varchar(MAX) DECLARE @FinalAction VARCHAR(50) DECLARE @Item_Path VARCHAR(250) OPEN DeprecatedReportsCursor FETCH NEXT FROM DeprecatedReportsCursor INTO @Item_ID, @FinalAction, @Item_Path WHILE @@FETCH_STATUS = 0 BEGIN IF @FinalAction = 'HIDE' BEGIN SET @SQL = ' UPDATE c SET Hidden = 1 FROM reportserver.dbo.CATALOG c WHERE ItemID = ''' + @Item_ID + '''' PRINT 'Report Hidden' END IF @FinalAction = 'DELETE' BEGIN SET @sql = 'EXEC ReportServer.dbo.DeleteObject @Path=N''' + @Item_Path + ''',@AuthType=1,@Prefix=N''' + @Item_Path + '%''' PRINT 'Report deleted' END IF @FinalAction = 'NOTHING' BEGIN PRINT 'nothing' END PRINT @SQL EXEC (@sql) FETCH NEXT FROM DeprecatedReportsCursor INTO @Item_ID, @FinalAction, @Item_Path END CLOSE DeprecatedReportsCursor DEALLOCATE DeprecatedReportsCursor
This process is very similar to the process above, we have a CURSOR that loops over all the reports that are past the final notice period and either does nothing, hides or deletes the report.
As stated above this is all done in the same way as the report server to avoid causing any internal issues (use this at your own risk).
I have this as a separate job which runs the evening of the first job.
With that you have an automated report expiration process for SSRS.
Thanks for reading.