topic Re: Being alerted to scheduled refresh failures in Report Server

Being alerted to scheduled refresh failures

robm135 — Tue, 01 Feb 2022 00:50:59 GMT

Running Jan 2021 PBI RS

What is a good way to get proactivitly alerted to a scheduled refresh stopping?

Sometimes a scheduled refresh just stops happening and users have to let me know. Once I click 'refresh now' the schedule refreshing begins again.

If PBI RS doesn't natively do this has anyone worked out a decent workaround?

Re: Being alerted to scheduled refresh failures

AnkitKukreja — Tue, 01 Feb 2022 15:21:38 GMT

Hi! robm135

Your schedule refresh stops after 4 failures and 2 months of inactivity.

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh#schedule-refresh

You can check the usage metrics report by clicking on the ellipsis (3 dots) beside your report that will give you idea if the report was ideal or not.

I hope this helps.

Re: Being alerted to scheduled refresh failures

robm135 — Tue, 01 Feb 2022 21:35:31 GMT

thanks, but I'm referring to the powerbi report server. I know in cloud you can set an email alert for scheduled failure but this isn't an option for report server (at least for Jan 2021 which I'm running)

Re: Being alerted to scheduled refresh failures

CDavies — Wed, 02 Feb 2022 07:48:08 GMT

Use this query (connected to your PBI report server) in an SSRS report and schedule the SSRS to email out the failures:

WITH cte_LastRefresh AS ( SELECT MAX(SubscriptionHistoryID) AS SubscriptionHistoryID ,SubscriptionID FROM dbo.SubscriptionHistory WHERE [Status] = 2 GROUP BY SubscriptionID ) SELECT c.Name as [Report Name] --rs.scheduleid ,c.path as [Report Path] ,sub.[Description] as [Schedule Name] --,sub.[LastStatus] as [Last Status] ,SUBSTRING(sh.Details,CHARINDEX('Message":"',sh.Details)+10,200)+ '...' AS [Error Detail] --CASE WHEN sub.[InactiveFlags] = 0 THEN 'Enabled' -- WHEN sub.[InactiveFlags] = 128 THEN 'Disabled' -- ELSE 'Other' --END as [Enabled] --,sub.[EventType] as "Event Type" ,CONVERT(Varchar(17),sub.[LastRunTime],113) as [Last Run Time] ,SUBSTRING(u_own.UserName,CHARINDEX('\',u_own.UserName)+1,10) as [Created by] ,SUBSTRING(u_mod.[UserName],CHARINDEX('\',u_mod.[UserName])+1,10) as [Modified by] ,CASE WHEN jsch.next_run_date = 0 THEN '-' ELSE Convert(Varchar(17),msdb.dbo.agent_datetime(jsch.next_run_date, jsch.next_run_time),113) END as [Next Run Time] --,CASE c.[Type] WHEN 2 THEN 'SSRS' -- WHEN 5 THEN 'Data Source' -- WHEN 7 THEN 'Report Part' -- WHEN 8 THEN 'Shared Dataset' -- When 13 Then 'Power BI' -- ELSE 'Other' -- END AS "ReportType" ,sub.LastStatus FROM [dbo].[Subscriptions] sub INNER JOIN dbo.[Catalog] c ON sub.report_oid = c.itemid INNER JOIN [dbo].Users u_own ON c.CreatedByID = u_own.UserID INNER JOIN [dbo].Users u_mod ON c.ModifiedByID = u_mod.UserID INNER JOIN dbo.ReportSchedule rs ON sub.SubscriptionID = rs.SubscriptionID INNER JOIN cte_LastRefresh lr ON rs.SubscriptionID = lr.SubscriptionID INNER JOIN dbo.SubscriptionHistory sh ON lr.SubscriptionHistoryID = sh.SubscriptionHistoryID LEFT OUTER JOIN msdb.dbo.sysjobs j ON CAST(rs.ScheduleID AS VARCHAR(100)) = j.name LEFT OUTER JOIN msdb.dbo.sysjobschedules jsch ON j.job_id = jsch.job_id WHERE 1=1 AND sub.LastStatus like '%failed%' AND jsch.next_run_date <> 0 AND Convert(smalldatetime,msdb.dbo.agent_datetime(jsch.next_run_date, jsch.next_run_time)) > getdate() order by 2

Re: Being alerted to scheduled refresh failures

robm135 — Wed, 02 Feb 2022 22:17:02 GMT

champion! that's works a treat

Re: Being alerted to scheduled refresh failures

Mubar22 — Mon, 15 Jul 2024 08:45:50 GMT

Does this works other DBMS other than SRSS? Like PostgreSQL?