Forum Discussion
Same device with Onboarded and Not Onboarded status
Something like this might do it. The correct term is anti join.
let TableOnboarded = DeviceInfo
| where OnboardingStatus == "Onboarded";
let TableCouldBeOnboarded = DeviceInfo | where OnboardingStatus != "Onboarded";
TableCouldBeOnboarded
| join kind=anti TableOnboarded on DeviceId
jbmartin6 That really helped. I just changed the DeviceId to DeviceName, as the DeviceId is in fact different, what's equal is the devicename. So now the query is like this:
let TableOnboarded = DeviceInfo
| where OnboardingStatus == "Onboarded";
let TableCouldBeOnboarded = DeviceInfo | where OnboardingStatus != "Onboarded" and MachineGroup contains "Windows Server";
TableCouldBeOnboarded
| join kind=anti TableOnboarded on DeviceName
| distinct DeviceNameNow there's only one last issue. There's one device which on the "Onboarded" state, the DeviceName has the domain, like "srv-server.domain.local", and the "Not Onboarded" DeviceName is just the server name, without the domain.
Is there any chance to parse out the domain name and compare just the server name?
Thanks
jbmartin6 Thanks for the tip. I also do the same many times, extract the data with Powershell and then work it out. But as the goal here is to create a Detection Rule, it needs to be a query with Defender.
I'll take a look at those references.
- This is getting outside my experience with Kusto, we typically pull the raw data using the API then manipulate the data locally via powershell. What you suggest may be possible since Kusto breaks values up into substrings automatically, that is part of what makes it such a great query tool for large data sets. Take a look here (https://learn.microsoft.com/en-us/kusto/query/datatypes-string-operators?view=azure-data-explorer&preserve-view=true) and see if you can get something like "Left.Devicename contains Right.DeviceName" to work.