Forum Discussion
KQL Query Help - Identify all devices without Sysmon running
I am working on a query to highlight devices within the environment that do not sysmon.exe running on them. There are several hundreds of devices in the environment and all of these should have sysmo...
You can use an anti-join here to make a table of all the devices with sysmon then clip them from the DeviceInfo table. It is also possible you could leverage the Software Inventory data in MDE (the
DeviceTvmSoftwareInventory table I think?)
// make a table with all devices seen that have sysmon.exe running
let Sysmonhosts =
DeviceProcessEvents
| where FileName == "sysmon.exe" // adjust this to suit variations on the process name
| distinct DeviceId; // the reduces the data to just a list of unique deviceIds
// now clip all these device Ids from the DeviceInfo table
DeviceInfo
| join kind=leftanti Sysmonhosts on DeviceId
OK, well maybe DeviceInfo isn't quite the way to do this, but anti-join is the operation you need to remove matching data from a larger data set.