-- -------------------------------------------------------------------
-- 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