Report to extract failed subscriptions

[kreddy1]

Hi Team,

We develoepd multiple paginated reports and created subscriptions in Power Bi Report server. 

Could you please assist the logic to extract failed subsctiptions list from audit DB or audit logs. We want to build report to extract the failed subscriptions it helps team to start investigate the issues immediatly  and fix the issues.  

[vcarazo]

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.

[Shahfaisal]

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 = 'Changeme@craigporteous.com'
   	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