-- Copyright 2020, Ryan McCauley
-- https://sqlryan.com
--
-- This code uses the GNU All-permissive License:
-- Copying and distribution of this file, with or without modification, are permitted in any medium without royalty,
-- provided the copyright notice and this notice are preserved. This file is offered as-is, without any warranty.
--
-- For more information on how to use this code, please visit the post at the author's site
-- https://sqlryan.com/2020/08/adding-failed-refresh-notification-emails-to-power-bi-server/



SET NOCOUNT ON

-- These are the variables that you need to change:
DECLARE @ToAddresses VARCHAR(1000)           = 'You@SomeDomain.com',
		@URLRootDataRefresh VARCHAR(1000)    = 'https://(YourPBIServer)/reports/',
		@IgnorePath VARCHAR(1000)            = '/IgnoreThisFolder/%',
		@DBMailProfile VARCHAR(500)          = 'Your DBMail Profile',
		@SendEmail BIT                       = 0		-- Leave as 0 to print the email body, set to 1 to actually send email


-- These are the variables you can change if you want, but not required:
DECLARE @CopyAddresses VARCHAR(1000)         = NULL,
		@Blind_copy_recipients VARCHAR(1000) = NULL, 
		@ThresholdSeconds INT                = 300, -- 5 minutes
		@Subject VARCHAR(1000)               = 'Failed Power BI Refresh Alert'
		

-- These variables are just for use later:
DECLARE @Body VARCHAR(8000),
		@FailedReports INT,
		@LongestPath INT,
		@LongestName INT,
		@LongestLink INT,
		@CRLF VARCHAR(2) = CHAR(13) + CHAR(10)

-- Now let's get started!
	SET @URLRootDataRefresh = REPLACE(@URLRootDataRefresh + '/manage/catalogitem/listcaching_pbi', '//', '/')

	SET @Body = 'Some Power BI Server reports had their last data refresh fail. Please investigate and resolve below.' + @CRLF + @CRLF

	;with ls as (SELECT  [SubscriptionID]
						,max([EndTime]) as LastSuccessTime
				   FROM [PBIReportServer].[dbo].[SubscriptionHistory]
				  WHERE Status = 0 -- Success ("Completed Data Refresh")
			   group by SubscriptionID)

	  select LEFT(c.Path, LEN(c.Path) - LEN(c.Name)) as Path,
			c.Name, 
			@URLRootDataRefresh + Path as DataRefreshURL,
			s.Description, s.LastStatus, LastRunTime, ls.LastSuccessTime
	  into #workingtable
	  from [dbo].[Subscriptions] s
	  join dbo.[Catalog] c
		on s.Report_OID = c.ItemID
	  left
	  join ls
		on s.SubscriptionID = ls.SubscriptionID
	  where s.LastStatus not in ('Completed Data Refresh', 'New Scheduled Refresh Plan', 'Refreshing Data')
		and c.path not like @IgnorePath
		and DATEDIFF(ss, LastRunTime, GETDATE()) < @ThresholdSeconds 

	
	SET @FailedReports = @@ROWCOUNT

	IF @FailedReports > 0
	BEGIN

		-- Figure out how wide to make the columns
		SET @LongestName = (SELECT MAX(LEN(Name))			FROM #workingtable)
	    SET @LongestPath = (SELECT MAX(LEN(path))		    FROM #workingtable)
		set @LongestLink = (SELECT MAX(LEN(DataRefreshURL)) FROM #workingtable)
			
			
		-- Now generate the headers
		SELECT @Body = @Body + ('Report Title:' + SPACE(@LongestName - 13 + 2))
							 + ('Path:' + SPACE(@LongestPath - 5 + 2))
							 + ('Refresh Status Link:' + SPACE(@LongestLink - 20))
							 + @CRLF
							 + REPLICATE('-', @LongestName) + SPACE(2)
							 + REPLICATE('-', @LongestPath) + SPACE(2)
							 + REPLICATE('-', @LongestLink)
							 + @CRLF


		-- Add each failed report as a new row
		SELECT @Body = @Body + Name + SPACE(@LongestName - LEN(Name) + 2)
							 + Path + SPACE(@LongestPath - LEN(Path) + 2)
							 + DataRefreshURL
							 + @CRLF
		  FROM #workingtable
		  ORDER BY Name, Path

		  SET @Body = @Body + @CRLF + 'This email was generated by ' + @@SERVERNAME + ' at ' + CONVERT(VARCHAR, GETDATE(), 120)

		  IF @SendEmail = 1
		  BEGIN
			  -- Actually send the email
				EXEC msdb.dbo.sp_send_dbmail
					@profile_name = @DBMailProfile,
					@recipients = @ToAddresses,
					@copy_recipients = @CopyAddresses,
					@Blind_copy_recipients = @Blind_copy_recipients,
					@subject = @Subject,
					@body = @Body
		  END
		  ELSE
		  BEGIN
				PRINT @Body
		  END
	END


DROP TABLE #workingtable