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) =
    '<style>
    table { font-family: Calibri, Verdana, Ariel, sans-serif;  font-size: 90%; }
    th { padding-left: 3pt; font-style: italic; text-align: left; border-bottom: 1px solid black;}
    td { padding-left: 3pt; white-space: nowrap; }
    p { font-family: Calibri, Verdana, Ariel, sans-serif;  font-size: 95%; text-indent: 0pt; margin-bottom: 12pt; }
    </style>
    <p>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.</p>
    <table>
    <tr><th>Owner</th><th>Report</th><th>Last Run</th><th>Intended Recipients</th><th>Status</th></tr>'
 
    DECLARE @ReportTableClose VARCHAR(MAX) = '</table>'
 
    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
      ,''<a href="' + @ReportServerBaseURL + ''' + b.Path + ''">'' + b.Name + ''</a>'' 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 += '<tr><td>' + ISNULL('<a href="mailto:' + [OwnerUsername] + '@' + @MailDomain + '">' + [OwnerUsername] + '</a>', 'Unknown') + '</td><td>' + ISNULL([Link], 'Unavailable') + '</td><td>' + ISNULL(CONVERT(VARCHAR, [LastRunTime], 120), 'Never') + '</td><td>' + ISNULL([IntendedRecipients], 'Unknown') + '</td><td>' + ISNULL([Message],'Unknown') + '</td></tr>' + 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 <BusinessIntelligence@yourcompany.com>'
  ,@FromAddress = 'Business Intelligence <BusinessIntelligence@yourcompany.com>'
  ,@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.