SSRS 如何根据查询语句Query找到使用该查询的报表Report

生成环境中，经常会捕获到一些消耗CPU和内存资源较多的Query，有一些来自某个APP，有一些来Client，还有一些来自报表服务器。通常报表服务器连接过来的都是通过配置好的共享DataSource, 所以很难判断是谁。下面介绍一下我是如何根据Query快速找到Report Owner：

第一步：将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')

附：解析XML的另一个方法：

--drop table #temp1
--go

;
WITH ItemContentBinaries AS
(
SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
    When 13 Then 'Power BI Report'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
FROM PBIReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8,13) --AND ItemID = @ItemID
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
SELECT
     ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
     ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText into #temp1
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

select * from #temp1

SSRS 如何根据查询语句Query找到使用该查询的报表Report

推荐阅读