July 11, 2013 at 3:38 pm
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 1THEN 'January'
WHEN 2THEN 'February'
WHEN 3THEN 'March'
WHEN 4THEN 'April'
WHEN 5THEN 'May'
WHEN 6THEN 'June'
WHEN 7THEN 'July'
WHEN 8THEN 'August'
WHEN 9THEN '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 1THEN 'Sunday'
WHEN 2THEN 'Monday'
WHEN 3THEN 'Tuesday'
WHEN 4THEN 'Wednesday'
WHEN 5THEN 'Thursday'
WHEN 6THEN 'Friday'
WHEN 7THEN '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
July 26, 2013 at 6:56 am
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. 🙂
July 26, 2013 at 10:15 am
Could you share that query? I'd be curious to see it!
Cheers,
EJM
July 26, 2013 at 11:26 am
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[/url]
Learn Extended Events
August 1, 2013 at 6:52 am
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. 🙂
August 2, 2013 at 2:31 pm
This is great information, can you provide an example of your tally/numbers table?
August 2, 2013 at 2:57 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply