Manage subscription

• tonzonline

  Mr or Mrs. 500

  Points: 597

  More actions

  January 26, 2016 at 1:06 am

  #304060

  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

• dclemens

  SSC-Addicted

  Points: 414

  More actions

  January 26, 2016 at 1:07 pm

  #1854541

  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