Custom Alerting: count per hour is <1 in a time frame

[SakshiSachdeva]

Hi All,

I have below DQL query for which I need to setup alerting only if the count per hour is less than 1 from 6am to 11pm CST. Problem is- if the count is zero that hour is not getting captured because of null values, so difficult to put condition check on this. Can anyone suggest how to replace null values with 0 so that alert can be created for such scenario. We are using workflows to create alerts and have total of 10-15 alerts like this scenario which we are trying to group together under single cron of every hour[0 * * * *] by putting hour check in the query as different alerts have different timeframes.

DQL query:

fetch logs, from: -60m, to: now()
| filter matchesPhrase(host.name,"prod*") //Environment
| filter matchesPhrase(content,"AUTO_ADJUSTMENT") AND matchesPhrase(content,"submitXMLToKafka")
| fieldsAdd hour= toLong((formatTimestamp(timestamp, format:"H", timeZone:"America/Chicago")))
| summarize count=count(),by: hour
| filter (hour>6 AND hour<23 AND count<1)

Sample output below[hour 6 and 7 are not even captured as count would be zero and treated as null and that's what we need alerting exactly on]

Thank you!

[deni]

Hi @SakshiSachdeva ,

Here is my suggestion:

data record(hour = 6),
record(hour = 7),
record(hour = 8),
record(hour = 9),
record(hour = 10),
record(hour = 11),
record(hour = 12),
record(hour = 13),
record(hour = 14),
record(hour = 15),
record(hour = 16),
record(hour = 17),
record(hour = 18),
record(hour = 19),
record(hour = 20),
record(hour = 21),
record(hour = 22)
| join [
fetch logs, timeframe:"2025-08-20T21:00:00Z/2025-08-21T00:10:00Z"
| fieldsAdd hour = toLong(formatTimestamp(timestamp, format:"H", timeZone:"America/Chicago"))
| summarize count = count(), by: hour
], on: {left[hour] == right[hour]}, kind: outer
| fields hour, count = coalesce(right.count, 0)
| filter (hour > 6 AND hour < 23 AND count < 1)

I removed the filters, to be able to test on my env, but the result looks like what you need:

And this what it looks like if I remove the last filter:

Regards, Deni

[SakshiSachdeva]

Hi @deni ,

I appreciate your input; however, it is not applicable to my scenario. My alerts are configured to run every hour within specific time range, and if we adhere to the specified hours, the value will consistently remain at 0.

For example, this alert should only be activated between 6 AM and 10 PM CST, executing every hour to check if the count is less than 0 and sending notifications only when the hours and conditions align.

When I apply a condition to verify the last 60 minutes, it returns all hours with a count of 0, resulting in false positives.

DQL Query-
data record(hour = 6),
record(hour = 7),
record(hour = 8),
record(hour = 9),
record(hour = 10),
record(hour = 11),
record(hour = 12),
record(hour = 13),
record(hour = 14),
record(hour = 15),
record(hour = 16),
record(hour = 17),
record(hour = 18),
record(hour = 19),
record(hour = 20),
record(hour = 21),
record(hour = 22)
| join [
fetch logs, from: -60m, to: now()
| filter matchesPhrase(host.name,"prod*") //Environment
| filter matchesPhrase(content,"AUTO_ADJUSTMENT") AND matchesPhrase(content,"submitXMLToKafka")
| fieldsAdd hour = toLong(formatTimestamp(timestamp, format:"H", timeZone:"America/Chicago"))
| summarize count = count(), by: hour
], on: {left[hour] == right[hour]}, kind: outer
| fields hour, count = coalesce(right.count, 0)
| filter (hour > 6 AND hour < 23 AND count < 1)

Output-

My requirement is to check for that particular hour if the count is less than 0.

Note- I am using this query as "Execute DQL Query" as one of the tasks in the workflows which sends email based on the output. 

Currently, the alerts are configured as separate workflows with individual c; however, we are attempting to consolidate them into a single cron job by incorporating hours in the filter condition to decrease the total number of workflows.

[deni]

What about this one:

data record(dummy = true)
| makeTimeseries { ts = start(), cnt = count(default: 0) },
    time: now(), from: now()-60m, to: now(), interval: 1h, nonempty: true
| expand ts
| fields hour = formatTimestamp(ts, format:"H")
| join [
    fetch logs, from: now()-60m, to: now()
    | filter contains(content, "kiugiugi")
    | summarize c = count(),
        by: { hour = formatTimestamp(timestamp, format:"H") }
], on: { left[hour] == right[hour] }, kind: leftouter
| fields hour, count = coalesce(right.c, 0)
| sort hour asc

Regards, Deni