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

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 2

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