January 26, 2016 at 1:06 am
Hi all,
Does anyone know whether there is a tool to list out report Subscription ?
Currently, in Manage - Subscription, they only show Description, Trigger, LastRun, and Status, which only useful to know where the it was ran or not.
Problem is when we have a bunch of subscription, with different "Email To", even in each subscription there are parameters which basically unique each other.
It is quite tedious to check one by one the Edit button to see the setup inside, whereas if only we can see at least the "Email To" column in the Subscription list. Show 1 or 2 parameter will very very much help.
Kindly advice. Thanks.
Tonz
January 26, 2016 at 1:07 pm
Here is a query I have in my toolbox that should put you on the right track.
USE ReportServer
GO
;
WITH 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 = 1
THEN 'Sun'
WHEN SC.DaysOfWeek = 2
THEN 'Mon'
WHEN SC.DaysOfWeek = 4
THEN 'Tue'
WHEN SC.DaysOfWeek = 8
THEN 'Wed'
WHEN SC.DaysOfWeek = 16
THEN 'Thu'
WHEN SC.DaysOfWeek = 32
THEN 'Fri'
WHEN SC.DaysOfWeek = 64
THEN 'Sat'
WHEN SC.DaysOfWeek = 62
THEN 'Mon-Fri'
WHEN SC.DaysOfWeek = 10
THEN 'Mon AND Wed'
WHEN SC.DaysOfWeek = 127
THEN 'All Days'
END AS DaysOfWeek
,CASE
WHEN SC.DaysOfMonth = 1
THEN '1'
WHEN SC.DaysOfMonth = 2
THEN '2'
WHEN SC.DaysOfMonth = 4
THEN '3'
WHEN SC.DaysOfMonth = 8
THEN '4'
WHEN SC.DaysOfMonth = 16
THEN '5'
WHEN SC.DaysOfMonth = 32
THEN '6'
WHEN SC.DaysOfMonth = 64
THEN '7'
WHEN SC.DaysOfMonth = 128
THEN '8'
WHEN SC.DaysOfMonth = 256
THEN '9'
WHEN SC.DaysOfMonth = 512
THEN '10'
WHEN SC.DaysOfMonth = 1024
THEN '11'
WHEN SC.DaysOfMonth = 2048
THEN '12'
WHEN SC.DaysOfMonth = 4096
THEN '13'
WHEN SC.DaysOfMonth = 8192
THEN '14'
WHEN SC.DaysOfMonth = 16384
THEN '15'
WHEN SC.DaysOfMonth = 32768
THEN '16'
WHEN SC.DaysOfMonth = 65536
THEN '17'
WHEN SC.DaysOfMonth = 131072
THEN '18'
WHEN SC.DaysOfMonth = 262144
THEN '19'
WHEN SC.DaysOfMonth = 524288
THEN '20'
WHEN SC.DaysOfMonth = 1048576
THEN '21'
WHEN SC.DaysOfMonth = 2097152
THEN '22'
WHEN SC.DaysOfMonth = 4194304
THEN '23'
WHEN SC.DaysOfMonth = 8388608
THEN '24'
WHEN SC.DaysOfMonth = 16777216
THEN '25'
WHEN SC.DaysOfMonth = 33554432
THEN '26'
WHEN SC.DaysOfMonth = 67108864
THEN '27'
WHEN SC.DaysOfMonth = 134217728
THEN '28'
WHEN SC.DaysOfMonth = 268435456
THEN '29'
WHEN SC.DaysOfMonth = 536870912
THEN '30'
WHEN SC.DaysOfMonth = 1073741824
THEN '31'
WHEN SC.DaysOfMonth = 8193
THEN '1st and 14th day'
END AS DaysOfMonth
,CASE
WHEN SC.Month = 4095
THEN 'Every Month'
WHEN SC.Month = 585
THEN 'Jan,April,July,October (Quarterly)'
WHEN SC.Month = 1
THEN 'Jan'
WHEN SC.Month = 2
THEN 'Feb'
WHEN SC.Month = 4
THEN 'Mar'
WHEN SC.Month = 8
THEN 'Apr'
WHEN SC.Month = 16
THEN 'May'
WHEN SC.Month = 32
THEN 'Jun'
WHEN SC.Month = 64
THEN 'Jul'
WHEN SC.Month = 128
THEN 'Aug'
WHEN SC.Month = 256
THEN 'Sep'
WHEN SC.Month = 512
THEN 'Oct'
WHEN SC.Month = 1024
THEN 'Nov'
WHEN SC.Month = 2048
THEN 'Dec'
END AS Month
,SC.MonthlyWeek
,SC.STATE
,SC.EventType
--, S.MatchData
-- , CL.ItemID AS [ParentID]
-- this is the OLD form for SSRS 2005
-- , [URLDirect] = 'http://reports.tcsedsystem.edu/reports/pages/subscriptionproperties.aspx?itempath=' + C.[Path] + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(80)) -- URL direct to the subscription
-- , [URLSubTab] = 'http://reports.tcsedsystem.edu/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
)
--where LinkedParentName like '%active driver%'
SELECT DMU.*
-- new URL link form for SSRS 2008R2
,[URLDirect] = CASE
WHEN DMU.DataDriven = 'False'
THEN 'http://reports/reports/pages/subscriptionproperties.aspx?itempath=' + DMU.[ReportPath] + '&IsDataDriven=' + DMU.DataDriven + '&SubscriptionID=' + CAST(DMU.[SubscriptionID] AS VARCHAR(80)) -- URL direct to the subscription
ELSE 'http://reports/reports/pages/subscriptionproperties.aspx?itempath=' + DMU.[ReportPath] + '&IsDataDriven=' + DMU.DataDriven + '&SubscriptionID=' + CAST(DMU.[SubscriptionID] AS VARCHAR(80)) + '&RedirectUrl=' + 'http://reports/Reports/Pages/Report.aspx?ItemPath=' + DMU.[ReportPath] + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' -- URL direct to the subscription
END
,[URLSubTab] = 'http://reports/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
WHERE 1 = 1
ORDER BY LinkedParentName
,ReportName
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply