Log Analytics alert rule at specific time

Question

I have a DataFactory with diagnostic setting activate and sending logs to a Log Analytics Workspace.

I want to create an alert that fires only once if an event trigger hasn't run after 9AM.

I think some query like this:

let StartTime =startofday(now());
let EndTime =now();
let CheckHour = 9;
ADFTriggerRun
| where ResourceId contains toupper("DataFactory_Name")
| where TriggerName == "Trigger_Name"
| where TimeGenerated > StartTime and TimeGenerated < EndTime
| extend Hour = datetime_part("hour", TimeGenerated)
| where Hour < CheckHour

But I see some problems if I set the following settings to the alert:

• Number of results less than 0
• Period = 30 minutes
• Frequency = 30 minutes

(If the trigger runs correctly) The alert will fire 18 times before 9AM.

(If the trigger doesn't run) The alert will fire 48 times in a day.

Is there some query to avoid this? Maybe with some if condition?

Answer 1

There is no such solution to directly solve this issue.

I suggest you can set up 2 alerts:

Alert 1: To send alert if the trigger doesn't run all the day. You can use your query in your question, just set Period = 1440 minutes, Frequency = 1440 minutes, Number of results less than 0. Then it will only send one alert email if the trigger doesn't run at all.

Alert 2: Use the query below by adding iff() function:

let StartTime =startofday(now());
let EndTime =now();
let CheckHour = 9;
ADFTriggerRun
| where ResourceId contains toupper("DataFactory_Name")
| where TriggerName == "trigger1"
| where TimeGenerated > StartTime and TimeGenerated < EndTime
| extend Hour = datetime_part("hour", TimeGenerated)
| extend isFailed = iff(Hour < CheckHour, "Success","Failed")
| where isFailed == "Failed"

Then set Period = 30 minutes, Frequency = 30 minutes, Number of results Equal to 1. By using this query / setting, you at most receive 2 email alerts if the triggers runs after 9AM(For example, if the trigger runs at 10:07AM, and the alert scans at 10:20AM / 10:50AM, only at these 2 times, it will send alerts; if the trigger runs before 9AM, no alerts will be sent).