Receive email for Refresh failure

[gemidriver]

Is it possible to setup email alerts if a scheduled refresh fails?

[Jayendran]

 Hi @gemidriver  The feature which you are looking for is now available with the latest update 🙂 

https://powerbi.microsoft.com/en-us/blog/sending-refresh-notifications-to-others/

[braddo81]

that looks like it is for cloud

we use on prem

[matthewtmitchel]

How do we request that this feature is added to the On Premise version of Power BI Report Server?  We have internal clients that are requesting this, and although there are ways documented in the message string around it, it would be much easier for our internal clients to have the "Send me a notification email if the refresh fails" option available.

[seena]

You can schedule refresh at the dataset level. Goto Schedule refresh and select below option at the bottom of where you enter the schedule time.

Send refresh failure notification to me

[gemidriver]

please explain?

we have different types of error messages

refresh at dataset level?

we have to setup a refresh on each report

what i would like is someway of knowing if a refresh has failed

[PeterNesbitt]

Scheduled refreshing is done at the dataset level for the Power BI Service. Reports will use the latest data that is in the dataset it connects to.

There's limited email notification settings for a refresh failure, but you can tick the 'Send refresh failure notification email to me'. I can confirm this works (example below) :). I'm not sure if this sends an email to all owners of the workspace or not, someone else may be able to confirm that.

[gemidriver]

we are using the on premise report server - not the cloud version

[PeterNesbitt]

Ahh yes! My apologies, I jumped the gun.  It seems you and I both did not read the reference articles that Yuliana Gu posted above. Noteably, https://workingondata.wordpress.com/2017/11/02/pbirs-scheduled-data-refresh-execution-history/

That contains all the details we are after 🙂

[v-yulgu-msft]

Hi @gemidriver,

At the moment, it is not supported to setup email alert for scheduled refresh failure.

Regards,

Yuliana Gu

[gemidriver]

ok so it isn't supported

is there something that can be used ?

we have some schedules failing to refresh, and it would be great to be able to be notified in some way

cheers

[Anonymous]

We have setup a couple of SSRS reports to make it easier to see the status of the refreshes across the server, the top level report shows the status of all data refreshes. You could schedule this report to be delivered via email and indeed you could schedule a version of it to be delivered that only showed recent failures. Its not quite what you want but its better than nothing.

The query to get the status of the refreshes is here (NOTE this can be broken by changes to the underlying ReporTServer DB schema in future relases of SSRS-PBI Server

SELECT
	sj.name AS SQLAgentJobName
	,c.name AS ReportName
	,c.[path] AS ReportPath
	,c.ItemID
		, CASE 
		WHEN s.laststatus = 'Completed Data Refresh' THEN s.laststatus
		WHEN LEFT(s.laststatus,19) =  'Data Refresh failed' THEN 'Data Refresh failed'
		ELSE 'Status Unknown'
		END AS ShortStatus
	, 'http://yourservername/yourserverpath/manage/catalogitem/properties' + c.[path]  AS managePath
	, REPLACE(  LEFT(c.[path], CHARINDEX('/',c.[path],2)) , '/' ,'') AS basepath
	,s.[Description] as SubscriptionName
	, rs.SubscriptionID
	, s.laststatus

	,sh.SubscriptionHistoryID
	, CASE s.laststatus
		WHEN 'Completed Data Refresh' THEN 1
		ELSE 0
		END AS successFlag
	, CASE s.laststatus
		WHEN 'Completed Data Refresh' THEN 0
		ELSE 1
		END AS FailFlag
	, s.eventtype
	, s.LastRunTime
	, CASE jsch.next_run_date
            WHEN 0 THEN cast('n/a' as char(10))
            ELSE	convert(char(10), 
							CONVERT(datetime, 
									CONVERT(char(8),
											jsch.next_run_date)
									)
							,120)  
					+ ' ' 
					+ left(
							STUFF(
									(stuff(
											(replicate(
														'0', 
														6 - len(jsch.next_run_time)
													  )
											)
											+ convert(VARCHAR(6),
														jsch.next_run_time
													  )
											,3
											,0,
											':'
											)
									)
									,6
									,0,
									':'
								  ),
							8)
        END AS NextRunTime
	, sj.date_created
	, sj.date_modified
	, 1 AS ct
FROM ReportServer.dbo.ReportSchedule rs 
		INNER JOIN msdb.dbo.sysjobs sj
            ON rs.ScheduleID = CAST(sj.name AS uniqueidentifier)
			and 101 = sj.category_id
			LEFT OUTER JOIN msdb.dbo.sysjobschedules jsch
				ON sj.job_id = jsch.job_id
        INNER JOIN ReportServer.dbo.Subscriptions s
            ON  rs.SubscriptionID = s.SubscriptionID
			AND 'DataModelRefresh' = s.eventTYpe
        INNER JOIN ReportServer.dbo.[Catalog] c
            ON s.report_oid = c.itemid
		LEFT OUTER JOIN (SELECT MAX(SubscriptionHistoryID) AS SubscriptionHistoryID, SubscriptionID FROM dbo.SubscriptionHistory GROUP BY SubscriptionID) sh
			ON rs.SubscriptionID = sh.SubscriptionID

[v-yulgu-msft]

Hi @gemidriver,

The scheduled refresh history will be recorded in Power BI Report Server database hosted on SQL Server. Maybe you could configure an alert in SQL Server when the state of scheduled refresh is recorded as "Failure" which will send an email to specific recipients.

Reference: PBIRS scheduled refresh data refresh: Execution history
                  Query Details of given SSRS Report from ReportServer Database tables

                  Send an email from a trigger

                  Get an alert when a certain record changes in SQL Server

Best regards,

Yuliana Gu

[gemidriver]

Hi

Any ideas on tables/columns to monitor?