Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Subscriptions Monitoring query Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 3:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:26 PM
Points: 278, Visits: 806
Y'all -

This came up in another thread, but I thought I would share this query that I came up with to monitor SSRS subscriptions. I think it's a little more robust than lots of other ones I've seen, maybe some of you will find some use from it.

I took the results of this and then made it into a Report that I send to myself every morning showing the results of the Subscription runs, but you could make one that sends on failures or some other event.

Also, suggestions are welcome if I'm doing something a wonky way or you know of something better.

Cheers,
EJM


DECLARE @ServerName varchar(50)
SET @ServerName = 'someservername'

;
WITH CTE_MonthList
AS (
SELECT sc.scheduleid
, Numbers.N-1 AS Num
, sc.Month & POWER(2, Numbers.N-1) AS bits
FROM Reportserver.dbo.Schedule AS sc
CROSS JOIN ztools.dbo.Tally AS Numbers -- or whatever your Tally/numbers table is called
WHERE Numbers.N between 1 AND 13 -- or however many bits you need
--AND sc.Month IS NOT NULL --which means it's not used
)

,
CTE_DaysList
AS (
SELECT sc.scheduleid
, Numbers.N-1 AS Num
, sc.DaysOfMonth & POWER(cast(2 AS BIGINT), Numbers.N-1) AS bits
FROM Reportserver.dbo.Schedule AS sc
CROSS JOIN ztools.dbo.Tally AS Numbers -- or whatever your Tally/numbers table is called
WHERE Numbers.N between 1 AND 32 -- or however many bits you need
-- AND sc.DaysOfMonth IS NOT NULL --which means it's not used
)

,
CTE_WeekdayList
AS (
SELECT sc.scheduleid
, Numbers.N-1 AS Num
, sc.DaysOfWeek & POWER(2, Numbers.N-1) AS bits
FROM Reportserver.dbo.Schedule AS sc
CROSS JOIN ztools.dbo.Tally AS Numbers -- or whatever your Tally/numbers table is called
WHERE Numbers.N between 1 AND 8 -- or however many bits you need
-- AND sc.DaysOfMonth IS NOT NULL --which means it's not used
)
,

CTE_ReadableCalendarEntities
AS (
SELECT d1.scheduleid
, DaysOfMonth = substring((
SELECT (', ' + cast(num + 1 AS VARCHAR(2)))
FROM CTE_DaysList AS d2
WHERE d1.scheduleid = d2.scheduleid
AND bits <> 0
ORDER BY ScheduleID
, num
FOR XML PATH('')
), 3, 1000)

, Months = substring(( --create CSV list for the various months
SELECT (', ' + CASE NUM + 1
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END
)

FROM CTE_MonthList AS m2
WHERE m1.scheduleid = m2.scheduleid
AND bits <> 0
ORDER BY ScheduleID
, num
FOR XML PATH('')
), 3, 1000)

, DaysOfWeek = substring(( --create CSV list for the weekdays
SELECT (', ' + CASE NUM + 1
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END
)

FROM CTE_WeekdayList AS w2
WHERE w1.scheduleid = w2.scheduleid
AND bits <> 0
ORDER BY ScheduleID
, num
FOR XML PATH('')
), 3, 1000)

FROM CTE_DaysList AS d1
JOIN CTE_MonthList as m1
ON d1.ScheduleID=m1.ScheduleID
JOIN CTE_WeekdayList as w1
ON d1.ScheduleID = w1.ScheduleID

GROUP BY d1.ScheduleID, m1.ScheduleID, w1.ScheduleID
)


, ReportEmailsInfo AS
(
SELECT
S.[SubscriptionID], CONVERT(XML, N'<Root>' + CONVERT(NVARCHAR(MAX), S.[ExtensionSettings]) + N'</Root>') AS X
FROM
ReportServer.dbo.[Subscriptions] AS S WITH (NOLOCK)
--WHERE
-- S.[DeliveryExtension] = 'Report Server Email' --only get e-mail subscriptions, not other delivery types
)


