How can I tell when a recipient in a subscription was sent the SSRS report?
I've seen similar questions but they seem to relate to which email addresses are currently subscribed to the report, rather than specific instances of when an email was sent to the recipient(s).
CodePudding user response：
I've used this SQL script to find the execution log of subscriptions.
There are some example reports in my GitHub project.
USE [ReportServer];
GO
DECLARE @StartDate DATETIME = '03-MAR-2022';
DECLARE @EndDate DATETIME = '04-MAR-2022';
DECLARE @ReportFolder NVARCHAR(200) = NULL;
DECLARE @ReportName NVARCHAR(200) = NULL;
DECLARE @LogStatus NVARCHAR(200) = NULL;
DECLARE @StatusGroup NVARCHAR(200) = NULL;
DECLARE @ServiceAccount NVARCHAR(200) = 'your_service_account'; -- enter the subscription service account here
WITH
report_status
AS
(
SELECT tbl.* FROM (VALUES
( 'rrRenderingError', 'Failure')
, ( 'rsHttpRuntimeClientDisconnectionError', 'Failure')
, ( 'rsInternalError', 'Failure')
, ( 'rsInvalidDataSourceCredentialSetting', 'Failure')
, ( 'rsProcessingAborted', 'Failure')
, ( 'rsProcessingError', 'Failure')
, ( 'rsRenderingExtensionNotFound', 'Failure')
, ( 'rsReportServerDatabaseError', 'Failure')
, ( 'rsSuccess', 'Success')
) tbl ([StatusName], [StatusGroup])
)
,
report_users
AS
(
SELECT
[UserID]
, [UserName]
, [SimpleUserName] = LOWER(RIGHT([UserName], (LEN([UserName]) - CHARINDEX('\', [UserName]))))
FROM
[dbo].[Users] WITH(NOLOCK)
)
,
report_catalog
AS
(
SELECT
rpt.[ItemID]
, rpt.[CreatedById]
, rpt.[ModifiedById]
, rpt.[Type]
, rpt.[Name]
, [ReportName] = rpt.[Name]
, rpt.[Description]
, rpt.[Parameter]
, [CreationDate] = CONVERT(DATETIME, CONVERT(VARCHAR(11), rpt.[CreationDate], 13))
, [ModifiedDate] = CONVERT(DATETIME, CONVERT(VARCHAR(11), rpt.[ModifiedDate], 13))
, [ReportFolder] = SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2)
, rpt.[Path]
, [URL_ReportFolder] = 'http://' Host_Name() '/Reports/Pages/Report.aspx?ItemPath=/' SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2) '&ViewMode=List'
, [URL_Report] = 'http://' Host_Name() '/Reports/Pages/Report.aspx?ItemPath=/' SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2) '/' rpt.[Name]
, [ReportDefinition] = CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), rpt.[Content]))
FROM
[dbo].[Catalog] AS rpt WITH (NOLOCK)
WHERE
1=1
AND rpt.[Type] = 2
)
,
subscription_days
AS
(
SELECT tbl.* FROM (VALUES
( 'DaysOfMonth', 1, '1')
, ( 'DaysOfMonth', 2, '2')
, ( 'DaysOfMonth', 4, '3')
, ( 'DaysOfMonth', 8, '4')
, ( 'DaysOfMonth', 16, '5')
, ( 'DaysOfMonth', 32, '6')
, ( 'DaysOfMonth', 64, '7')
, ( 'DaysOfMonth', 128, '8')
, ( 'DaysOfMonth', 256, '9')
, ( 'DaysOfMonth', 512, '10')
, ( 'DaysOfMonth', 1024, '11')
, ( 'DaysOfMonth', 2048, '12')
, ( 'DaysOfMonth', 4096, '13')
, ( 'DaysOfMonth', 8192, '14')
, ( 'DaysOfMonth', 16384, '15')
, ( 'DaysOfMonth', 32768, '16')
, ( 'DaysOfMonth', 65536, '17')
, ( 'DaysOfMonth', 131072, '18')
, ( 'DaysOfMonth', 262144, '19')
, ( 'DaysOfMonth', 524288, '20')
, ( 'DaysOfMonth', 1048576, '21')
, ( 'DaysOfMonth', 2097152, '22')
, ( 'DaysOfMonth', 4194304, '23')
, ( 'DaysOfMonth', 8388608, '24')
, ( 'DaysOfMonth', 16777216, '25')
, ( 'DaysOfMonth', 33554432, '26')
, ( 'DaysOfMonth', 67108864, '27')
, ( 'DaysOfMonth', 134217728, '28')
, ( 'DaysOfMonth', 268435456, '29')
, ( 'DaysOfMonth', 536870912, '30')
, ( 'DaysOfMonth', 1073741824, '31')
, ( 'DaysOfMonth', 8193, '1st and 14th')
, ( 'DaysOfWeek', 1, 'Sun')
, ( 'DaysOfWeek', 2, 'Mon')
, ( 'DaysOfWeek', 4, 'Tues')
, ( 'DaysOfWeek', 8, 'Wed')
, ( 'DaysOfWeek', 16, 'Thurs')
, ( 'DaysOfWeek', 32, 'Fri')
, ( 'DaysOfWeek', 64, 'Sat')
, ( 'DaysOfWeek', 62, 'Mon - Fri')
, ( 'DaysOfWeek', 10, 'Mon - Wed')
, ( 'DaysOfWeek', 24, 'Wed - Thurs')
, ( 'DaysOfWeek', 120, 'Wed - Sat')
, ( 'DaysOfWeek', 126, 'Mon - Sat')
, ( 'DaysOfWeek', 127, 'Daily')
, ( 'DayOfWeek', 1, 'Sun')
, ( 'DayOfWeek', 127, 'Sun')
, ( 'DayOfWeek', 2, 'Mon')
, ( 'DayOfWeek', 10, 'Mon')
, ( 'DayOfWeek', 62, 'Mon')
, ( 'DayOfWeek', 126, 'Mon')
, ( 'DayOfWeek', 127, 'Mon')
, ( 'DayOfWeek', 4, 'Tue')
, ( 'DayOfWeek', 10, 'Tue')
, ( 'DayOfWeek', 62, 'Tue')
, ( 'DayOfWeek', 126, 'Tue')
, ( 'DayOfWeek', 127, 'Tue')
, ( 'DayOfWeek', 8, 'Wed')
, ( 'DayOfWeek', 10, 'Wed')
, ( 'DayOfWeek', 24, 'Wed')
, ( 'DayOfWeek', 62, 'Wed')
, ( 'DayOfWeek', 120, 'Wed')
, ( 'DayOfWeek', 126, 'Wed')
, ( 'DayOfWeek', 127, 'Wed')
, ( 'DayOfWeek', 16, 'Thr')
, ( 'DayOfWeek', 24, 'Thr')
, ( 'DayOfWeek', 62, 'Thr')
, ( 'DayOfWeek', 120, 'Thr')
, ( 'DayOfWeek', 126, 'Thr')
, ( 'DayOfWeek', 127, 'Thr')
, ( 'DayOfWeek', 32, 'Fri')
, ( 'DayOfWeek', 62, 'Fri')
, ( 'DayOfWeek', 120, 'Fri')
, ( 'DayOfWeek', 126, 'Fri')
, ( 'DayOfWeek', 127, 'Fri')
, ( 'DayOfWeek', 64, 'Sat')
, ( 'DayOfWeek', 120, 'Sat')
, ( 'DayOfWeek', 126, 'Sat')
, ( 'DayOfWeek', 127, 'Sat')
) tbl ([GroupName], [CodeNbr], [Label])
)
,
subscription_schedule
AS
(
SELECT
[ScheduleID]
, [SchDaySun] = Sun
, [SchDayMon] = Mon
, [SchDayTue] = Tue
, [SchDayWed] = Wed
, [SchDayThr] = Thr
, [SchDayFri] = Fri
, [SchDaySat] = Sat
, [ScheduleName]
, [ScheduleStartDate]
, [ScheduleEndDate]
, [Flags]
, [RecurrenceType]
, [State]
, [MinutesInterval]
, [DaysInterval]
, [WeeksInterval]
, [DaysOfWeek]
, [DaysOfMonth]
, [Month]
, [MonthlyWeek]
, [ScheduleDays]
FROM
(
SELECT
sc.[ScheduleID]
, sd.[CodeNbr]
, sd.[Label]
, [ScheduleName] = CASE WHEN sc.[EventType] = 'SharedSchedule' THEN sc.[name] ELSE NULL END
, [ScheduleStartDate] = sc.[StartDate]
, [ScheduleEndDate] = sc.[EndDate]
, sc.[Flags]
, sc.[RecurrenceType]
, sc.[State]
, sc.[MinutesInterval]
, sc.[DaysInterval]
, sc.[WeeksInterval]
, sc.[DaysOfWeek]
, sc.[DaysOfMonth]
, sc.[Month]
, sc.[MonthlyWeek]
, [ScheduleDays] =
CASE
WHEN sc.[DaysOfMonth] IS NOT NULL THEN COALESCE(dom.[Label], '(' CAST(sc.[DaysOfMonth] AS VARCHAR(20)) ') NOT CODED')
WHEN sc.[DaysOfWeek] IS NOT NULL THEN COALESCE(dow.[Label], '(' CAST(sc.[DaysOfWeek] AS VARCHAR(20)) ') NOT CODED')
END
FROM
[dbo].[Schedule] sc WITH (NOLOCK)
LEFT JOIN subscription_days sd ON sc.[DaysOfWeek] = sd.[CodeNbr] AND sd.[GroupName] = 'DayOfWeek'
LEFT JOIN subscription_days AS dom ON sc.[DaysOfMonth] = dom.[CodeNbr] AND dom.[GroupName] = 'DaysOfMonth'
LEFT JOIN subscription_days AS dow ON sc.[DaysOfWeek] = dow.[CodeNbr] AND dow.[GroupName] = 'DaysOfWeek'
) sch
PIVOT
(
COUNT(sch.[Label])
FOR sch.[Label]
IN ([Sun], [Mon], [Tue], [Wed], [Thr], [Fri], [Sat])
) AS pvt
)
,
report_subscription
AS
(
SELECT
sub.[SubscriptionID]
, sub.[Report_OID]
, [SubscriptionDescription] = sub.[Description]
, sub.[ExtensionSettings]
, sub.[EventType]
, sub.[OwnerID]
, sub.[ModifiedByID]
, sub.[ModifiedDate]
, [RunTime] = CONVERT(VARCHAR(5), sub.[LastRunTime], 8)
, [LastRunDate] = CONVERT(VARCHAR(11), sub.[LastRunTime], 13)
, [LastRunTime] = CAST(CONVERT(CHAR(16), sub.[LastRunTime], 113) AS DATETIME)
, sub.[DeliveryExtension]
, sub.[MatchData]
, [SubscriptionLastStatus] = sub.[LastStatus]
, [StatusFail] = CASE WHEN sub.[LastStatus] LIKE '%Mail sent%' THEN 'N' ELSE 'Y' END
, [EmailSubject] = CASE CHARINDEX('<Name>SUBJECT</Name><Value>', sub.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(sub.ExtensionSettings, LEN('<Name>SUBJECT</Name><Value>') CHARINDEX('<Name>SUBJECT</Name><Value>', sub.ExtensionSettings), CHARINDEX('</Value>', sub.ExtensionSettings, CHARINDEX('<Name>SUBJECT</Name><Value>', sub.ExtensionSettings) 1) - (LEN('<Name>SUBJECT</Name><Value>') CHARINDEX('<Name>SUBJECT</Name><Value>', sub.ExtensionSettings))) END
, [EmailTo] = SUBSTRING(sub.ExtensionSettings, LEN('<Name>TO</Name><Value>') CHARINDEX('<Name>TO</Name><Value>', sub.ExtensionSettings), CHARINDEX('</Value>', sub.ExtensionSettings, CHARINDEX('<Name>TO</Name><Value>', sub.ExtensionSettings) 1) - (LEN('<Name>TO</Name><Value>') CHARINDEX('<Name>TO</Name><Value>', sub.ExtensionSettings)))
, [EmailCc] = CASE CHARINDEX('<Name>CC</Name><Value>', sub.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(sub.ExtensionSettings, LEN('<Name>CC</Name><Value>') CHARINDEX('<Name>CC</Name><Value>', sub.ExtensionSettings), CHARINDEX('</Value>', sub.ExtensionSettings, CHARINDEX('<Name>CC</Name><Value>', sub.ExtensionSettings) 1) - (LEN('<Name>CC</Name><Value>') CHARINDEX('<Name>CC</Name><Value>', sub.ExtensionSettings))) END
, [EmailBcc] = CASE CHARINDEX('<Name>BCC</Name><Value>', sub.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(sub.ExtensionSettings, LEN('<Name>BCC</Name><Value>') CHARINDEX('<Name>BCC</Name><Value>', sub.ExtensionSettings), CHARINDEX('</Value>', sub.ExtensionSettings, CHARINDEX('<Name>BCC</Name><Value>', sub.ExtensionSettings) 1) - (LEN('<Name>BCC</Name><Value>') CHARINDEX('<Name>BCC</Name><Value>', sub.ExtensionSettings))) END
, [EmailComment] = CASE CHARINDEX('<Name>Comment</Name><Value>', sub.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(sub.ExtensionSettings, LEN('<Name>Comment</Name><Value>') CHARINDEX('<Name>Comment</Name><Value>', sub.ExtensionSettings), CHARINDEX('</Value>', sub.ExtensionSettings, CHARINDEX('<Name>Comment</Name><Value>', sub.ExtensionSettings) 1) - (LEN('<Name>Comment</Name><Value>') CHARINDEX('<Name>Comment</Name><Value>', sub.ExtensionSettings))) END
, [EmailIncludeLink] = CASE CHARINDEX('<Name>IncludeLink</Name><Value>', sub.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(sub.ExtensionSettings, LEN('<Name>IncludeLink</Name><Value>') CHARINDEX('<Name>IncludeLink</Name><Value>', sub.ExtensionSettings), CHARINDEX('</Value>', sub.ExtensionSettings, CHARINDEX('<Name>IncludeLink</Name><Value>', sub.ExtensionSettings) 1) - (LEN('<Name>IncludeLink</Name><Value>') CHARINDEX('<Name>IncludeLink</Name><Value>', sub.ExtensionSettings))) END
, [EmailRenderFormat] = CASE CHARINDEX('<Name>RenderFormat</Name><Value>', sub.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(sub.ExtensionSettings, LEN('<Name>RenderFormat</Name><Value>') CHARINDEX('<Name>RenderFormat</Name><Value>', sub.ExtensionSettings), CHARINDEX('</Value>', sub.ExtensionSettings, CHARINDEX('<Name>RenderFormat</Name><Value>', sub.ExtensionSettings) 1) - (LEN('<Name>RenderFormat</Name><Value>') CHARINDEX('<Name>RenderFormat</Name><Value>', sub.ExtensionSettings))) END
, [EmailPriority] = CASE CHARINDEX('<Name>Priority</Name><Value>', sub.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(sub.ExtensionSettings, LEN('<Name>Priority</Name><Value>') CHARINDEX('<Name>Priority</Name><Value>', sub.ExtensionSettings), CHARINDEX('</Value>', sub.ExtensionSettings, CHARINDEX('<Name>Priority</Name><Value>', sub.ExtensionSettings) 1) - (LEN('<Name>Priority</Name><Value>') CHARINDEX('<Name>Priority</Name><Value>', sub.ExtensionSettings))) END
, sch.[MinutesInterval]
, sch.[DaysInterval]
, sch.[WeeksInterval]
, sch.[DaysOfWeek]
, sch.[DaysOfMonth]
, sch.[Month]
, sch.[MonthlyWeek]
, sch.[ScheduleName]
, sch.[ScheduleDays]
, sch.[SchDaySun]
, sch.[SchDayMon]
, sch.[SchDayTue]
, sch.[SchDayWed]
, sch.[SchDayThr]
, sch.[SchDayFri]
, sch.[SchDaySat]
, sch.[ScheduleStartDate]
, sch.[ScheduleEndDate]
, sch.[Flags]
, sch.[RecurrenceType]
, sch.[State]
, sub.[LastStatus]
, rs.[ScheduleID]
FROM
[dbo].[Subscriptions] AS sub WITH (NOLOCK)
LEFT JOIN [dbo].[Notifications] AS n WITH (NOLOCK) ON n.[SubscriptionID] = sub.[SubscriptionID] AND sub.[Report_OID] = n.[ReportID]
LEFT JOIN [dbo].[ReportSchedule] AS rs WITH (NOLOCK) ON sub.[SubscriptionID] = rs.[SubscriptionID]
LEFT JOIN subscription_schedule AS sch ON rs.[ScheduleID] = sch.[ScheduleID]
)
SELECT
rpt.[Path]
, rpt.[ReportFolder]
, rpt.[Name]
, rpt.[URL_ReportFolder]
, rpt.[URL_Report]
, [URL_Report_Filtered] = rpt.URL_Report '&rs:Command=Render&' CONVERT(VARCHAR(2000), el.[Parameters])
, [UserName] = usr.[SimpleUserName]
, el.[Status]
, el.[TimeStart]
, el.[RowCount]
, el.[ByteCount]
, el.[Format]
, [Parameters] =
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(CONVERT(NVARCHAR(MAX), el.[Parameters])
, ' ', ' ')
, '/', '-')
, '<', '<')
, '>', '>')
, ':', ':')
, [TotalSeconds] = CONVERT(CHAR(8),DATEADD(ms, (el.[TimeDataRetrieval] el.[TimeProcessing] el.[TimeRendering]), 0), 108)
, [TimeDataRetrieval] = CONVERT(CHAR(8),DATEADD(ms, el.[TimeDataRetrieval], 0), 108)
, [TimeProcessing] = CONVERT(CHAR(8),DATEADD(ms, el.[TimeProcessing], 0), 108)
, [TimeRendering] = CONVERT(CHAR(8),DATEADD(ms, el.[TimeRendering], 0), 108)
, [SubscriptionOwner] = usc.[SimpleUserName]
, [SubscriptionModifiedBy] = usm.[SimpleUserName]
, [SubscriptionModifiedDate] = sub.[ModifiedDate]
, sub.[SubscriptionID]
, sub.[SubscriptionDescription]
, sub.[ExtensionSettings]
, sub.[EventType]
, sub.[EmailSubject]
, sub.[EmailTo]
, sub.[EmailCc]
, sub.[EmailBcc]
, sub.[EmailComment]
, sub.[EmailIncludeLink]
, sub.[EmailRenderFormat]
, sub.[EmailPriority]
, sub.[DeliveryExtension]
, [DeliveryExtensionImage] =
CASE
WHEN sub.[DeliveryExtension] = 'Report Server Email' THEN 'email'
WHEN sub.[DeliveryExtension] = 'Report Server FileShare' THEN 'folder'
ELSE sub.[DeliveryExtension]
END
, sub.[SubscriptionLastStatus]
, sub.[StatusFail]
, sub.[MatchData]
, sub.[RunTime]
, sub.[LastRunDate]
, sub.[LastRunTime]
, sub.[MinutesInterval]
, sub.[DaysInterval]
, sub.[WeeksInterval]
, sub.[DaysOfWeek]
, sub.[DaysOfMonth]
, sub.[Month]
, sub.[MonthlyWeek]
, sub.[ScheduleName]
, sub.[ScheduleDays]
, sub.[SchDaySun]
, sub.[SchDayMon]
, sub.[SchDayTue]
, sub.[SchDayWed]
, sub.[SchDayThr]
, sub.[SchDayFri]
, sub.[SchDaySat]
, sub.[ScheduleStartDate]
, sub.[ScheduleEndDate]
, sub.[Flags]
, sub.[RecurrenceType]
, sub.[State]
, [EventStatus] = el.[Status]
, [EventDateTime] = el.[TimeEnd]
, sub.[LastStatus]
, sub.[ScheduleID]
FROM
report_catalog AS rpt
INNER JOIN report_subscription AS sub ON sub.[Report_OID] = rpt.[ItemID]
LEFT JOIN [dbo].[ExecutionLog] AS el WITH (NOLOCK) ON el.[ReportID] = rpt.[ItemID]
LEFT JOIN report_status AS rs ON el.[Status] = rs.[StatusName]
LEFT JOIN report_users AS usr ON el.[UserName] = usr.[UserName]
LEFT JOIN report_users AS usc ON sub.[OwnerID] = usc.[UserID]
LEFT JOIN report_users AS usm ON sub.[ModifiedByID] = usm.[UserID]
WHERE
1=1
AND (@StartDate IS NULL OR el.[TimeStart] >= @StartDate)
AND (@EndDate IS NULL OR el.[TimeStart] <= @EndDate)
AND (@ReportFolder IS NULL OR rpt.[ReportFolder] = @ReportFolder)
AND (@ReportName IS NULL OR rpt.[ReportName] = @ReportName)
AND (@LogStatus IS NULL OR el.[Status] = @LogStatus)
AND (@StatusGroup IS NULL OR rs.[StatusGroup] = @StatusGroup)
AND (@ServiceAccount IS NULL OR usr.[SimpleUserName] = @ServiceAccount)
ORDER BY
rpt.[Path]
, el.[TimeStart] DESC