Get / Query SSRS Report Details from ReportServer database system tables

I have written an utility procedure to query the ReportServer database to get the details of the given SSRS report. You can download the stored procedure from the below link

http://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c

SSRS - Reportserver Database Tables Explored - Part 5 (Final)

In this last post of this series let us look into the below tables.All the below tables are SSRS internal tables which used by the Report Server to stores values needed for it's execution and other operations

1.ConfigurationInfo
2.RunningJobs
3.Keys
4.UpgradeInfo
5.ServerUpgradeHistory
6.DBUpgradeHistory
7.ChunkData
8.ChunkSegmentMapping
9.Segment
10.SegmentedChunk
11.ServerParametersInstance
12.SubscriptionsBeingDeleted

ConfigurationInfo

Has server level configuration info like “EnableRemoteErrors” . If this set to true it will give detailed error from SSRS. This table can be manually updated.

RunningJobs

Internal Table : This stores the jobs currently which is associated with scheduling , snapshot etc

Keys

Internal Table. Has the details about the installation , symmetric encryption key and scale out option keys.

UpgradeInfo

Internal Table. Has the items and status for the items to be taken care during the server upgrade.

ServerUpgradeHistory

Internal Table. Has the details of the previous server version and current server versions in case of upgrades.

DBUpgradeHistory

Internal Table. Has the Database upgrade history for the DB upgrade.

ChunkData

Internal Table : Used for storing the data while delivering the reports using Deliver extensions

ChunkSegmentMapping

Internal Table : Used for storing the data while delivering the reports using Deliver extensions

Segment

Internal Table : Used for storing the data while delivering the reports using Deliver extensions

SegmentedChunk

Internal Table : Used for storing the data while delivering the reports using Deliver extensions

ServerParametersInstance

Internal Table.

SubscriptionsBeingDeleted

Internal Table.


With this the SSRS Report Server Tables explored series ends. Hope this series was usefull. Please feel free to post your comments or add any points to this blog.

Bye... till i catch you in my next Blog !!!

SSRS - Reportserver Database Tables Explored - Part 4

In this post of this series let us take a look into the below tables

1.SnapshotData
2.History
3.ExecutionLogStorage
4.ModelDrill
5.ModelPerspective

SnapshotData

Stores the configuration of the snapshot for the report.

History

Has the history of snapshot of reports with the parameters and other details.

ExecutionLogStorage

Has the execution log for each and every report launched in the reporting server. This table data is help full for performance tuning the report.

Some Important Fields

Parameters	Parameters passed for the report
TimeStart	Start time
TimeEnd	End time
TimeDataRetrieval	Total Reporting time = Timeprocessing + TimeRendering
TimeProcessing	Time taken to retrieve the data from database
TimeRendering	Time taken for rendering the report
Status	Status success or failure

ModelDrill

Stores the details of the custom click through report configured for the model items. This can be set up on ModelsàManage à Click through.

ModelPerspective

Stores the name and the perspectives list created with in the model. A perspective can be created in BIDS by rightclick on Model àNew à Perspective.

This is similar to SSAS perspective to group the attributes.

Sample usage queries
 
1. Get the details of the history pf the snapshot with report name and schedule used to create the snapshot


SELECT c.name,
       h.snapshotdate,
       s.DESCRIPTION,
       s.effectiveparams,
       s.queryparams,
       sc.name,
       sc.nextruntime
  FROM history h
  JOIN snapshotdata s
    ON h.snapshotdataid = s.snapshotdataid
  JOIN catalog c
    ON c.itemid = h.reportid
  JOIN reportschedule rs
    ON rs.reportid = h.reportid
  JOIN schedule sc
    ON sc.scheduleid = rs.scheduleid
 WHERE rs.reportaction = 2 -- Create schedule

2. Get the execution details of a given report with the details like User executing the report , Execution time etc

SELECT c.name,
       CASE e.requesttype
       WHEN 1 THEN 'Subscription'
       WHEN 0 THEN 'Report Launch'
       ELSE ''
       END,
       e.*
  FROM executionlog e
  JOIN catalog c
    ON e.reportid = c.itemid
 WHERE c.name = N'@Reportname' 

