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 sysmon.exe running as it's been widely deployed. The purpose of my query is to identify devices that do not have sysmon.exe running.
The problem I run into using the DeviceProcessEvents table is that when I run an example query
DeviceProcessEvents
| where FileName !== "Sysmon.exe" or FileName !== "Sysmon64.exe"
| project DeviceName, FileName, InitiatingProcessAccountName, InitiatingProcessFileName, InitiatingProcessFolderPath, InitiatingProcessCommandLine
I get other processes that are not "Sysmon.exe or Sysmon64.exe" which is not what I want. I want to identify devices which do not have the Sysmon.exe or Sysmon64.exe process running. All suggestions are welcome.
6 Replies
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.
- Interesting scenario. How about running the kql for devices running the process and bouncing that list against the entire set of enrolled devices? That should technically give you the list of devices that haven’t run the process.
rahuljindal Thanks for your response and suggestion. Modifying the query as suggested below shows. Any suggestions on which kql commands I can use to make the comparisons against all enrolled devices? I am quite new to KQL so learning as much as I can.
DeviceProcessEvents
| where FileName == "Sysmon.exe" or FileName == "Sysmon64.exe"
| project DeviceName, FileName, InitiatingProcessAccountName, InitiatingProcessFileName, InitiatingProcessFolderPath, InitiatingProcessCommandLine- Does this command give you any results? If yes, then export it and extract the devices names. Then pull the list of all the enrolled devices in Defender. You can do that using kql or just pull it directly from devices under asset management. Now with both the lists exported, you can do a vlookup and filter out the devices that were missing in the first list from the kql. Everything is manual and not really elegant way, but it should atleast get you started until you work out the kql.
