Creating KQL Query to Detect and Alert on Offline Log Sources

Question

G'Day,

We're trying to alert when one or more log sources go offline in Sentinel, then project or summarize the offline log source(s) into an offense for review. I'm using the Heartbeat table here as an example because most people will have it. The immediate way I thought of doing this was using set operations. The thought being you pull the set of "heartbeats" from the last 24 hours (for example), then pull a separate set of heartbeats from the last hour. Using set_difference() ("A - B"), if the returned array is not {}, report an offense to Sentinel. (Evidently, if it's {}, nothing has gone offline.) The reverse case, B-A is not a consideration, as that would be new log sources being added, which is fine.

If this was only a log source or two we needed information on, I'd create a different kind of rule. But in some cases we have thousands of high priority log sources that cannot go offline.

I've taken a variety of stabs at this using different methods of summarizing the data I need, with... no results.

let HBDay = Heartbeat
| where TimeGenerated > ago(24h)
| summarize make_set(Computer);
Heartbeat
| where TimeGenerated > ago(1h)
| summarize make_set(Computer)
| project set_difference(HBDay.set_Computer, set_Computer)

The last line I suspect isn't valid KQL. I was trying to pull the set out of the first query, and then compare them. That (expectedly) failed. I've tried variations of this - pulling the first pull of data (using distinct(), assigning that to a variable, then checking it against the second set. Something like:

let A = Heartbeat
| where TimeGenerated > ago(24h)
| distinct Computer;
Heartbeat
| where TimeGenerated > ago(24h)
| distinct Computer
| where Computer !in ((A))

I haven't gotten anywhere, except a headache. Has anyone done something similar or have guidance?

Answer 1

There are several workbooks with related queries that can be used as examples. Maybe something like this:

Answer 2

// this is what I tend to run and prefer, this shows me Tables with no data in the past 15mins and also the 3 previous 15min intervals (so I know in the Alert if this is normal or not ) union * // Look back in the last hour, run this rule on a 5min frequency | make-series count() default=0 on TimeGenerated from ago(1h) to now() step 15m by Type | where count_ [-1] == 0 // look at the last record [-1] and only show events when last data point was equal to zero | project-away TimeGenerated ![168334-screenshot-2022-01-25-144439.png][1] [1]: /api/attachments/168334-screenshot-2022-01-25-144439.png?platform=QnA