, CTE_DataMinusURLs AS
(
SELECT
REI.SubscriptionID
, S.Description AS SubscriptionName
, CASE WHEN S.DataSettings IS NULL
THEN 'False'
ELSE 'True' END AS DataDriven
, S.DataSettings AS DataDrivenReportSettings
, RS.ReportID
, C.Name AS [ReportName]
, S.LastStatus
--, C.SubType
, CL.NAME AS LinkedParentName
, S.DeliveryExtension
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="Subject"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_Subject'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="TO"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_TO'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="CC"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_CC'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="BCC"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_BCC'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="ReplyTo"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_ReplyTo'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="IncludeReport"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_IncludeReport'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="IncludeLink"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_IncludeLink'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="Priority"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_Priority'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="Comment"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_Comment'
, C.Path AS [ReportPath]
, U1.UserName AS [ReportOwner]
, U3.UserName AS [SubCreatedBy]
, U2.UserName AS [SubModifiedBy]

--, S.LastRunTime

-- , SC.ScheduleID
, case when sc.eventtype <>'SharedSchedule' then 'Report-specific' else sc.Name end as Schedule_Name
, SC.StartDate as Schedule_StartDate
, SC.NextRunTime as Schedule_NextRunTime
, SC.LastRunTime AS Schedule_LastRunTime
--, SC.LastRunStatus AS Schedule_LastRunStatus
, SC.EndDate as Schedule_EndDate
, CASE
when SC.recurrencetype = 1 then 'One-off'
when SC.recurrencetype = 2 then 'Hourly'
when SC.recurrencetype = 4 then 'Daily'
when SC.recurrencetype = 5 then 'Monthly'
when SC.recurrencetype = 6 then 'Month-Week' END AS Schedule_RecurrenceType

, SC.MinutesInterval
, SC.DaysInterval
, SC.WeeksInterval

, CASE
WHEN SC.DaysOfWeek = 127 THEN 'Every Weekday'
WHEN sc.DaysOfWeek IS NULL THEN NULL
ELSE RCE.DaysOfWeek
END AS DaysOfWeek

, CASE
WHEN SC.DaysOfMonth = POWER( CAST(2 as BIGINT), 28) - 1
THEN '1-28'
WHEN SC.DaysOfMonth = POWER( CAST(2 as BIGINT), 29) - 1
THEN '1-29'
WHEN SC.DaysOfMonth = POWER( CAST(2 AS BIGINT), 30) - 1
THEN '1-30'
WHEN SC.DaysOfMonth = POWER( CAST(2 AS BIGINT), 31) - 1
THEN '1-31'
WHEN SC.DaysOfMonth IS NULL THEN NULL
ELSE RCE.DaysOfMonth
END AS DaysOfMonth

--, SC.DaysOfMonth as RawDays
, CASE
WHEN SC.Month = 4095
THEN 'Every Month'
WHEN SC.Month IS NULL
THEN NULL
ELSE RCE.Months
END AS Month
--, SC.Month as rawMonth
, SC.MonthlyWeek
, SC.State
, SC.EventType
-- , S.MatchData
-- , CL.ItemID AS [ParentID]


-- this is the OLD form for SSRS 2005
-- , [URLDirect] = 'http://reports.somewebsitename.com/reports/pages/subscriptionproperties.aspx?itempath=' + C.[Path] + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(80)) -- URL direct to the subscription
-- , [URLSubTab] = 'http://reports.somewebsitename.com/Reports/Pages/Report.aspx?ItemPath=' + C.[Path] + '&SelectedTabId=SubscriptionsTab' -- URL to the "Subscriptions" tab on the report (which can be used to delete the subscription)
FROM
ReportEmailsInfo AS ReI
JOIN ReportServer.dbo.[Subscriptions] AS S ON REI.SubscriptionID=S.SubscriptionID --actual subscriptions
JOIN ReportServer.dbo.[Catalog] AS C ON S.[Report_OID] = C.[ItemID]
LEFT OUTER JOIN ReportServer.dbo.[ReportSchedule] AS RS ON S.[SubscriptionID] = RS.[SubscriptionID] --Subscription Schedules
LEFT OUTER JOIN ReportServer.dbo.[Schedule] AS SC ON RS.[ScheduleID] = SC.[ScheduleID] --Schedules
LEFT OUTER JOIN ReportServer.dbo.Catalog AS CL ON C.LinkSourceID = CL.ItemID
JOIN ReportServer.dbo.[Users] AS [U1] ON S.[OwnerID] = [U1].[UserID] -- Subscription Owner
JOIN ReportServer.dbo.[Users] AS [U2] ON S.[ModifiedByID] = [U2].[UserID] -- Subscription ModifiedBy
JOIN ReportServer.dbo.[Users] AS [U3] ON SC.[CreatedByID] = [U3].[UserID] -- Subscription CreatedBy
LEFT OUTER JOIN CTE_ReadableCalendarEntities AS RCE ON SC.ScheduleID = RCE.ScheduleID