SSRS - Reportserver Database Tables Explored - Part 3

Today let us take look into the security related below tables

1. Users
2. Policies
3. Roles
4. PolicyRole
5. CachePolicy
6. SecData
7. ModelItemPolicy
 
Users

Stores the list of users

Some important fields
Usertype - Stores User type
                 0 – system defined
                 1 – User Defined

Policies

Stores a one policy per userdefined object created. If policy flag is 1 , that is system defined policy for the system administrators.

The policyId created will be mapped to each userdefined object in catalog table.

Roles

Stores the Roles related to the Reports. Presently available roles are

Browser	May view folders, reports and subscribe to reports.
Content Manager	May manage content in the Report Server. This includes folders, reports and resources.
Model Item Browser	Allows users to view model items in a particular model.
My Reports	May publish reports and linked reports; manage folders, reports and resources in a users My Reports folder.
Publisher	May publish reports and linked reports to the Report Server. Report
Builder	May view report definitions.
System Administrator	View and modify system role assignments, system role definitions, system properties,and shared schedules.
System User	View system properties, shared schedules, and allow use of Report Builder or other clients that execute report definitions.

PolicyRole

This stores the mapping between the User , Role and policy of a catalog object.

CachePolicy

Stores the cache details in processing options section of reports. If you select donot cache option no records will be inserted in this table.

SecData

Stores the total permission for the reports and other objects in XML format .

ModelItemPolicy

This table stores the data of the access provided at the modelitem level in ModelItemSecurity tab under the Manage model.

Sample usage queries

1. Get the users and their roles mapped for the reports

SELECT c.name,
       u.username,
       u.authtype,
       r.rolename,
       r.DESCRIPTION
 FROM users u
 JOIN policyuserrole pur
   ON u.userid = pur.userid
 JOIN policies p
   ON p.policyid = pur.policyid
 JOIN roles r
   ON r.roleid = pur.roleid
 JOIN catalog c
   ON c.policyid = p.policyid
WHERE c.TYPE = 2 -- For Reports Only
ORDER BY name,username 

2. Get the Cache Policy for the Reports

SELECT c.name,
       cp.cacheexpiration,
       cp.expirationflags
 FROM  cachepolicy cp
 JOIN  catalog c
   ON  c.itemid = cp.reportid

3. Get the Security Details XML from SecData Table

 SELECT c.name,
       CONVERT(XML, sec.xmldescription)
  FROM catalog c
  JOIN secdata sec
    ON c.policyid = sec.policyid
 WHERE c.TYPE = 2 

4. Get the Model Item's User and Role

SELECT c.name,
       mip.modelitemid,
       u.username,
       r.rolename
  FROM catalog c
  JOIN modelitempolicy mip
    ON c.itemid = mip.catalogitemid
  JOIN policies p
    ON p.policyid = mip.policyid
  JOIN policyuserrole pur
    ON p.policyid = pur.policyid
  JOIN users u
    ON u.userid = pur.userid
  JOIN roles r
    ON r.roleid = pur.roleid 

SSRS - Reportserver Database Tables Explored - Part 2

It has been a long gap between my first post and this one. It has been a very tight schedule in the project i have been assigned to.At last got some breathing time to continue the series.

Today let us look into the below tables

1. Subscription
2. Schedule
3. ReportSchedule
4. ActiveSubscription
5. Notifications
6. Event
7. Batch

Subscription

This table stores the subscriptions created by the user.

Some important fields

Description           - Name or detail of the subscription
Laststatus             - Last status of message of the subscription
Eventype              - Event type which has used the subscription
Parameters           - XML value with the parameters used while running the subscription
DeliveryExtension - The extension to which the report is delivered

Schedule

Stores the details of the schedules like shared schedules , TimedSubscription , ReportHistorySchedule

Some important fields

Name                    - Schedule Name
StartDate              - Schedule StartDate
NextRunTime       - Next run time for the schedule
LastRunTime        - Last Run time of the schedule
Endate                 - The end date for the schedule
State                    - State of the Subscription. If state > 2 , the subscription is expired i.e, endate has crossed.
RecurrenceType   - Stores the recurrencetype of the schedule
                              1 - Once
                              2 - Hourly
                              4 - Daily / Weekly
                              6 - Monthly
