SSRS 如何根据查询语句Query找到使用该查询的报表Report
生成环境中，经常会捕获到一些消耗CPU和内存资源较多的Query，有一些来自某个APP，有一些来Client，还有一些来自报表服务器。通常报表服务器连接过来的都是通过配置好的共享DataSource, 所以很难判断是谁。 下面介绍一下我是如何根据Query快速找到Report Owner：
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。
第一步： 将SSRS服务器所有Report的定义信息取出，并转换成可读的XML类型：
SELECT
[Path]
,CASE [Type]
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
END AS TypeName
,CAST(CAST(content AS varbinary(max)) AS xml) as command
, [Description] into #temp1
FROM PBIReportServer.dbo.[Catalog] CTG
WHERE
[Type] IN (2, 5)
第二步：将XML字段中Dataset中CommandText取出：
SELECT *, command.value('
declare namespace ns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition";
(/ns:Report/ns:DataSets/ns:DataSet/ns:Query/ns:CommandText)[1] ', 'varchar(max)') as Result into #temp2
FROM #temp1
SELECT *
from #temp2
where Result like '%query statement%'
第三步：根据上面找到的Report Path，找到 Report Owner：
SELECT
ItemID -- Unique Identifier
, [Path] --Path including object name
, [Name] --Just the objectd name
, ParentID --The ItemID of the folder in which it resides
, CASE [Type] --Type, an int which can be converted using this case statement.
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
WHEN 6 THEN 'Report Model - Rare'
WHEN 7 THEN 'Report Part - Rare'
WHEN 8 THEN 'Shared Data Set - Rare'
WHEN 9 THEN 'Image'
ELSE CAST(Type as varchar(100))
END AS TypeName
--, content
, LinkSourceID --If a linked report then this is the ItemID of the actual report.
, [Description] --This is the same information as can be found in the GUI
, [Hidden] --Is the object hidden on the screen or not
, CreatedBy.UserName CreatedBy
, CreationDate
, ModifiedBy.UserName ModifiedBy
FROM
[PBIReportServer].dbo.[Catalog] CTG
INNER JOIN
[PBIReportServer].dbo.Users CreatedBy ON CTG.CreatedByID = CreatedBy.UserID
INNER JOIN
[PBIReportServer].dbo.Users ModifiedBy ON CTG.ModifiedByID = ModifiedBy.UserID
where path in (
'/Contoso/Report1',
'/Contoso/Report2')
