topic Re: Receive email for Refresh failure in Report Server

Receive email for Refresh failure

gemidriver — Wed, 03 Jan 2018 04:23:49 GMT

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

Re: Receive email for Refresh failure

v-yulgu-msft — Thu, 04 Jan 2018 01:38:35 GMT

Hi gemidriver,

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

Regards,

Yuliana Gu

Re: Receive email for Refresh failure

gemidriver — Thu, 04 Jan 2018 02:19:19 GMT

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

Re: Receive email for Refresh failure

v-yulgu-msft — Sat, 27 Jan 2018 06:49:08 GMT

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

Re: Receive email for Refresh failure

gemidriver — Wed, 31 Jan 2018 20:21:23 GMT

Any ideas on tables/columns to monitor?

Re: Receive email for Refresh failure

seena — Wed, 23 May 2018 15:15:47 GMT

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

Re: Receive email for Refresh failure

gemidriver — Wed, 23 May 2018 22:42:45 GMT

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

Re: Receive email for Refresh failure

PeterNesbitt — Thu, 24 May 2018 11:33:30 GMT

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.

Re: Receive email for Refresh failure

gemidriver — Thu, 24 May 2018 11:52:08 GMT

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

Re: Receive email for Refresh failure

PeterNesbitt — Thu, 24 May 2018 14:47:53 GMT

Ahh yes! My apologies, I jumped the gun. :manfrustrated: 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 :)

Re: Receive email for Refresh failure

matthewtmitchel — Mon, 15 Oct 2018 14:00:35 GMT

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.

Re: Receive email for Refresh failure

Jayendran — Thu, 11 Jul 2019 13:06:44 GMT

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/

Re: Receive email for Refresh failure

Anonymous — Thu, 11 Jul 2019 22:07:30 GMT

that looks like it is for cloud

we use on prem

Re: Receive email for Refresh failure

Anonymous — Fri, 12 Jul 2019 08:52:15 GMT

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