Get a list of active SSRS subscriptions

Recently, I was asked by developers to get a list of all the active SSRS subscriptions. I found this script on msdn but improvized it to include LastRuntime and LastRunStatus.

USE ReportServer
go
SELECT   c.[Name] ReportName,           
s.ScheduleID JobName,           
ss.[Description] SubscriptionDescription,           
ss.DeliveryExtension SubscriptionType,
ss.LastRunTime,
ss.LastStatus,
c.[Path] ReportFolderPath,           
row_number() over(order by s.ScheduleID) as rn             
into
#Temp  
FROM     
ReportSchedule rs           
INNER JOIN Schedule s ON rs.ScheduleID = s.ScheduleID           
INNER JOIN Subscriptions ss ON rs.SubscriptionID = ss.SubscriptionID           
INNER JOIN [Catalog] c ON rs.ReportID = c.ItemID AND ss.Report_OID = c.ItemID   

SELECT * from #temp
DROP TABLE #Temp

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: