Same device with Onboarded and Not Onboarded status

[dmarquesgn]

Hi,

I'm creating a detection rule to search for servers which are not onboarded to Defender. What's strange about this query is that I get the same device (same devicename but different deviceid) with both Onboarding status, which is "Onboarded" and "Can be onboarded".

Anyone knows why? This way I get uncorrect results on my detection rule.

Thanks

[#2]

I get duplicate devices in the Device Inventory when a device has been reimaged, but I also note that it is picking up a different OSPlatform as well. Are both DeviceIds in your Device Inventory?

[#3]

@GI472 

Hi. On the Device Inventory panel I only see the onboarded device, I don't the see the other one.

[#4]

@dmarquesgn What query did you use to return that result? 

[#5]

@GI472 Here you have it:

[#6]

From the 'can be onboarded' phrase my guess is the Device Discovery feature in MDE generated one of the entries, from traffic sniffing on another machine. Then when the server was onboarded a separate entry was created. I don't know if MDE has some way of matching Device Discovery entries to endpoints onboarded later. It might be useful to check if you see the same sort of results for other machines. I suggest you consider turning off Device Discovery unless you really want it, it uses up a lot of CPU and clutters your device data. If you wait 30 days or so the 'can be onboarded' entry will probably disappear.

[#7]

@jbmartin6 I do have more machines in this state, about 5 or 6 servers. I wouldn't like to turn off device discovery, as we're using it actively to detect some stuff.

[#8]

@dmarquesgn What I was thinking now is that inside the query, I could do a check and if the query returned 2 devices with the same name, and one is "Onboarded", then it would not list the other one. But not sure how to do this on kql.

[#9]

You can't just ignore the 'can be onboarded' entries? I guess you want to know if there is a 'can be onboarded' that is actually not onboarded

[#10]

@jbmartin6 Yes, the goal is to find which Windows Servers exists without being onboarded. So I can ignore the ones which are not well classified.

But there's one big issue, I have a detection rule based on this query, generating alerts, so this means I will generate a lot of false positives, and the SOC analysts will have to treat each alert, so it's kind of bad having false positives, providing them unnecessary work regularly.

[#11]

I think a negative join is what you need here, get a table of all the 'can be onboarded' and all the 'onboarded' and use a negative join to get the entries in the first table that are not in the second table

[#12]

@jbmartin6 I got the idea. I don't have enough experience with KQL to build something like that. Do you have any idea how to build that kind of query? Or any place where I find some examples to build on that?

Thanks

[#13]

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

[#14]

@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 DeviceName

Now 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

[#15]

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&pr...) and see if you can get something like "Left.Devicename contains Right.DeviceName" to work.

[#16]

@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.