Retrieve SSRS Report Definition, Role Assignments, Executions Using T-SQL

Just a few queries against the ReportServer database that might come in handy…
Of course, these are just some sample queries. Tune and modify according to your needs …

-- -------------------------------------------------------------------
-- get report definitions
-- -------------------------------------------------------------------
-- Type
-- 1 Folder
-- 2 Report
-- 3 Resource
-- 4 Linked Report
-- 5 Data Source
-- 6 Model
-- report definition is stored as VARBINARY, so we need to convert it
-- to something readable
SELECT
    ItemID,
    [Path],
    [Name],
    CONVERT(VARCHAR(MAX), 
            CONVERT(NVARCHAR(MAX), 
            CONVERT(XML, CONVERT(VARBINARY(MAX), Content)))) 
	AS [ReportDefinition]

FROM [ReportServer].[dbo].[Catalog]
WHERE [Type] = 2

-- -------------------------------------------------------------------
-- check what roles/permissions are assigned to folders and reports
-- -------------------------------------------------------------------
SELECT 
	CASE RSCatalog.Type
		WHEN 1 THEN 'Folder'
		ELSE 'Report'
	END AS [Type],
	RSCatalog.Path,
	RSCatalog.Name AS Report,
	Users.UserName,
	Roles.RoleName
--	*
FROM 
	[ReportServer].[dbo].[Catalog] RSCatalog
	INNER JOIN [ReportServer].[dbo].[PolicyUserRole] PolicyUserRole
	ON RSCatalog.PolicyID = PolicyUserRole.PolicyID
	INNER JOIN [ReportServer].[dbo].[Roles] Roles
	ON PolicyUserRole.RoleID = Roles.RoleID
	INNER JOIN [ReportServer].[dbo].[Users] Users
	ON PolicyUserRole.UserID = Users.UserID
WHERE
	RSCatalog.Type IN (1,2)
ORDER BY 
	RSCatalog.Path,
	RSCatalog.Name,
	Users.UserName

-- -------------------------------------------------------------------
-- check last 100 report executions, requestors, parameters and outcome
-- -------------------------------------------------------------------
SELECT 
	TOP 100 
	ExecutionLog.TimeStart,
	ExecutionLog.Status,
	RSCatalog.Path,
	RSCatalog.Name AS Report,
	ExecutionLog.UserName,
	ExecutionLog.Format,
	ExecutionLog.Parameters		
FROM 
	[ReportServer].[dbo].[ExecutionLog] ExecutionLog
	INNER JOIN [ReportServer].[dbo].[Catalog] RSCatalog
	ON ExecutionLog.ReportID = RSCatalog.ItemID
ORDER BY 
	ExecutionLog.TimeStart DESC


Advertisements

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s