Receive email for Refresh failure

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