EventType           - Stores the eventtype
                              SharedSchedule - Shared schedule
                              TimedSubscription - Schedule created within the subscription
                              RefreshCache - Schedule created for refreshing the cache.

ReportSchedule

Stores the mapping between Schedule , Report & Subscription.

ActiveSubscriptions

Stores the subscription notification consolidated results.

Notifications

Stores the notification sent by the subscriptions.

Event

Internal Table. When the scheduled time comes for a scheduler , the SQL Server Agent generates an event by executing the scheduled job. The job inserts a row in the Event table of the ReportServer database. This row serves as an event for the Scheduling and Delivery Processor. The event record will be deleted after the process.

Batch

Internal Table. One event will be assigned as batches and processed. The batch record also will be deleted after process.

Sample usage queries

1. Get the Schedule List with type and Recurrence

SELECT Name
    ,StartDate
    ,EndDate
    ,NextRunTime
    ,LastRunTime
    ,LastRunStatus
    ,RecurrenceType = CASE RecurrenceType
            WHEN  1 THEN 'Once'
            WHEN  2 THEN 'Hourly '
            WHEN  4 THEN 'Daily / Weekly'
            WHEN  6 THEN 'Monthly'
            End
   ,EventType
FROM Schedule

2. Query to get the list of Subscription and it's schedule for a given report

SELECT Reportname = c.Name
      ,SubscriptionDesc=su.Description
      ,Subscriptiontype=su.EventType
      ,su.LastStatus
      ,su.LastRunTime
      ,su.Parameters
      ,Schedulename=sch.Name
      ,sch.Type
      ,sch.EventType
  FROM Subscriptions su
  JOIN Catalog c
    ON su.Report_OID = c.ItemID
  JOIN ReportSchedule rsc
    ON rsc.ReportID = c.ItemID
   AND rsc.SubscriptionID = su.SubscriptionID
  JOIN Schedule Sch
    ON rsc.ScheduleID = sch.ScheduleID
 WHERE c.Name = '@ReportName'

3. Query to get the notification details sent for the given report

SELECT C.Name
      ,S.Description
      ,N.NotificationEntered
      ,A.TotalNotifications
      ,A.TotalSuccesses
      ,A.TotalFailures
  FROM Notifications N
  JOIN ActiveSubscriptions A
    ON N.SubscriptionID = A.SubscriptionID
   AND N.ActivationID = A.ActiveID
  JOIN Catalog C
    ON C.ItemID = N.ReportID
  JOIN Subscriptions S
    ON S.SubscriptionID = N.SubscriptionID
 WHERE c.Name = '@ReportName'

SSRS - Reportserver Database Tables Explored - Part 1

Greetings!!! This is the first of the series of blogs about the metadata tables available in SSRS Reportserver Database. Reportserver database has all the details about reports deployed in the Report manager by storing them internally in tables.Let us take closer look on the tables and what it stores in it.In this blog , i will just give a small brief about tables and what kind of data it stores. And then provide sample queries as much as possible for better understanding. As a first installement , Let us take a look at below 3 tables.

                                              1. Catalog
                                              2. DataSource
                                              3. DataSets

Catalog

Stores the master data about all the objects related to SSRS listed below
 
1 = Folder
2 = Report (.rdl)
3 = .XML
4 = Link Report
5 = Data Source (.rds)
6 = Model
8 = Shared Dataset
9 = Report Part

This is the base master table where all the objects related to the SSRS is stored.

Some important fields
Parentid   - Under which object this object is present.
Type         - Type of the object as metioned above.
Content    - Has the report xml stored in varbinary format.
Properties - Has the XML with the properties of the object.
Parameter - Has the XML with parameters used in the reports.

  

DataSource
Stores the list of shared and embedded data sources available.

Some important fields
ItemId - Reference to the catalog table
Link     - Reference to the Report to which this datasource is linked.

  

DataSets
Stores the list of the shared datasets available.

Some important fields

ItemId - Reference to the catalog table.

LinkId  - Reference to the Report to which this datasource is linked.
Name   - DataSet Name in the report.

Sample usage queries

1. To View the Report code in XML format from content field in Catalog Table

