USE [ReportServer]
GO
/*
Query 1:
Specify report name looking for and list of parameters for report with timings in milliseconds
ordered by the total number of times parameter selection made in report (occurrences)
*/
DECLARE @ReportName nvarchar(200) = '%REPORTNAME%' --Set Report name
DECLARE @OccurancesOver int = 5 --Set occurance level
DECLARE @PeriodRange datetime = dateadd(month,-1,getdate()) --Last month
SELECT
[ItemPath]
,[Parameters]=replace(replace(cast([Parameters] as nvarchar(max)),'&',' / '),'%26','&')
,[Status]
,TotalOccurances = count(*)
,TimeDataRetrieval=sum([TimeDataRetrieval])
,TimeProcessing=sum([TimeProcessing])
,TimeRendering=sum([TimeRendering])
,TotalTime=sum([TimeDataRetrieval])+sum([TimeProcessing])+sum([TimeRendering])
FROM [dbo].[ExecutionLog3]
WHERE ItemPath like @ReportName
AND [Source] = 'Live' --Live queries only not already caching
AND [Status] = 'rsSuccess' -- ONLY successful
AND [TimeStart] >= @PeriodRange
GROUP BY
[ItemPath]
,replace(replace(cast([Parameters] as nvarchar(max)),'&',' / '),'%26','&')
,[Status]
HAVING count(*) >= @OccurancesOver
ORDER BY count(*) desc
/*
Query 2:
List all subscriptions and status
*/
SELECT
[Description]
,[EventType]
,[LastStatus]
,[LastRunTime]
FROM [dbo].[Subscriptions]
WHERE [Description] <> ''
order by [LastRunTime] desc
DECLARE @ReportName nvarchar(200) = '%REPORTNAME%' --Set Report name
ReplyDeleteDECLARE @OccurancesOver int = 0 --Set occurance level
DECLARE @PeriodRange datetime = dateadd(month,-1,getdate()) --Last month
SELECT
[ItemPath]
,[Parameters]=replace(replace(cast([Parameters] as nvarchar(max)),'&',' / '),'%26','&')
,[Status]
,[Source]
,[RequestType]
,TotalOccurances = count(*)
,TimeDataRetrieval=sum([TimeDataRetrieval])
,TimeProcessing=sum([TimeProcessing])
,TimeRendering=sum([TimeRendering])
,TotalTime=sum([TimeDataRetrieval])+sum([TimeProcessing])+sum([TimeRendering])
,TimeRan=max(TimeEnd)
FROM [dbo].[ExecutionLog3]
WHERE ItemPath like @ReportName
--AND [Source] = 'Live' --Live queries only not already caching
--AND [Status] = 'rsSuccess' -- ONLY successful
AND [TimeStart] >= @PeriodRange
GROUP BY
[ItemPath]
,replace(replace(cast([Parameters] as nvarchar(max)),'&',' / '),'%26','&')
,[Status]
,[Source]
,[RequestType]
HAVING count(*) >= @OccurancesOver
ORDER BY max(TimeEnd) desc,count(*) desc
ReplyDeleteSELECT
'EXEC ReportServer2.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' + CAST(a.SubscriptionID AS NVARCHAR(500)) + '''' AS ReportCommand,
b.name AS JobName
, e.name
, e.path
, d.description
, a.SubscriptionID
, laststatus
, eventtype
, LastRunTime
, date_created
, date_modified
FROM
ReportServer2.dbo.ReportSchedule a
JOIN msdb.dbo.sysjobs b ON cast(a.ScheduleID as varchar(50)) = b.name
JOIN ReportServer2.dbo.ReportSchedule c ON b.name = cast(c.ScheduleID as varchar(50))
JOIN ReportServer2.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID
JOIN ReportServer2.dbo.Catalog e ON d.report_oid = e.itemid
WHERE
e.name like 'MYREPORTNAME%'