Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
6 | |
4 | |
3 | |
2 |
User | Count |
---|---|
13 | |
10 | |
5 | |
4 | |
3 |