Report to extract failed subscriptions

You can query the SSRS catalog to get the information you need.

Here is a good article with queries, etc. - https://www.sqlshack.com/ssrs-failed-subscription-alerting/

And below is the code from the same source. 

USE ReportServer
GO
 
DECLARE @count INT
 
SELECT
   	Cat.[Name],
   	Rep.[ScheduleId],
   	Own.UserName,
   	ISNULL(REPLACE(Sub.[Description],'send e-mail to ',''),' ') AS Recipients,
   	Sub.[LastStatus],
   	Cat.[Path],
   	Sub.[LastRunTime]
INTO
   	#tFailedSubs
FROM
   	dbo.[Subscriptions] Sub with (NOLOCK)
INNER JOIN
   	dbo.[Catalog] Cat with (NOLOCK) on Sub.[Report_OID] = Cat.[ItemID]
INNER JOIN
   	dbo.[ReportSchedule] Rep with (NOLOCK) ON (cat.[ItemID] = Rep.[ReportID] and Sub.[SubscriptionID] =Rep.[SubscriptionID])
INNER JOIN
   	dbo.[Users] Own with (NOLOCK) on Sub.[OwnerID] = Own.[UserID]
WHERE
Sub.[LastStatus] NOT LIKE '%was written%' --File Share subscription
AND Sub.[LastStatus] NOT LIKE '%pending%' --Subscription in progress. No result yet
AND Sub.[LastStatus] NOT LIKE '%mail sent%' --Mail sent successfully.
AND Sub.[LastStatus] NOT LIKE '%New Subscription%' --New Sub. Not been executed yet
AND Sub.[LastStatus] NOT LIKE '%been saved%' --File Share subscription
AND Sub.[LastStatus] NOT LIKE '% 0 errors.' --Data Driven subscription
AND Sub.[LastStatus] NOT LIKE '%succeeded%' --Success! Used in cache refreshes
AND Sub.[LastStatus] NOT LIKE '%successfully saved%' --File Share subscription
AND Sub.[LastStatus] NOT LIKE '%New Cache%' --New cache refresh plan
-- AND Sub.[LastRunTime] > GETDATE()-1
 
-- If any failed subscriptions found, proceed to build HTML & send mail.
SELECT @count = COUNT(*) FROM #tFailedSubs
 
IF (@count>0)
 
   	BEGIN
 
   	DECLARE @EmailRecipient NVARCHAR(1000)
   	DECLARE @SubjectText NVARCHAR(1000)
   	DECLARE @ProfileName NVARCHAR(1000)
   	DECLARE @tableHTML1 NVARCHAR(MAX)
   	DECLARE @tableHTMLAll NVARCHAR(MAX)
 
   	SET NOCOUNT ON
   	
   	SELECT @EmailRecipient = '[email protected]'
   	SET @SubjectText = 'Failed SSRS Subscriptions'
 
   	--Set DB Mail profile to use
   	SELECT TOP 1 @ProfileName = [Name] FROM msdb.dbo.sysmail_profile WHERE [Name] = 'Alert-BI-Admins'
   	
   	SET @tableHTML1 =
 
         	N'<H3 style="color:red; font-family:verdana">Failed SSRS Subscription details. Please resolve & re-run jobs</H3>' +
         	N'<p align="left" style="font-family:verdana; font-size:8pt"></p>' +
         	N'<table border="2" style="font-size:8pt; font-family:verdana; text-align:left">' +
         	N'<tr style="color:black; font-weight:bold">' +
         	N'<th>Report Name</th><th>SQL Agent Job ID</th><th>Owner Username</th><th>Distribution</th><th>Error Message</th><th>Report Location</th><th>Last Run Time</th></tr>' +
         	CAST((
                	SELECT
                       	td = t.[Name],'',
                       	td = t.[ScheduleId],'',
                       	td = t.[UserName],'',
                       	td = t.[Recipients],'',
                       	td = t.[LastStatus],'',
                       	td = t.[Path],'',
                	   	td = t.[LastRunTime]
                	FROM
                       	#tFailedSubs t
                	FOR XML PATH('tr'), TYPE)
         	AS NVARCHAR(MAX) ) +
         	N'</table>'
 
SET @tableHTMLAll = ISNULL(@tableHTML1,'')
 
IF @tableHTMLAll <> ''
   	
   	BEGIN
 
   	--SELECT @tableHTMLAll
         	EXEC msdb.dbo.sp_send_dbmail
                	@profile_name = @ProfileName,
                	@recipients = @EmailRecipient,
                	@body = @tableHTMLAll,
                	@body_format = 'HTML',
                	@subject = @SubjectText
   	END
 
SET NOCOUNT OFF
 
DROP TABLE #tFailedSubs
 
END  

Hi,

we have developed a paginated report that has a suscription baded on data. this way sends an emial to the owners tha has a suscription with an error.

This is the query that the paginated report uses:

SELECT convert(varchar(50), sus.[SubscriptionID]) as SubscriptionID
     , sus.[Description]
      , sus.[LastStatus]
      , sus.LastRunTime
      , cata.[Path]
      , cata.[Name]
      , usucrea.UserName as usuario_creador
	  , usumod.UserName as usuario_modif
	  , RTRIM(corpuser.email) as email_usu_modif
	  , RTRIM(corpuser.full_name) as nombre_usu_modif
      , getdate() as fhasta
      , getdate() - 1 as fdesde
FROM [PBIReportServer].[dbo].[Subscriptions] sus WITH(NOLOCK)
    left join [PBIReportServer].[dbo].[Catalog] cata WITH(NOLOCK) on sus.Report_OID = cata.ItemID
    left join [PBIReportServer].[dbo].[Users] usucrea WITH(NOLOCK) on cata.[CreatedByID] = usucrea.UserID
    left join [PBIReportServer].[dbo].[Users] usumod WITH(NOLOCK) on cata.[ModifiedByID] = usumod.UserID
    left join [PBIReportServer].[dbo].[my_table_of_corp_users] corpuser WITH(NOLOCK) on usumod.UserName = corpuser.ID
where sus.EventType = 'TimedSubscription'
    and (
        (sus.LastStatus like 'Done:%' and sus.LastStatus not like '%0 errors%' )
    or sus.LastStatus like 'Failure%' 
      )
    and getdate()-1 <= sus.LastRunTime and sus.LastRunTime <= GETDATE()

my_table_of_corp_users is a table where we tansfer every day name, ID and email of the corporate users.