tim laqua dot com Thoughts and Code from Tim Laqua

19Apr/111

Monitoring Failed Report Server Subscriptions

While we love to empower our users as much as possible, we still need to pay attention to them and what they're up to. The first place I usually see users running amok as Microsoft BI adoption grows in organization is the Report Subscriptions in SSRS. They go nuts with them. I was at one company where 50% of the report subscriptions went to users outside of the company - that means our customers were depending on these subscriptions! Which brings up an interesting licensing debacle... but that's another story. Needless to say, when people set up a subscription, they expect it to work. If it doesn't, we REALLY need to let someone know.

Here, we have a procedure to monitor for Reporting Services subscription failures. Specifically, email subscription failures. When a failed subscription is detected, an email is sent to both the subscription owner and a digest of failures is sent to the specified admin group.

This procedure has saved me so many times it's not even funny. The last thing you want is your end users knowing about a failure before you.

/************************************************************************
***
*** Procedure:       Alert_FailedReportingServicesSubscriptions
*** Purpose:         Notifies administrators and subscription owners that
***                  subscriptions were not delivered as expected
***      
*** Author:          tl
*** Date Created:    2009-10-28
*** 
*** Revision History
*** Date        Author   Description
*** 2009-10-28  tl       Created
*** 
************************************************************************/
CREATE PROCEDURE [dbo].[Alert_FailedReportingServicesSubscriptions]
   @ReportServerBaseURL VARCHAR(1024)
  ,@AdminEmail VARCHAR(255)
  ,@FromAddress VARCHAR(255)
  ,@MailDomain VARCHAR(255)
  ,@NTDomain VARCHAR(255)
  ,@ReportServerDatabase VARCHAR(255) = 'ReportServer'
  ,@ReportServerSchema VARCHAR(255) = 'dbo'
  ,@HoursToCheck INT = 1
  ,@HourSchedule INT = 16777215
AS
BEGIN
  SET NOCOUNT ON;

  IF @HourSchedule & POWER(2, DATEPART(HOUR, GETDATE())) > 0
  BEGIN
    DECLARE @ReportTableOpen VARCHAR(MAX) =
    '
    

The following subscription(s) that you created was/were not sent to specified recipients at the date/time you scheduled. Database Administrators has been notified. Please reply to this email if you have any questions.

