----------1.Query reporting tables to see usage
SELECT
Catalog.Name AS ReportName,
Catalog.Path AS ReportPath,
Catalog.Type AS ReportType,
Catalog.CreationDate AS CreatedDate,
COUNT(ExecutionLog.InstanceName) AS UsageCount
FROM
Catalog
LEFT JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
WHERE
Catalog.Type = 2 -- 2 corresponds to reports in the Catalog
GROUP BY
Catalog.Name, Catalog.Path, Catalog.Type, Catalog.CreationDate
ORDER BY
UsageCount DESC;
------------2.follow up on longest used report
USE [ReportServer]
SELECT c.[name] as reportName
,e.username as userExec
,e.TimeStart
,e.TimeEnd
,DATEDIFF(ss,e.TimeStart,e.TimeEnd) as TimeInSeconds
,e.Parameters
,c.ModifiedDate as ReportLastModified
,u.username as userCreated
FROM catalog c
INNER JOIN executionlogstorage e on c.itemid = e.reportid
INNER JOIN users u on c.modifiedbyid = u.userid
where DATEDIFF(ss,e.TimeStart,e.TimeEnd) != 0
ORDER BY 5 DESC
------------3.follow up on least used report
SELECT
Catalog.Name AS ReportName,
Catalog.Path AS ReportPath,
Catalog.Type AS ReportType,
Catalog.CreationDate AS CreatedDate,
COUNT(ExecutionLog.InstanceName) AS UsageCount
FROM
Catalog
LEFT JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
WHERE
Catalog.Type = 2 -- 2 corresponds to reports in the Catalog
GROUP BY
Catalog.Name, Catalog.Path, Catalog.Type, Catalog.CreationDate
ORDER BY
UsageCount ASC;
-------- 4.follow up on never used report
USE ReportServer; -- Assuming the name of your ReportServer database
SELECT
Catalog.Name AS ReportName,
Catalog.Path AS ReportPath,
Catalog.CreationDate AS CreatedDate
FROM
Catalog
LEFT JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
WHERE
Catalog.Type = 2 -- 2 corresponds to reports in the Catalog
AND ExecutionLog.ReportID IS NULL
ORDER BY
Catalog.CreationDate DESC;
---5.Archieved report or process no longer used
USE [ReportServer]; -- Replace [ReportServer] with your SSRS database name
SELECT
C.ItemID AS ReportID,
C.Name AS ReportName,
C.Path AS ReportPath
FROM
Catalog C
LEFT JOIN
ExecutionLog L ON C.ItemID = L.ReportID
WHERE
L.InstanceName IS NULL
AND C.Type = 2; -- 2 represents reports in the Catalog, you may need to adjust this based on your specific setup
-------6 write a query to review reports and determine how many are using inline sql queries still versus stored procedure write a query to review reports and determine how many are using
------------inline sql queries still versus stored procedure
