One of the first things I wanted to know was what subscriptions had run that did not succeed. This query should be run on the server that hosts your ReportServer Database. I'm running these on SQL Server 2008R2
USE [ReportServer]
--GET THE SERVER NAME
DECLARE @ServerURL VARCHAR(MAX)
SELECT TOP 1 @ServerURL =[MachineName]
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL
IF CHARINDEX('\',@ServerURL) > 0
SET @ServerURL = LEFT(@ServerURL,CHARINDEX('\',@ServerURL)-1)
SET @ServerURL = 'http://' + @ServerURL + '/Reports/Pages'
--FIND THE NON-SUCCESSFUL SUBSCRIPTIONS
;WITH AgentJobsCTE AS (
SELECT [SJ].job_id
,[SJ].name AS AgentJobName
,[SJS].[command]
,CAST(REPLACE(REPLACE(REPLACE([SJS].[command],'exec [ReportServer].dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''',''),'exec ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''',''), '''', '') AS SYSNAME) AS SubscriptionID
FROM msdb.[dbo].[sysjobs] AS SJ
JOIN msdb.[dbo].[sysjobsteps] AS SJS ON [SJ].[job_id] = [SJS].[job_id]
WHERE SJS.[command] LIKE '%TimedSubscription%'
)
SELECT O.[UserName] AS OwnerName,M.[UserName] AS ModifiedBy,C.[Name] AS ReportName,[AJ].AgentJobName,S.[SubscriptionID],S.[LastStatus],S.[LastRunTime],S.[ModifiedDate],S.[Report_OID]
,@ServerURL + '/SubscriptionProperties.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(MAX)) AS SubscriptionURL
,@ServerURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM [ReportServer].[dbo].[Subscriptions] AS S
JOIN [ReportServer].[dbo].[Users] AS O ON [S].[OwnerID] = [O].[UserID]
JOIN [ReportServer].[dbo].[Users] AS M ON S.[ModifiedByID] = [M].[UserID]
JOIN [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
LEFT JOIN [AgentJobsCTE] AJ ON CAST(S.[SubscriptionID] AS SYSNAME) = AJ.[SubscriptionID]
WHERE [LastRunTime] >= '04/02/2012 15:00' --NOT NEEDED, BUT MAKES YOUR LIST SHORTER
AND [LastStatus] NOT LIKE 'Mail sent to%'
AND [LastStatus] NOT LIKE 'The file "%'
--AND [LastStatus] NOT LIKE 'Pending%' --Pending are currently running.
ORDER BY [LastRunTime] DESC
I noticed that I was seeing a number of subscriptions currently running for the same report, many run at the same time. I wrote a query to tell me which subscriptions had the same "report parameter values". This doesn't look at the subscription start time or days of the week, just the parameters...so you'll need to do some investigation (the next query will help with that)
USE [ReportServer]
DECLARE @ServerURL VARCHAR(MAX)
SELECT TOP 1 @ServerURL =[MachineName]
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL
IF CHARINDEX('\',@ServerURL) > 0
SET @ServerURL = LEFT(@ServerURL,CHARINDEX('\',@ServerURL)-1)
SET @ServerURL = 'http://' + @ServerURL + '/Reports/Pages'
;WITH DuplicateSubscriptions AS (
SELECT C.Name AS ReportName
,CAST(Parameters AS VARCHAR(MAX)) AS Parameters
,[Report_OID]
,C.Path
,COUNT(1) AS [Count]
FROM [ReportServer].[dbo].[Subscriptions] S
JOIN [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
LEFT JOIN [dbo].[ReportSchedule] AS RS ON [S].[SubscriptionID] = [RS].[SubscriptionID]
LEFT JOIN [dbo].[Schedule] AS SC ON RS.[ScheduleID] = SC.[ScheduleID]
WHERE (SC.EndDate IS NULL OR (SC.EndDate IS NULL AND SC.[RecurrenceType] <> 1))
GROUP BY CAST(Parameters AS VARCHAR(MAX)),[Report_OID],C.Name,C.Path
HAVING COUNT(1) > 1
)
SELECT *
,@ServerURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS URL
FROM [DuplicateSubscriptions]
--ORDER BY [ReportName], [Count] DESC
ORDER BY [Count] DESC, [ReportName]
So when you find a possible duplicate subscription in the previous list, get the Report_OID and Parameters field and past them into the variables in this query and execute to see if it needs more investigation. I usually look at the "LastRun" field and see if there are any duplicates in that list. If you need to dig in more, the query provides links to both the subscription webpage and the report management page listing all subscriptions
USE [ReportServer]
-- VIEW ALL (NON EXPIRED) SUBSCRIPTIONS FOR A SPECIFIC REPORT WITH SPECIFIC PARAMETERS
DECLARE @ServerSubscriptionURL VARCHAR(MAX)
SELECT TOP 1 @ServerSubscriptionURL =[MachineName]
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL
IF CHARINDEX('\',@ServerSubscriptionURL) > 0
SET @ServerSubscriptionURL = LEFT(@ServerSubscriptionURL,CHARINDEX('\',@ServerSubscriptionURL)-1)
SET @ServerSubscriptionURL = 'http://' + @ServerSubscriptionURL + '/Reports/Pages'
DECLARE @ReportID sysname = 'F9999999-888G-7H77-I9I9-000000J00000' -- ReportID from previous query
,@ParameterList VARCHAR(MAX) = '[parameters]'-- Parameters from previous query
SELECT C.Name AS ReportName
,S.[SubscriptionID]
,S.[Description] Descr
,S.[LastRunTime] LastRun
,S.[LastStatus]
--,SC.[EndDate]
--,SC.[RecurrenceType]
,U.[UserName] AS Owner
,@ServerSubscriptionURL + '/SubscriptionProperties.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(MAX)) AS SubscriptionURL
,@ServerSubscriptionURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM [ReportServer].[dbo].[Subscriptions] S
JOIN [ReportServer].[dbo].[Users] AS U ON [S].[OwnerID] = [U].[UserID]
JOIN [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
LEFT JOIN [dbo].[ReportSchedule] AS RS ON [S].[SubscriptionID] = [RS].[SubscriptionID]
LEFT JOIN [dbo].[Schedule] AS SC ON RS.[ScheduleID] = SC.[ScheduleID]
WHERE Report_OID = @ReportID
AND CAST(S.Parameters AS VARCHAR(MAX)) = @ParameterList
AND (SC.EndDate IS NULL OR (SC.EndDate IS NULL AND SC.[RecurrenceType] <> 1))
ORDER BY S.Description
But wait, thats not all, some additional interesting queries:
See all currently running reports
SELECT [JobID],[StartDate],[ComputerName],[RequestName],[RequestPath]
,[Description],[Timeout] AS Timeout, DATEDIFF(ss, startdate, GETDATE()) AS SecondsSinceRun
, DATEDIFF(mi, startdate, GETDATE()) AS MinutesSinceRun
,[JobAction],[JobType],[JobStatus]
,'http://[reporturl]/Reports/Pages/Report.aspx?ItemPath=' + REPLACE(REPLACE(RJ.RequestPath,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM [ReportServer].[dbo].[RunningJobs] AS RJ
See the run information for a specific report (comment out the RequestType if you want to see things other than subscriptions)
SELECT *
FROM [ReportServer].[dbo].[ExecutionLog2] AS EL
WHERE [ReportPath] LIKE '%[report name=""]' AND [RequestType] = 'Subscription'
ORDER BY [TimeStart] DESC
View a schedule for a specific Subscription
-- VIEW SCHDULE DATA FOR A SPEICIFC SUBSCRIPTION
SELECT *
FROM [dbo].[Schedule] AS S
JOIN [dbo].[ReportSchedule] AS RS ON [S].[ScheduleID] = [RS].[ScheduleID]
WHERE RS.[SubscriptionID] = '[subscriptionid]'
Ok, one last one, this lets you see all expired (past the end date) and one time subscriptions (useful if you want to clean them up)
-- EXPIRED AND ONE TIME RUN SUBSCRIPTIONS
DECLARE @ServerURL VARCHAR(MAX)
SELECT TOP 1 @ServerURL =[MachineName]
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL
IF CHARINDEX('\',@ServerURL) > 0
SET @ServerURL = LEFT(@ServerURL,CHARINDEX('\',@ServerURL)-1)
SET @ServerURL = 'http://' + @ServerURL + '/Reports/Pages'
;WITH ExpiredSubscriptions AS
(
SELECT RS.[SubscriptionID], S.[EndDate]
FROM [dbo].[Schedule] AS S
JOIN [dbo].[ReportSchedule] AS RS ON [S].[ScheduleID] = [RS].[ScheduleID]
WHERE S.[EndDate] < GETDATE() OR S.[RecurrenceType] = 1
)
SELECT S.[SubscriptionID]
,C.Name AS ReportName
,S.[Description] Descr
,S.[LastRunTime] LastRun
,ES.[EndDate]
,S.[LastStatus]
,U.[UserName] AS Owner
,@ServerURL + '/SubscriptionProperties.aspx?ItemPath=' + REPLACE(REPLACE(Path,'/','%2f'),' ','+') + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(MAX)) AS SubscriptionURL
,@ServerURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM Subscriptions S
JOIN [dbo].[Users] AS U ON [S].[OwnerID] = [U].[UserID]
JOIN [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
JOIN [ExpiredSubscriptions] ES ON S.[SubscriptionID] = ES.[SubscriptionID]
ORDER BY C.[Name], ES.[EndDate] DESC
If you have any other good queries, please drop them in a comment below
No comments:
Post a Comment