SSRS Queries for a DBA or Developers to monitor Reporting Server

The Script to Check Reports and Subcriptions with Sorted results per last Run Date

SELECT c.Name AS ReportName
, rs.ScheduleID AS JOB_NAME
, s.[Description]
, s.LastStatus
, s.LastRunTime
FROM ReportServer..[Catalog] c
JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID
jOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
AND rs.SubscriptionID = s.SubscriptionID
order by 5

To Check Latest Running reports with Full details of who is using reports sevices at what time and which report,…. etc..

SELECT [InstanceName]
,b.name
,[UserName]
,[RequestType]
,[Format]
,[Parameters]
,convert(varchar(10),[TimeStart],103)+' '+convert(varchar(8),[TimeStart],108) as TimeStart
,convert(varchar(10),[TimeEnd],103) +' '+convert(varchar(8),[TimeEnd],108) as TimeEnd
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
FROM [ReportServer].[dbo].[ExecutionLog] a
inner join [ReportServer].[dbo].[Catalog] b
on a.reportid=b.itemid

Leave a Reply

Your email address will not be published. Required fields are marked *