topic Report to extract failed subscriptions in Report Server

Report to extract failed subscriptions

kreddy1 — Fri, 23 Sep 2022 16:08:56 GMT

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.

Re: Report to extract failed subscriptions

Shahfaisal — Fri, 23 Sep 2022 19:04:12 GMT

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

Re: Report to extract failed subscriptions

vcarazo — Wed, 06 Nov 2024 06:48:32 GMT

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.