Listing Report Server Subscription Details

Ever get annoyed by the list of SQL Agent Reporting Services jobs with their non-sensical names. You don't know which ones are active subscriptions and which ones are only once off? Users ring you to say they're not receiving the report they subscribed to, and there is no quick way to see how the subscription is setup.

In Report Manager you can only view the subscriptions for the selected report. To see the schedule details to need to drill down another couple of screens.

Using Management Studio you are still stuck with looking at each individual report and then each subscription. At least when you bring up the properties for the subscription it's all within one dialog.

The following query will list the basic details about all the subscriptions in the ReportServer database. In the next iteration I hope to include more details such as file format, parameters selected, etc...

SELECT
  cat.path AS [ReportServerPath]
  , own.username AS SubscriptionCreatedBy
  , jb.date_created AS [SubscriptionCreatedOn]
  , mod.username AS [SubscriptionModifiedBy]
  , sub.ModifiedDate AS [SubscriptionModifiedOn]
  , sub.Description AS [SubscriptionDescription]
  , sub.EventType AS [SubscriptionType]
  , CASE
      WHEN PATINDEX('%Recurrence%', sub.MatchData) > 0THEN 'Recurring' 
      ELSE 'Once Off'
    END AS [ScheduleType]
  , sch.LastRunTime AS [SubscriptionLastRun]
FROM msdb.dbo.sysjobs jb
  INNER JOIN [ReportServer].[dbo].[Schedule] sch
  ON jb.name = CAST(sch.scheduleid AS VARCHAR(40))
    INNER JOIN [ReportServer].[dbo].[Subscriptions] sub
    ON sch.eventdata = sub.subscriptionid
      INNER JOIN [ReportServer].[dbo].[Catalog] cat
      ON sub.Report_OID = cat.ItemID
      INNER JOIN [ReportServer].[dbo].[Users] own
      ON sub.OwnerID = own.UserID
      INNER JOIN [ReportServer].[dbo].[Users] mod
      ON sub.ModifiedByID = mod.UserID