Thursday, January 15

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


10 comments:

Sequel said...

This is a good script but have you figured out how to query the detailed run schedule of a report? Im definitely going to use this query as a subscription to myself but Im interested on when my reports are going to run next. Any thoughts? Thanks!

Greg said...

Very nice script.

By the way, you can edit the HTML and use a PRE tag at the beginning of the code and /PRE tag at the end and it will at least retain the spaces and show up as Courier. That's what I do on my Blogger site (codetempest.blogspot.com). Getting syntax coloring is something I have not investigated yet.

Cheers.

CraigN said...

Nice post. There is a bug in the script though. The join "Subscriptions.OwnerID = SubscriptionModifiedByUsers.UserID" should be "Subscriptions.ModifiedByID = SubscriptionModifiedByUsers.UserID".

Scott Herbert said...

Hi CraigN, thanks for the correction, I've altered the code in the post.

Paul said...
This comment has been removed by the author.
Paul said...

If you wanted to design a query to make a report for management that would show which AD Groups or users have access to which reports, how would you go about it.
I have already designed reports that shows which reports ran when and by who. But creating an on demand report that shows who has access to which reports would be nice.

Scott Herbert said...

Hi Paul,

I've put up a new post at http://sqlninja.blogspot.com/2011/02/report-permissions.html

Hopefully that's a step toward what you're after!

Paul Fuller said...

This was helpful--thank you! Your post also gave me a little chuckle. I've often said, "I'm knock out a few things today" or "Here's something I worked up today...", but typically (at least here in the US) to say that you "knocked up" something has a totally different connotation!

sqlreporter said...

how do we get datasource name?

datasource.name as DatasourceName,

from
catalog
inner join datasource on catalog.itemid = datasource.itemid

where would that be incorporated?

Scott Herbert said...

Sorry, I'm not sure what you're referring to?