Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Server Reporting Services (SSRS)- find Report Subscriptions by Email or Comments or Name/Description, and view last Status (i.e. was it sent?). See https://natethedba.wordpress.com/tag/tsql2sday/ . Subject to updates and open to feedback!
USE ReportServer;
--Fetch the subscription settings XML
WITH subscriptionXmL AS (
SELECT SubscriptionID, OwnerID, Report_OID, Locale, InactiveFlags
, ExtensionSettings, ExtensionSettingsXML = CONVERT(xml, ExtensionSettings)
, ModifiedByID, ModifiedDate, [Description], LastStatus
, EventType, MatchData, LastRunTime, [Parameters]
, DeliveryExtension, [Version]
FROM ReportServer.dbo.Subscriptions
)
--Use the above to "shred" XML into name-value pairs
, SettingsCTE AS (
SELECT SubscriptionID, ExtensionSettings
-- include other fields if you need them.
, SettingName = ISNULL(Settings.value('(./*:Name/text())[1]', 'nvarchar(1024)'), 'Value')
, SettingValue = Settings.value('(./*:Value/text())[1]', 'nvarchar(max)')
FROM subscriptionXmL
CROSS APPLY subscriptionXmL.ExtensionSettingsXML.nodes('//*:ParameterValue') Queries(SETTINGS)
)
--Now the main query.
/* I lazily use 'DISTINCT' here b/c if I'm searching using a Report name/description and NOT a SettingValue,
we could get as many rows as there are Settings on the report, and that's not always helpful. */
SELECT DISTINCT
SubscriptionName = subs.[Description]
, JobName = sch.ScheduleID
, ReportName = cat.Name, ReportPath = cat.Path
, sch.LastRunStatus, LastRun = sch.LastRunTime
, subs.LastStatus, LastSubRun = subs.LastRunTime
, subs.OwnerID, u.UserName, subs.SubscriptionID
, cat.ItemID [CatalogReportID]
--I don't usually need to see all the settings, but if you do, you could uncomment this.
--, cte.*
FROM SettingsCTE cte
JOIN ReportServer.dbo.Subscriptions subs
ON subs.SubscriptionID = cte.SubscriptionID
INNER JOIN dbo.ReportSchedule rsch
ON rsch.SubscriptionID = subs.SubscriptionID
INNER JOIN dbo.Schedule sch
ON rsch.ScheduleID = sch.ScheduleID
INNER JOIN dbo.[Catalog] cat
ON rsch.ReportID = cat.ItemID
AND subs.Report_OID = cat.ItemID
JOIN dbo.Users u
ON u.UserID = subs.OwnerID
--Little trick to have a bunch of 'OR' conditions that I can comment-in/out at will...
WHERE 1=0
OR cte.SettingName IN ('TO','CC','BCC') AND cte.SettingValue LIKE '%bigwig@corporate.com%'
--OR cat.Name LIKE '%daily%bigwig%report%'
--OR cat.[Description] LIKE '%please%find%attached%'
--OR SettingName = 'Comment' AND cte.SettingValue LIKE '%please%find%attached%'
ORDER BY sch.LastRunTime DESC, subs.LastRunTime DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment