topic Re: Scheduled Refresh notifications (On-Premise) in Report Server

Scheduled Refresh notifications (On-Premise)

xantari — Fri, 06 Apr 2018 14:58:12 GMT

We can not find a way to get Refresh Notification failure emails when a scheduled data refresh occurs on-premise.

Apparently this has existed for a long time on Power BI (because they have to deal with a lot more data connectivity issues with the gateway service I am guessing).

Where is this function in the on-premise version? As it is important on-premise as it is on cloud service.

Re: Scheduled Refresh notifications (On-Premise)

Anonymous — Fri, 06 Apr 2018 16:58:13 GMT

In Power BI report Server, every schedule you create goes to SQL Server Agent JOB, so you can go to the SQL Server Agent JOB and then you can add one more step after the current step for sending the email. You might need to configure email in SQL in order to send the email through JOB.

Re: Scheduled Refresh notifications (On-Premise)

xantari — Fri, 06 Apr 2018 17:27:15 GMT

WHich one of the 150 SQL Agent jobs do I do that on. They are just labeled as a GUID.

This doesn't seem like a good solution.

Microsoft needs to fix this if there is no other way. Why don't they just add a task at the end of these SQL Agent jobs for the server administrator if it is setup in the Power BI Server backend?

Seems like basic functionality is missing here.

Re: Scheduled Refresh notifications (On-Premise)

xantari — Fri, 06 Apr 2018 17:29:36 GMT

FYI, I found the SQL AGent job (labeled as GUID 87B024E7-A122-4DE9-818A-8016826EB9E3) in SQL Agent.

It shows as successful this morning. But the data refresh shows error on Power BI front end.

So it appears SQL Agent failures do NOT occur when the data refresh fails.

Re: Scheduled Refresh notifications (On-Premise)

Anonymous — Sat, 07 Apr 2018 04:25:06 GMT

HI xantari,

You can create your idea for this on the below URL and keep voting for the same.

https://ideas.powerbi.com/forums/265200-power-bi-ideas

Re: Scheduled Refresh notifications (On-Premise)

Anonymous — Mon, 09 Apr 2018 13:48:52 GMT

The problem is the call to the subscription succeeds though the subscription itself fails. Which is, as you say, a bit rubbish!

You can add a second SQL Step to each SQLAgentJob and basically force a failure using some code like the stuff below. You can then just add your "operators" to the Notifications for the SQL Agent job and set the first step up to call the second on success and both steps to Fail the job!

This will generate a SQL Agent ALERT to the operators! You could of course log the details of the query into a table somewhere before you call RAISEERROR so you would have the details of what had failed.

Figuring out why the **bleep** thing failed is another matter entirely.

DECLARE @SQLAgentJobID UNIQUEIDENTIFIER;


SET @SQLAgentJobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)));

IF EXISTS(

	SELECT	sj.name AS SQLAgentJobName
		   ,c.Name AS ReportName
		   ,c.[Path] AS ReportPath
		   ,s.[Description] AS SubscriptionName
		   ,rs.SubscriptionID
		   ,s.LastStatus
		   ,s.EventType
		   ,s.LastRunTime
		   ,sj.date_created
		   ,sj.date_modified
	FROM	ReportServer.dbo.ReportSchedule rs
			INNER JOIN msdb.dbo.sysjobs sj
				ON rs.ScheduleID = CAST(sj.name AS UNIQUEIDENTIFIER)
				   AND 101 = sj.category_id
			--INNER JOIN ReportServer.dbo.ReportSchedule c
			--    ON CAST(sj.name AS uniqueidentifier) = c.ScheduleID
			INNER JOIN ReportServer.dbo.Subscriptions s
				ON rs.SubscriptionID = s.SubscriptionID
			INNER JOIN ReportServer.dbo.[Catalog] c
				ON s.Report_OID = c.ItemID
	-- to find specific report last status 
	-- WHERE e.name = 'Usage Stats'
	-- to find failed status
	WHERE	LastStatus <> 'Completed Data Refresh'
	-- for a specific SQL Agent Job
			AND sj.name = @SQLAgentJobID)
BEGIN
	--LOG the query results above to something if you want a simple way of finding out what has failed
        RAISERROR('Data Refresh Failed!',16,1);
END
-- NOTE you have to look in subscription history for any error messages/details (good luck with that!)

PS Have I mentioned how shoddy and third rate the logging and monitoring is for PBI SSRS Server?

Re: Scheduled Refresh notifications (On-Premise)

Anonymous — Mon, 09 Apr 2018 14:14:10 GMT

I have just realised that there is a "magic number" in that SQL that makes it work.

INNER JOIN msdb.dbo.sysjobs sj
				ON rs.ScheduleID = CAST(sj.name AS UNIQUEIDENTIFIER)
				   AND 101 = sj.category_id

the 101 is the category_id of the Job Category called "Report Server"

you can find these using the following.

USE msdb
GO

SELECT * FROM dbo.syscategories

I think 101 is safe on most systems but it may well be different on your installation

Re: Scheduled Refresh notifications (On-Premise)

Anonymous — Tue, 10 Apr 2018 09:16:23 GMT

Thanks much Anonymous, for the info. Much Appriciated.

Re: Scheduled Refresh notifications (On-Premise)

Anonymous — Tue, 10 Apr 2018 10:13:16 GMT

I extended the query above slightly in the following post to show the errors that are recorded by the refresh process

Data Refresh Fail

Re: Scheduled Refresh notifications (On-Premise)

adestarBI23 — Thu, 07 Jun 2018 17:53:02 GMT

Please use the below query and schedule it through SQL Server Agent to notify/email certain users if a Data Refresh failure happens and that will notify you if a data refresh did not run successfuly.

SELECT Subscriptions.Description,
Subscriptions.LastStatus,
Subscriptions.EventType,
MAX(SubscriptionHistory.StartTime) start_time,
MAX(SubscriptionHistory.EndTime) end_time,

SubscriptionHistory.Message,
SubscriptionHistory.Details

FROM subscriptions
JOIN dbo.SubscriptionHistory
ON Subscriptions.SubscriptionID = SubscriptionHistory.SubscriptionID
WHERE CAST(StartTime AS Date) = CAST(GETDATE() AS DATE)

GROUP BY Subscriptions.Description,
Subscriptions.LastStatus,
Subscriptions.EventType,
SubscriptionHistory.Status,
SubscriptionHistory.Message,
SubscriptionHistory.Details

HAVING Subscriptions.LastStatus LIKE 'Data Refresh failed%'

Note: This query will be run in the BI Database that has the metadata for the Power BI Report Server.

Hope this helps!

Re: Scheduled Refresh notifications (On-Premise)

adestarBI23 — Thu, 07 Jun 2018 18:01:31 GMT

Please use the below query and schedule it through SQL Server Agent to notify/email certain users if a Data Refresh failure happens and that will notify you if a data refresh did not run successfuly.

SELECT Subscriptions.Description,
Subscriptions.LastStatus,
Subscriptions.EventType,
MAX(SubscriptionHistory.StartTime) start_time,
MAX(SubscriptionHistory.EndTime) end_time,

SubscriptionHistory.Message,
SubscriptionHistory.Details

FROM subscriptions
JOIN dbo.SubscriptionHistory
ON Subscriptions.SubscriptionID = SubscriptionHistory.SubscriptionID
WHERE CAST(StartTime AS Date) = CAST(GETDATE() AS DATE)

GROUP BY Subscriptions.Description,
Subscriptions.LastStatus,
Subscriptions.EventType,
SubscriptionHistory.Status,
SubscriptionHistory.Message,
SubscriptionHistory.Details

HAVING Subscriptions.LastStatus LIKE 'Data Refresh failed%'

Note: This query will be run in the BI Database that has the metadata for the Power BI Report Server.

Hope this helps!

Re: Scheduled Refresh notifications (On-Premise)

agent_007 — Fri, 08 Jun 2018 03:50:23 GMT

This is my solution as second step in SQL Agent. Preliminarily you should configure MS SQL MAIL and find out GUID of your Catalog.ItemID = '????'. This step sends notification by mail after successfully refreash data (1 step) to all users of this report

DECLARE	
	@xml  XML,
	@str varchar(8000) = ''
SELECT @xml = CAST(XmlDescription as XML)
FROM Catalog WITH (NOLOCK)
INNER JOIN Policies WITH (NOLOCK) ON Catalog.PolicyID = Policies.PolicyID
LEFT OUTER JOIN SecData WITH (NOLOCK) ON Policies.PolicyID = SecData.PolicyID AND AuthType = 1
WHERE Catalog.ItemID = 'E3280AC8-EFF4-4AF6-8ED3-583C6112139A' 
AND PolicyFlag = 0
declare
	@t table (mail_to varchar(100))
SET QUOTED_IDENTIFIER ON;
insert 
	@t
SELECT	xmlData.A.value('.', 'VARCHAR(100)') AS mail
FROM	@xml.nodes('Policies/Policy/GroupUserName') xmlData(A)
delete @t
where mail_to = 'BUILTIN\Administrators'
update @t
set mail_to = REPLACE(REPLACE(mail_to, 'KCELL.KZ\',''), '@kcell.kz','') + '@kcell.kz'

SELECT @str = @str + ISNULL(t.mail_to + ';', '') FROM @t t

SET @str = SUBSTRING(@str, 1, LEN(@str) - 1)

WAITFOR DELAY '00:10:00'; 

DECLARE @body NVARCHAR(MAX)
SET @body = '
<html>
	<body>
		<H2>Daily RSSS-OU-CCHT statistics</H2>
		<H3>Data were updated successfully!</H3>
		<p><a href="http://192.168.223.13/Reports/powerbi/CCD%20reports/Daily%20RSSS-OU-CCHT%20statistics">Open report</a></p>
	</body>
</html>'

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'power_bi_report_server',
    @recipients = @str,
    @copy_recipients = 'your_name@kcell.kz',
    @body = @body,
    @subject = 'Power BI Report Server Alert',
    @body_format = 'HTML';

Re: Scheduled Refresh notifications (On-Premise)

georgzeplin — Thu, 09 Mar 2023 08:46:15 GMT

Please vote for this: https://ideas.powerbi.com/ideas/idea/?ideaid=a9ac0d7b-4a95-ec11-826d-501ac50aa35e