Querying ReportServer Database

Here's a script I knocked up which gives you some insights into your Reports Catalog in Reporting Services.

I got frustrated with the Reports Manager site and its inability to give you a wholistic view of subscriptions, and the script blew out a little from there as I had a scrounge around the ResportServer database structure.

I might hook this up to an RS report at some stage, if that's not akin to "crossing the streams".

Note that info about the number of executions and last execution time are based on the execution log, which only keeps records for the last 60 days by default.

USE ReportServer
SELECT
         CatalogParent.Name ParentName,
         Catalog.Name ReportName,
         ReportCreatedByUsers.UserName ReportCreatedByUserName,
         Catalog.CreationDate ReportCreationDate,
         ReportModifiedByUsers.UserName ReportModifiedByUserName,
         Catalog.ModifiedDate ReportModifiedDate,
         CountExecution.CountStart TotalExecutions,
         ExecutionLog.InstanceName LastExecutedInstanceName,
         ExecutionLog.UserName LastExecutedUserName,
         ExecutionLog.Format LastExecutedFormat,
         ExecutionLog.TimeStart LastExecutedTimeStart,
         ExecutionLog.TimeEnd LastExecutedTimeEnd,
         ExecutionLog.TimeDataRetrieval LastExecutedTimeDataRetrieval,
         ExecutionLog.TimeProcessing LastExecutedTimeProcessing,
         ExecutionLog.TimeRendering LastExecutedTimeRendering,
         ExecutionLog.Status LastExecutedStatus,
         ExecutionLog.ByteCount LastExecutedByteCount,
         ExecutionLog.[RowCount] LastExecutedRowCount,
         SubscriptionOwner.UserName SubscriptionOwnerUserName,
         SubscriptionModifiedByUsers.UserName SubscriptionModifiedByUserName,
         Subscriptions.ModifiedDate SubscriptionModifiedDate,
         Subscriptions.Description SubscriptionDescription,
         Subscriptions.LastStatus SubscriptionLastStatus,
         Subscriptions.LastRunTime SubscriptionLastRunTime
   FROM
         dbo.Catalog
         JOIN
         dbo.Catalog CatalogParent
         ON Catalog.ParentID = CatalogParent.ItemID
         JOIN
         dbo.Users ReportCreatedByUsers
         ON Catalog.CreatedByID = ReportCreatedByUsers.UserID
         JOIN
         dbo.Users ReportModifiedByUsers
         ON Catalog.ModifiedByID = ReportModifiedByUsers.UserID
         LEFT JOIN
   (
   SELECT
            ReportID,
            MAX(TimeStart) LastTimeStart
      FROM
            dbo.ExecutionLog
      GROUP BY
            ReportID
   ) LatestExecution
         ON Catalog.ItemID = LatestExecution.ReportID
         LEFT JOIN
   (
   SELECT
            ReportID,
            COUNT(TimeStart) CountStart
      FROM
            dbo.ExecutionLog
      GROUP BY
            ReportID
   ) CountExecution
         ON Catalog.ItemID = CountExecution.ReportID
         LEFT JOIN
         dbo.ExecutionLog
         ON LatestExecution.ReportID = ExecutionLog.ReportID
      AND LatestExecution.LastTimeStart = ExecutionLog.TimeStart
         LEFT JOIN
         dbo.Subscriptions
         ON Catalog.ItemID = Subscriptions.Report_OID
         LEFT JOIN
         dbo.Users SubscriptionOwner
         ON Subscriptions.OwnerID = SubscriptionOwner.UserID
         LEFT JOIN
         dbo.Users SubscriptionModifiedByUsers
         ON Subscriptions.ModifiedByID = SubscriptionModifiedByUsers.UserID
   ORDER BY
         CatalogParent.Name,
         Catalog.Name