SSRS usage Part1

----------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