--LEFT OUTER JOIN ReportServer.dbo.[ReportSchedule] AS RS2 ON RS2.ReportID = C.LinkSourceID AND C.LinkSourceID IS NOT NULL
)

SELECT DMU.*
-- new URL link form for SSRS 2008R2
, [URLDirect] =
CASE WHEN DMU.DataDriven = 'False'
THEN
'http://' + @ServerName + '/reports/pages/subscriptionproperties.aspx?itempath=' + DMU.[ReportPath] + '&IsDataDriven='+ DMU.DataDriven + '&SubscriptionID=' + CAST(DMU.[SubscriptionID] AS VARCHAR(80)) -- URL direct to the subscription
ELSE
'http://' + @ServerName + '/reports/pages/subscriptionproperties.aspx?itempath=' + DMU.[ReportPath] + '&IsDataDriven='+ DMU.DataDriven + '&SubscriptionID=' + CAST(DMU.[SubscriptionID] AS VARCHAR(80)) +
'&RedirectUrl=' + 'http://' + @ServerName + '/Reports/Pages/Report.aspx?ItemPath=' + DMU.[ReportPath] + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' -- URL direct to the subscription
END
, [URLSubTab] = 'http://' + @ServerName + '/Reports/Pages/Report.aspx?ItemPath=' + DMU.[ReportPath] + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' -- URL to the "Subscriptions" tab on the report (which can be used to delete the subscription)


FROM CTE_DataMinusURLs AS DMU

ORDER BY LinkedParentName, ReportName


Post #1472804
Posted Friday, July 26, 2013 6:56 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:50 PM
Points: 532, Visits: 448
Thanks for sharing! I do something similar but send a failure report instead. I then have a query that grabs the failed jobs, which I then copy and paste to another query window and execute against msdb.
Post #1477980
Posted Friday, July 26, 2013 10:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:26 PM
Points: 278, Visits: 806
Could you share that query? I'd be curious to see it!

Cheers,
EJM
Post #1478095
Posted Friday, July 26, 2013 11:26 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
Thanks for the query



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1478121
Posted Thursday, August 1, 2013 6:52 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:50 PM
Points: 532, Visits: 448
eric.muller (7/26/2013)
Could you share that query? I'd be curious to see it!

Cheers,
EJM


Sure, here it is:

select 'exec sp_start_job @job_name = ''' + cast(j.name as varchar(40)) + ''''

from msdb.dbo.sysjobs j
join msdb.dbo.sysjobsteps js on js.job_id = j.job_id
join [ReportServer].[dbo].[Subscriptions] s on js.command like '%' + cast(s.subscriptionid as varchar(40)) + '%'
where s.LastStatus like 'Failure sending%'AND (s.LastRunTime >= GETDATE() - 3)
or s.LastStatus like 'Failure writing%'AND (s.LastRunTime >= GETDATE() - 3);

If you want to look further back, just change the -3.
Post #1479919
Posted Friday, August 2, 2013 2:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 1:06 PM
Points: 1, Visits: 270
This is great information, can you provide an example of your tally/numbers table?
Post #1480607
Posted Friday, August 2, 2013 2:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:26 PM
Points: 278, Visits: 806
A great discussion of Numbers tables by Jeff Moden here: http://stackoverflow.com/a/2663232

This page has sample code to create one and some examples of its use: http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable#CreateTable
Post #1480620
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse