SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Subscriptions Monitoring query


Subscriptions Monitoring query

Author
Message
eric.muller
eric.muller
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 1343
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



Tee Time
Tee Time
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 Visits: 465
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. :-)
eric.muller
eric.muller
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 1343
Could you share that query? I'd be curious to see it!

Cheers,
EJM
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32006 Visits: 18551
Thanks for the query



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Tee Time
Tee Time
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 Visits: 465
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. :-)
BI USER
BI USER
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 271
This is great information, can you provide an example of your tally/numbers table?
eric.muller
eric.muller
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 1343
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search