SSRS Utility Reports: Subscription Inventory, Failed Subscriptions

When delivering a SQL Server Reporting Services (SSRS) solution with lots of subscriptions, it's helpful to also include some utility reports about those subscriptions.

Here are a pair of queries you can use against the ReportServer database (in this case, SQL Server 2014) to generate some quick internal reports for an inventory of report subscriptions (who is receiving what, when, in what format, including parameters?) and failed report subscriptions (what subscriptions have failed and why?), each with helpful info, timestamps and URL's.

Don't forget to change the url path's servername for these reports to reflect your own setup, keeping in mind that if you're using a named instance, the yourservername/Reports/ may look more like yourservername/Reports_instancename/.

Report Subscription Inventory

This is an easy way to provide business users with an accurate and easy list of "who's getting what" that is pulled directly from the ReportServer metadata - it'll never be out of date and it's live.

SELECT Catalog.Name AS ReportName
,'http://yourservername/Reports/Pages/Report.aspx?ItemPath=' + Catalog.Path + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' AS ReportSubscriptionMgrUrl
,Subscriptions.Description AS SubscriptionDescription
,Subscriptions.LastStatus
,Subscriptions.LastRunTime
,'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END
, 'Next Run Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,'NA')

,Subscriptions.Parameters
,ISNULL(
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)')
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="PATH"])[1]','nvarchar(150)')
) as [To]
,
ISNULL(
 Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(150)') 
, Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RENDER_FORMAT"])[1]','nvarchar(150)')
) as [Render Format]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(150)') as [Subject]
FROM [dbo].[ReportSchedule]
INNER JOIN [dbo].[Schedule]
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN [dbo].[Catalog]
ON ReportSchedule.ReportID = Catalog.ItemID
INNER JOIN [dbo].[Subscriptions]
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [dbo].[Users]
ON Subscriptions.OwnerID = Users.UserID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),[ReportSchedule].ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

Screenshot of Design view from my version, your results may vary

Report Subscription Failures

Allows the user to see if any subscriptions have failed most recently, for handling typical email or permissions errors, in the past 30 days. Because it uses the subscription's [laststatus] field (the same one you'll see in Report Manager), failures will drop off this report if they succeed again.

SELECT Catalog.Name AS ReportName
,'http://yourservername/Reports/Pages/Report.aspx?ItemPath=' + Catalog.Path + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' AS ReportSubscriptionMgrUrl
,Users.UserName AS SubscriptionOwner
,Subscriptions.Description AS SubscriptionDescription
,Subscriptions.LastStatus
,Subscriptions.LastRunTime
FROM [dbo].[ReportSchedule]
INNER JOIN [dbo].[Schedule]
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN [dbo].[Catalog]
ON ReportSchedule.ReportID = Catalog.ItemID
INNER JOIN [dbo].[Subscriptions]
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [dbo].[Users]
ON Subscriptions.OwnerID = Users.UserID
WHERE ((Subscriptions.DataSettings IS NULL AND Subscriptions.LastStatus LIKE 'Failure%') -- handle standard subscription errors
OR (Subscriptions.DataSettings IS NOT NULL AND RIGHT(Subscriptions.LastStatus, 11) <> '; 0 errors.'))
and Subscriptions.LastRunTime > dateadd(day, -31, getdate())

Screenshot of Design view from my version, your results may vary