' DECLARE @ReportTableClose VARCHAR(MAX) = '
OwnerReportLast RunIntended RecipientsStatus
' CREATE TABLE #FailedReport ([Id] INT IDENTITY(1,1) ,[Name] VARCHAR(255) ,[Link] VARCHAR(2048) ,[LastRunTime] DATETIME ,[OwnerUsername] VARCHAR(255) ,[IntendedRecipients] VARCHAR(1024) ,[Message] VARCHAR(1024)) DECLARE @sql VARCHAR(MAX) = ' INSERT INTO #FailedReport ([Name] ,[Link] ,[LastRunTime] ,[OwnerUsername] ,[IntendedRecipients] ,[Message]) SELECT b.Name ,'''' + b.Name + '''' AS [Link] ,LastRunTime ,REPLACE(c.UserName, ''' + @NTDomain + CHAR(92) + ''', '''') AS [OwnerUserName] ,REPLACE(CAST(ExtensionSettings AS XML).value (''(//ParameterValue[Name="TO"]/Value)[1]'' ,''VARCHAR(1024)'') + '';'' + ISNULL(CAST(ExtensionSettings AS XML).value (''(//ParameterValue[Name="CC"]/Value)[1]'' ,''VARCHAR(1024)''), ''''),'';;'','';'') AS [IntendedRecipients] ,LEFT(ISNULL(LastStatus, ''''), 1024) AS [Message] FROM ' + @ReportServerDatabase + '.' + @ReportServerSchema + '.Subscriptions a INNER JOIN ' + @ReportServerDatabase + '.' + @ReportServerSchema + '.Catalog b WITH(NOLOCK) ON a.Report_OID=b.ItemId INNER JOIN ' + @ReportServerDatabase + '.' + @ReportServerSchema + '.Users c WITH(NOLOCK) ON a.OwnerID = c.UserID WHERE a.DeliveryExtension = ''Report Server Email'' AND LastStatus NOT LIKE ''Mail Sent%'' AND LastStatus NOT LIKE ''% 0 errors.'' AND LastStatus NOT LIKE ''New Sub%'' AND LastStatus NOT LIKE ''Pending%'' AND LastRunTime >= DATEADD(HOUR, -1 * ' + CAST(@HoursToCheck AS VARCHAR(10)) + ', GETDATE()) ORDER BY REPLACE(c.UserName, ''' + @NTDomain + CHAR(92) + ''', ''''), b.Name' EXECUTE(@sql) IF (SELECT COUNT(1) FROM #FailedReport) > 0 BEGIN DECLARE @FullReportTable VARCHAR(MAX) = '' ,@LastOwnerId INT = 0 ,@Username VARCHAR(255) DECLARE @Owner TABLE ([OwnerId] INT IDENTITY(1,1) ,[Username] VARCHAR(255)) DECLARE @Notification TABLE ([NotificationId] INT IDENTITY(1,1) ,[To] VARCHAR(255) ,[Body] VARCHAR(MAX)) INSERT INTO @Owner ([Username]) SELECT DISTINCT OwnerUsername FROM #FailedReport ORDER BY OwnerUsername DECLARE @UserFailedReport TABLE ([Id] INT IDENTITY(1,1) ,[Name] VARCHAR(255) ,[Link] VARCHAR(2048) ,[LastRunTime] DATETIME ,[OwnerUsername] VARCHAR(255) ,[IntendedRecipients] VARCHAR(1024) ,[Message] VARCHAR(1024)) WHILE EXISTS (SELECT TOP 1 1 FROM @Owner WHERE [OwnerId] > @LastOwnerId) BEGIN DELETE FROM @UserFailedReport SELECT TOP 1 @Username = [Username] ,@LastOwnerId = [OwnerId] FROM @Owner WHERE [OwnerId] > @LastOwnerId ORDER BY [OwnerId] DECLARE @UserReportTable VARCHAR(MAX) = '' ,@LastId INT = 0 INSERT INTO @UserFailedReport ([Name] ,[Link] ,[LastRunTime] ,[OwnerUsername] ,[IntendedRecipients] ,[Message]) SELECT [Name] ,[Link] ,[LastRunTime] ,[OwnerUserName] ,[IntendedRecipients] ,[Message] FROM #FailedReport WHERE [OwnerUsername] = @Username WHILE EXISTS (SELECT TOP 1 1 FROM @UserFailedReport WHERE [Id] > @LastId) BEGIN SELECT TOP 1 @UserReportTable += '' + ISNULL('' + [OwnerUsername] + '', 'Unknown') + '' + ISNULL([Link], 'Unavailable') + '' + ISNULL(CONVERT(VARCHAR, [LastRunTime], 120), 'Never') + '' + ISNULL([IntendedRecipients], 'Unknown') + '' + ISNULL([Message],'Unknown') + '' + CHAR(13) ,@LastId = [Id] FROM @UserFailedReport WHERE [Id] > @LastId ORDER BY [Id] END SET @FullReportTable += @UserReportTable + CHAR(13) SET @UserReportTable = @ReportTableOpen + @UserReportTable + @ReportTableClose INSERT INTO @Notification ([To], [Body]) VALUES (@Username + '@' + @MailDomain, @UserReportTable) END SET @FullReportTable = @ReportTableOpen + @FullReportTable + @ReportTableClose INSERT INTO @Notification ([To], [Body]) VALUES (@AdminEmail, @FullReportTable) DECLARE @LastNotificationId INT = 0 ,@To VARCHAR(255) ,@Body VARCHAR(MAX) WHILE EXISTS (SELECT TOP 1 1 FROM @Notification WHERE [NotificationId] > @LastNotificationId) BEGIN SELECT TOP 1 @To = [To] ,@Body = [Body] ,@LastNotificationId = [NotificationId] FROM @Notification WHERE [NotificationId] > @LastNotificationId ORDER BY [NotificationId] EXEC msdb.dbo.sp_send_dbmail @recipients = @To ,@from_address = @FromAddress ,@subject = 'Failed Report Manager Subscription(s)' ,@body = @Body ,@importance = 'High' ,@body_format = 'HTML' --DECLARE @ProcName VARCHAR(255) = OBJECT_NAME(@@PROCID) --EXEC [dbo].[Utility_Alert_SendMail] -- @AlertSubject = 'Failed Report Manager Subscription(s)' -- ,@AlertRecipients = @To -- ,@AlertBody = @Body -- ,@AlertFormat = 'HTML' -- ,@AlertSource = @ProcName -- ,@FromAddress = @FromAddress END END DROP TABLE #FailedReport END END

Now to run it:

EXEC [Alert_FailedReportingServicesSubscriptions] 
   @ReportServerBaseURL = 'http://reports.yourcompany.com/Reports/Pages/Report.aspx?ItemPath='
  ,@AdminEmail = 'Business Intelligence '
  ,@FromAddress = 'Business Intelligence '
  ,@MailDomain = 'yourcompany.com'
  ,@NTDomain = 'AWESOMECO'

Set paramaters as follows:

@ReportServerBaseURL VARCHAR(1024)
-- The url string needed view a report (including the ItemPath querystring variable
-- An example is 'http://reports.yourcompany.com/Reports/Pages/Report.aspx?ItemPath='
-- You can obtain this by simply viewing a report and looking at the address bar	

@AdminEmail VARCHAR(255)
-- Email that will receive digest emails

@FromAddress VARCHAR(255)
-- From email, they should be able to reply to this with questions

@MailDomain VARCHAR(255)
-- Domain that you can send email to by appending their user name to
-- So if you set this to 'example.com' and your username is EXAMPLE\tlaqua
-- it would attempt to send an email to tlaqua@example.com

@NTDomain VARCHAR(255)
-- NTDomain for usernames.  If your username is EXAMPLE\tlaqua, 'EXAMPLE'
-- is the @NTDomain

@ReportServerDatabase VARCHAR(255) = 'ReportServer'
-- Name of ReportServer database - MUST be on the server procedure runs on 

@ReportServerSchema VARCHAR(255) = 'dbo'
-- Schema for ReportServer tables

@HoursToCheck INT = 1
-- This should conicide with how often you run the procedure
-- If you run the procedure hourly, set this to 1 so it checks 1 hour back
-- If you run the procedure daily, set it to 24

@HourSchedule INT = 16777215
-- Bitmask for hours of the day to run.  If you want it to only run if
-- it is 9AM, 6PM, and 11PM, set this value to 8651264:
-- POWER(2, 9) + POWER(2, 18) + POWER(2, 23) = 8651264
Tagged as: , , Leave a comment
Comments (1) Trackbacks (0)
  1. I need to see subscription history status , not only LastStatus .


Leave a comment

No trackbacks yet.