Tapping Into Your Reporting Services Database

Southhill

For every instance of SQL Server Reporting Services (2005+) there are two databases, a primary and a TempDB.  The primary database holds all of the data for your instance, such as: Users, Folder Structure, Roles, Security, Subscriptions, Data Sources, etc.  There a lot of data here that you can use to clean up your Reporting Services instances, or help keep them in order.  Also, from my experience the database doesn’t change much from 2005 to 2008, so these should work on both versions.  I’d be willing to bet they’ll work on 2008 R2 as well.  If they don’t, let me know.
The very first view I made using data from the Reporting Services database was a list of Report Executions.  This view was great because I could see which reports were actually being used and which weren’t, and who was using which reports.  I made a report off of this view and provided it to a manager so they could track to make sure the other managers were checking their employee hours report.

1.     CREATE VIEW [dbo].[ExecutionLogView]
   
2.     AS    SELECT TOP (100) PERCENT
   
3.                 dbo.ExecutionLog.UserName,
   
4.                 dbo.Catalog.Path,
   
5.                 dbo.Catalog.Name,
   
6.                 dbo.ExecutionLog.TimeStart,
   
7.                 dbo.ExecutionLog.TimeDataRetrieval
   
8.           FROM
   
9.                 dbo.ExecutionLog
   
10.                 INNER JOIN dbo.Catalog
    
11.                       ON dbo.ExecutionLog.ReportID = dbo.Catalog.ItemID
    
12.           ORDER BY
    
13.                 dbo.ExecutionLog.TimeStart DESC

复制代码

One of the issues I had with one of my instances was subscriptions.  There were scheduled subscriptions everywhere and a lot of them ran at the same time, but they weren’t on a shared schedule.

1.     create view dbo.ReportSubscriptionView
   
2.     as
   
3.     SELECT TOP (100) PERCENT
   
4.           dbo.Catalog.ItemID as ReportID,
   
5.           dbo.Catalog.Path,
   
6.           dbo.Catalog.Name,
   
7.           dbo.Catalog.Description,
   
8.           dbo.Catalog.Hidden,
   
9.           dbo.Subscriptions.SubscriptionID,
   
10.           dbo.Subscriptions.OwnerID,
    
11.           dbo.Subscriptions.Description AS SubscriptionDesc,
    
12.           dbo.Subscriptions.LastStatus,
    
13.           dbo.Subscriptions.EventType,
    
14.           dbo.Subscriptions.LastRunTime,
    
15.           dbo.Subscriptions.Parameters,
    
16.           dbo.Subscriptions.DataSettings,
    
17.           dbo.Subscriptions.DeliveryExtension,
    
18.           dbo.ReportSchedule.ScheduleID,
    
19.           dbo.Schedule.Name AS ScheduleName,
    
20.           dbo.Schedule.NextRunTime,
    
21.           dbo.Schedule.LastRunTime AS LastScheduleRun,
    
22.           dbo.Schedule.EndDate,
    
23.           dbo.Schedule.MinutesInterval,
    
24.           dbo.Schedule.DaysInterval,
    
25.           dbo.Schedule.WeeksInterval,
    
26.           dbo.Schedule.DaysOfWeek,
    
27.           dbo.Schedule.DaysOfMonth,
    
28.           dbo.Schedule.Month,
    
29.           dbo.Schedule.MonthlyWeek,
    
30.           dbo.Schedule.EventType AS ScheduleType,
    
31.           dbo.Schedule.ConsistancyCheck,
    
32.           dbo.Users.UserName as ModifiedBy
    
33.     FROM
    
34.           dbo.Subscriptions
    
35.           INNER JOIN dbo.Catalog
    
36.                 ON dbo.Subscriptions.Report_OID = dbo.Catalog.ItemID
    
37.           INNER JOIN dbo.Schedule
    
38.           INNER JOIN dbo.ReportSchedule
    
39.                 ON dbo.Schedule.ScheduleID = dbo.ReportSchedule.ScheduleID
    
40.                 ON dbo.Subscriptions.SubscriptionID = dbo.ReportSchedule.SubscriptionID
    
41.           INNER JOIN dbo.Users
    
42.                 ON dbo.Subscriptions.ModifiedByID = dbo.Users.UserID
    
43.     ORDER BY
    
44.           dbo.Catalog.Path,
    
45.           dbo.Catalog.Name

复制代码

I discovered that SSRS creates a SQL Agent Job for every schedule subscription that isn’t on a shared subscription, which explained the Job explosion I had as well on the server.  I went snooping and discovered that the Job that SSRS created is named the same as the ScheduleID in the above view.  I used that view and that knowledge to create a few shared schedules and then using the view I found all of the relevant subscriptions in Report Manager and updated them to use the shared subscription.  This reduced the number of jobs I had, and the number of different subscriptions I had.  I was also able to identify subscriptions that were no longer used.
One other view that I found useful was a view of the whole directory and the related security.

1.     create view [dbo].[CatalogSecurity] as
   
2.     select  top (100) percent
   
3.         c.ItemID,
   
4.         c.Path,
   
5.         c.Name,
   
6.         c.Type,
   
7.         c.PolicyID,
   
8.         u.UserName,
   
9.         r.RoleName
   
10.     from dbo.Catalog c
    
11.         left outer join dbo.PolicyUserRole pcr
    
12.             on c.PolicyID=pcr.PolicyID
    
13.         left outer join dbo.Users u
    
14.             on pcr.UserID=u.UserID
    
15.         left outer join dbo.Roles r
    
16.             on pcr.RoleID=r.RoleID
    
17.     order by Path, Type, UserName, RoleName

复制代码

With this view, I could see who all had what rights to specific folders and reports.  One of my initiatives has been to remove security from a by-report policy to a by-folder policy, making security a lot easier to manage.
Well, those are the main ones I’ve used.  If you’ve got other useful views into the Reporting Services data, let me know, I’d love to see them.

by David Forck (thirster42)
http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/tapping-into-your-reporting-services-dat


SQLDenis:
                                                You know that those views won't be sorted right? Since SQL Server 2005 this has changed, see here: Create a sorted view in SQL Server 2005 and SQL Server 2008       
                                        11/19/10 @ 15:52       

Southhill

A more system db related(msdb) script as follow for deep timely schedule information.

1. USE [tmpData]
   
2. 
   
3. SELECT     TOP (100) PERCENT msdb.schedule_id, msdb.next_run_date, msdb.next_run_time,
   
4.   SUBSTRING(ReportServer.dbo.Subscriptions.ExtensionSettings,
   
5.             CHARINDEX('<Name>FILENAME</Name><Value>',
   
6.              ReportServer.dbo.Subscriptions.ExtensionSettings)
   
7.               + LEN('<Name>FILENAME</Name><Value>'),
   
8.             CHARINDEX('</Value>', ReportServer.dbo.Subscriptions.ExtensionSettings,
   
9.             CHARINDEX('<Name>FILENAME</Name><Value>',
   
10.               ReportServer.dbo.Subscriptions.ExtensionSettings))
    
11.                - CHARINDEX('<Name>FILENAME</Name><Value>',
    
12.               ReportServer.dbo.Subscriptions.ExtensionSettings)- LEN('<Name>FILENAME</Name><Value>')
    
13.   ) AS Title, ReportServer.dbo.Schedule.ScheduleID, ReportServer.dbo.Subscriptions.SubscriptionID,
    
14.   ReportServer.dbo.Subscriptions.Description, ReportServer.dbo.Subscriptions.LastStatus,
    
15.   ReportServer.dbo.Subscriptions.LastRunTime, ReportServer.dbo.Subscriptions.Version,
    
16.   ReportServer.dbo.Schedule.Name, ReportServer.dbo.Schedule.StartDate,
    
17.   ReportServer.dbo.Schedule.LastRunTime AS LastRun, ReportServer.dbo.Schedule.RecurrenceType,
    
18.   ReportServer.dbo.Schedule.DaysOfMonth, ReportServer.dbo.Schedule.Month,
    
19.   ReportServer.dbo.Schedule.State,
    
20.   ReportServer.dbo.Schedule.Type, ReportServer.dbo.Schedule.EventData,
    
21.   ReportServer.dbo.Subscriptions.ModifiedDate
    
22. FROM   ReportServer.dbo.Subscriptions INNER JOIN
    
23.        ReportServer.dbo.ReportSchedule
    
24.        ON ReportServer.dbo.Subscriptions.SubscriptionID
    
25.           = ReportServer.dbo.ReportSchedule.SubscriptionID
    
26.        INNER JOIN
    
27.        ReportServer.dbo.Schedule
    
28.        ON ReportServer.dbo.ReportSchedule.ScheduleID = ReportServer.dbo.Schedule.ScheduleID
    
29.        INNER JOIN
    
30.              (SELECT     jobs.name, ms_schedule.schedule_id, ms_schedule.next_run_date,
    
31.                          ms_schedule.next_run_time, ms_schedule.job_id
    
32.               FROM          msdb.dbo.sysjobschedules AS ms_schedule LEFT OUTER JOIN
    
33.                             msdb.dbo.sysjobs AS jobs
    
34.                             ON jobs.job_id = ms_schedule.job_id
    
35.               WHERE      (ms_schedule.next_run_date = 20120301) AND (ms_schedule.schedule_id > 400)
    
36.               ) AS msdb
    
37.        ON msdb.name = CAST(ReportServer.dbo.Schedule.ScheduleID AS nvarchar(256))
    
38. WHERE     (ReportServer.dbo.ReportSchedule.ReportID = '2563B943-71AF-40D1-82C5-E2BB2C6A0655')
    
39. ORDER BY
    
40. --ReportServer.dbo.Subscriptions.SubscriptionID,
    
41. Title, msdb.schedule_id desc, LEFT(ReportServer.dbo.Schedule.ScheduleID, 8)
    

复制代码

Result may looks like:

schedule_id        next_run_date        next_run_time        Title        ScheduleID        SubscriptionID        Description        LastStatus        LastRunTime        Version        Name        StartDate        LastRun        RecurrenceType        DaysOfMonth        Month        State        Type        EventData        ModifiedDate
1183        20120301        123000        All Units - 4th UW Sales Forecasting - @Timestamp        228B2F87-2117-4ABF-9838-3D20AB8891BD        BACB4839-9ABC-4CA4-A931-8D0DC718870C        Save in \\reports\Sales\DataLib\Forecast as All Units - 4th UW Sales Forecasting - @Timestamp        The file "All Units - 4th UW Sales Forecasting - 2012_02_01_013347.xls" has been saved to the "\\reports\Sales\DataLib\Forecast" file share.        2012-02-01 01:01:14.107        3        23a5eac2-ae95-43ee-acbe-b93180fb446e        2012-01-09 12:30:00.000        2012-02-01 01:01:13.880        5        402653185        4095        1        1        bacb4839-9abc-4ca4-a931-8d0dc718870c        2012-02-21 15:26:06.680
1204        20120301        123000        All Units - Alberta Sales Forecasting - @Timestamp        F50F4C2C-1644-4DDF-9972-83BEAFD0C97C        07BDA22F-362B-480A-BC84-5D27D2948226        Save in \\reports\Sales\DataLib\Forecast as All Units - Alberta Sales Forecasting - @Timestamp        The file "All Units - Alberta Sales Forecasting - 2012_02_01_010913.xls" has been saved to the "\\reports\Sales\DataLib\Forecast" file share.        2012-02-01 01:00:51.693        3        60bcff43-3ed4-4c16-ac2b-af382d1132ac        2012-01-09 12:30:00.000        2012-02-01 01:00:51.620        5        402653185        4095        1        1        07bda22f-362b-480a-bc84-5d27d2948226        2012-02-21 15:41:43.000
1207        20120301        123000        All Units - All Divisions Sales Forecasting - @Timestamp        6128A5E0-41FA-45C3-A168-75373C382088        5B637557-48C0-4387-9DB1-FADA3DEB33E7        Save in \\reports\Sales\DataLib\Forecast as All Units - All Divisions Sales Forecasting - @Timestamp        The file "All Units - All Divisions Sales Forecasting - 2012_02_01_012025.xls" has been saved to the "\\reports\Sales\DataLib\Forecast" file share.        2012-02-01 01:01:01.660        3        57b8981b-f18a-42a8-abb8-3f584b385538        2012-01-10 12:30:00.000        2012-02-01 01:01:01.397        5        402653185        4095        1        1        5b637557-48c0-4387-9db1-fada3deb33e7        2012-02-21 15:42:08.237