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
You can’t perform that action at this time.