报告服务本地使用情况跟踪

Question

我想跟踪本地服务器上的 SSRS 和 Power BI 报表的使用情况。应用服务提供这些指标，但本地版本位于 SQL Server 上，因此必须使用 ExecutionLog3 表。

该表更难查询 PBIX 报告，因为它并不直接说明真正的"使用情况"是什么

Answer 1

可能有更好的方法来编写它，但它使你非常接近跟踪所有报表的本地使用情况 - SSRS 和 Power BI

declare @results TABLE
(
Report  NVARCHAR(50),
ExecutionTime DATETIME,
UserName NVARCHAR(25),
ExecutionId NVARCHAR(50)
)

INSERT INTO @results 
SELECT
Name,
TimeStart,
UserName,
ExecutionId
FROM (SELECT TimeStart,
Catalog.Name,
UserName,
ExecutionId
FROM Catalog
INNER JOIN 
ExecutionLog3
ON Catalog.Path = ExecutionLog3.ItemPath
WHERE Type IN (2) --ssrs
) AS RE
UNION ALL
SELECT 
Name,
TimeStart,
UserName,
ExecutionId
FROM (SELECT  TimeStart,
Catalog.Type,
Catalog.Name,
UserName,
ExecutionId
FROM Catalog
INNER JOIN 
ExecutionLog3
ON Catalog.Path = ExecutionLog3.ItemPath
WHERE Type IN (13) --power BI
AND ItemAction = 'ConceptualSchema'
AND Format = 'PBIX'
) AS RE
Order By TimeStart DESC
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ExecutionId ORDER BY ExecutionTime DESC) AS rn
FROM @results
)
SELECT
Report,
ExecutionTime,
UserName
FROM cte
WHERE rn = 1
ORDER BY ExecutionTime DESC