Find Reporting Services DATA

--Find the procedure names that are being used along with report, datasource names
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText,
x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'
ORDER BY name
--Find the procedure names that are used in reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
distinct x.value('CommandText[1]','VARCHAR(50)') AS CommandText
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'
--Get all text adhoc sql in the reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText,
x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type != 3
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') is null --= 'CommandText'
ORDER BY name--Find the distinct reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
distinct name
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
ORDER BY name

--Get the data sources
select Path, Name from dbo.Catalog
where Type = 5
--Get the report names and their paths
select Path, Name from dbo.Catalog
where Type = 2
--Get the supporting docs used in the reports
select Path, Name from dbo.Catalog
where Type = 3
--Is there a cache policy to expire after n minutes?
select Path, Name, ExpirationFlags, CacheExpiration
from dbo.Catalog c join dbo.CachePolicy cp
on c.ItemID = cp.reportID
where ExpirationFlags = 1
--Is there a cache policy to expire on a report specific/shared schedule?
select Path, Name, ExpirationFlags, CacheExpiration
from dbo.Catalog c join dbo.CachePolicy cp
on c.ItemID = cp.reportID
where ExpirationFlags = 2
--Which users are using these reports
select UserName from dbo.Users
where UserName not in
('Everyone', 'NT AUTHORITY\SYSTEM', 'NT AUTHORITY\NETWORK SERVICE',
'BUILTIN\Administrators', 'Domain Users')
--find the favorite rendering types
select format, count(1) as cnt
from dbo.ExecutionLog
group by format
order by cnt
--Get the most frequently used reports
select c.Path, c.Name, count(1) as cnt
from dbo.ExecutionLog e join dbo.Catalog c
on e.ReportID = c.ItemID
group by c.Path, c.Name
order by cnt desc
--Get the reports that take longer to retrieve the data
select c.Path, c.Name, max(TimeDataRetrieval)/1000. as seconds
from dbo.ExecutionLog e join dbo.Catalog c
on e.ReportID = c.ItemID
group by c.Path, c.Name
order by seconds desc
--Get the most frequent users
select UserName, count(1) as cnt from dbo.ExecutionLog
group by UserName
order by cnt desc
--Get the latest subscription reports delivered
select c.Path, c.Name, LastRunTime, replace(LastStatus, 'Mail sent to ','') as lastStatus
from dbo.Subscriptions s join dbo.Catalog c
on s.Report_OID = c.ItemID
order by LastRunTime desc
--get users and thier roles
select UserName, RoleName, Description
from dbo.Roles r join dbo.PolicyUserRole pur
on r.roleid = pur.roleid
join dbo.Users u
on pur.userid = u.userid
where UserName not in ('BUILTIN\Administrators', 'Domain Users', 'IUSR_REPORT02')
order by UserName