SELECT  Name,Convert(XML,(Convert(VARBINARY(MAX),Content))) AS ReportXML
  FROM  ReportServer.dbo.Catalog
 WHERE  Content IS NOT NULL
   AND  [Type] = 2 -- For Report objects alone


2. Get the Report's Created/Modified User and Created/Modified Dates.

SELECT Name
      ,CreatedBy = U.UserName
      ,CreationDate = C.CreationDate
      ,ModifiedBy = UM.UserName
      ,ModifiedDate
  FROM Reportserver.dbo.Catalog C
  JOIN Reportserver.dbo.Users U
    ON C.CreatedByID = U.UserID
  JOIN Reportserver.dbo.Users UM
    ON c.ModifiedByID = UM.UserID
 WHERE Name = 'ReportName'

3. Get the List of Report Parameters for the given Report

SELECT  Name = Paravalue.value('Name[1]', 'VARCHAR(250)')
       ,Type = Paravalue.value('Type[1]', 'VARCHAR(250)')
       ,Nullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)')
       ,AllowBlank = Paravalue.value('AllowBlank[1]', 'VARCHAR(250)')
       ,MultiValue = Paravalue.value('MultiValue[1]', 'VARCHAR(250)')
       ,UsedInQuery = Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)')
       ,Prompt = Paravalue.value('Prompt[1]', 'VARCHAR(250)')
       ,DynamicPrompt = Paravalue.value('DynamicPrompt[1]', 'VARCHAR(250)')
       ,PromptUser = Paravalue.value('PromptUser[1]', 'VARCHAR(250)')
       ,State = Paravalue.value('State[1]', 'VARCHAR(250)')
 FROM (
     SELECT C.Name,CONVERT(XML,C.Parameter) AS ParameterXML
       FROM  ReportServer.dbo.Catalog C
      WHERE  C.Content is not null
        AND  C.Type  = 2
        AND  C.Name  =  'ReportName'
    ) a
CROSS APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue )

4. Get the Data Sources used in the Report
Note : The XML Schema used here is for SQL 2011. You need to change it to make it work for other versions. To see the schema use the first query in the samples list.

WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT  ReportName     = name
       ,DataSourceName   = x.value('(@Name)[1]', 'VARCHAR(250)')
       ,DataProvider   = x.value('(ConnectionProperties/DataProvider)[1]','VARCHAR(250)')
       ,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
  FROM (  SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
           FROM  ReportServer.dbo.Catalog C
          WHERE  C.Content is not null
            AND  C.Type  = 2
      AND  C.Name  = 'ReportName'
        ) a
  CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') r ( x )
 ORDER BY name ;

5. Get the Data Sets used in the Report.
Note : The XML Schema used here is for SQL 2011. You need to change it to make it work for other versions. To see the schema use the first query in the samples list.

WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT  ReportName    = name
       ,DataSetName    = x.value('(@Name)[1]', 'VARCHAR(250)')
       ,DataSourceName  = x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
       ,CommandText    = x.value('(Query/CommandText)[1]','VARCHAR(250)')
       ,Fields      = df.value('(@Name)[1]','VARCHAR(250)')
       ,DataField    = df.value('(DataField)[1]','VARCHAR(250)')
       ,DataType    = df.value('(rd:TypeName)[1]','VARCHAR(250)')
  FROM (  SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
           FROM  ReportServer.dbo.Catalog C
          WHERE  C.Content is not null
            AND  C.Type = 2
         AND  C.Name = 'ReportName'
     ) a
  CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
  CROSS APPLY x.nodes('Fields/Field') f(df)
ORDER BY name

6. Get the list of Data Sources used by Reports using DataSources Table.

SELECT D.Name
       ,'Using Report '
      = CASE
        WHEN D.Name IS NOT NULL THEN  C.Name
        ELSE 'Shared Data Source'
        END
      ,'IsSharedDataSource' = CLink.Name
     FROM DataSource D
     JOIN Catalog C
     ON D.ItemID = C.ItemID
LEFT JOIN Catalog CLink
     ON Clink.ItemID = D.Link
  WHERE C.Name = 'ReportName'

I will continue with next set of tables in Part 2.  Bye for now !!!