Need a query for OMS agents NOT sending logs in the past 24 hours.

[SocInABox]

Hi there,

I have a watchlist of my oms agents.

I'd like to use DeviceProcessEvents to list agents that have NOT reported any processes in the past 24 hours.

I don't want to use the Heartbeat table for this.

I'm looking for agents that are possibly still sending heartbeats but they're 'unhooked' from memory, so they're blind to most processes.

eg:

let watchlist = (_GetWatchlist('OMSagents')|project Computer);
DeviceProcessEvents
|where DeviceName in (watchlist)
|summarize max(TimeGenerated) by DeviceName
|where max_TimeGenerated < now(-7d)

So the above query works, but only if all sensors in the list have logs in DeviceProcessEvents in the past 7 days.

Thank you in advance for your feedback.

[#2 · Accepted solution]

@SocInABox The main problem is that you won't know if one is missing unless it has sent data in the past.  So no matter whether you choose 7, 14, or 90 days, if the device has never sent data you won't know about it.

I would do a comparison with the Heartbeat table and see if the devices that are not sending data show up more in there and if so do a join with that table to get a listing of the server and see if they show up in the DeviceProcessEvents table.

[#3]

you're correct Gary, thanks, I hadn't thought it through all the way.
much appreciated!

[#4]

This may also be helpful

https://github.com/Azure/Azure-Sentinel/tree/master/Playbooks/Send-ConnectorHealthStatus