Monitor SSRS Subscription Errors

Question

Using SSRS 2012, we utilize report subscriptions to save reports network locations and send reports by email. I am familiar with how to debug errors, but I am looking for a solution to alert our support team when a subscription has failed to send. Call me crazy for being proactive.

I see a solution to monitor the ReportServer tables for status, but it assumes all subscriptions are by email (only handles email statuses).

I also see the execution log table(ExecutionLog3), but the table doesn't appear to capture all errors. I forced a subscription to fail by removing network access to the file location, but the error doesn't appear in the table.

I would like to write an SSRS report which can be run to view all subscription errors that have occurred for a day. Any suggestions are appreciated.

Answer 1

There is no built in way to do this. You will have to parse the Windows error log or the reporting services log. I have not tried this but the ssrs api might return the last subscription status for subscription based reports, however there is not historical log outside of the ReportingService database and even there I am not sure if failures are logged.

Answer 2

This does only handle the last status message so doesn't fully answer the question (I came across this post because I am looking for the same answer), but it does seem to work for me in terms of catching all subscription errors, including file share issues:

select count(*) 
from ReportServer.dbo.[Subscriptions] S 
where 0 = case 
          when S.[LastStatus] = 'New Subscription' then 1 
          when substring(S.[LastStatus],1,9) = 'Mail Sent' then 1 
          when substring(S.[LastStatus],1,5) = 'Done:' 
           and right(S.[LastStatus],9) = '0 errors.' then 1 
          when substring(S.[LastStatus],1,9) = 'The file ' 
           and patindex('%has been saved to the%',S.[LastStatus]) > 1 
           and right(S.[LastStatus],11) = 'file share.' then 1 
          else 0 
      end

Answer 3

I have used Report Server Diagnostic Reports pack from Microsoft

More info about the Report Server Diagnostic Reports (snapshots)

Download link for Report Server Diagnostic Reports Pack

I have not used this but i have heard about it

Free SQL Monitor SSRS Reporting Pack

Answer 4

Check out this solution I found on Jeff Prom's blog, http://jeffprom.com/2008/08/22/ssrs-failed-subscription-notifications/

SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description
 FROM Subscriptions AS S
 LEFT OUTER JOIN [Catalog] AS C
 ON C.ItemID = S.Report_OID
 WHERE LEFT (S.LastStatus, 12) != ‘Mail sent to’
   AND LEFT (S.LastStatus, 12) != ‘New Subscrip’