Power BI Report Server: sending a notification when a scheduled refresh fails

There is no built-in feature to do this. The failure information would be in the log files, so you could build a process to ingest those and notify you from there.

I think it's also technically possible to find that information in the database, but the schema is not documented and accessing the database directly like that is only supported for the ExecutionLog views. If you access the other tables /views you could deadlock with internal Report Server processes and affect the stability of your system. But you will probably find blog posts from people who have figured out approaches like this.

This should be built-in but it's not.

Personnally I use the following query on the ReportServer database to get the failed refreshes and I'm using our monitoring system to throw an alert if it returns any rows.

WITH latest AS
( SELECT ItemPath, MAX(TimeStart) AS LatestTimeStart FROM dbo.ExecutionLog3 WHERE ItemAction = 'DataRefresh'  GROUP BY ItemPath ),
scheduled AS
(
	SELECT c.Path
	FROM dbo.Catalog c 
		INNER JOIN dbo.ReportSchedule rs ON c.ItemID = rs.ReportID
)
SELECT *
FROM dbo.ExecutionLog3 log
	INNER JOIN latest ON latest.ItemPath = log.ItemPath AND latest.LatestTimeStart = log.TimeStart
	INNER JOIN scheduled ON latest.ItemPath = scheduled.Path
WHERE latest.ItemPath <> 'Unknown' AND log.Status <> 'rsSuccess' AND log.TimeEnd > DATEADD(DAY, -1, GETDATE())

d_gosbell 's idea is great. you should definetely follow his suggestion

logs by default are here: C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles

under installation folder.