KQL Query to extract list of devices

[dmarquesgn]

Hi,

I'm trying to automate some things on our environment and now I'm trying to extract a list of devices from our Defender environment, so then to make some comparisons over powershell. So my first query was simply:

DeviceInfo | project DeviceId, DeviceName, ClientVersion, OSPlatform, JoinType, AadDeviceId, OnboardingStatus, DeviceCategory, DeviceType, DeviceSubtype, Model, Vendor, OSDistribution, OSVersionInfo, SensorHealthState

Then I just saw that it returns several instances of the same "DeviceId", so as a next step I need to remove duplicates to get unique devices. But this is where something is not matching as it should. If I extract a list of devices just adding a | distinct DeviceId, I get a list of 3610 devices. If I go to the Defender portal, over Assets -> Devices, and on the "Computer & Mobile" tab I export that list I got 4358 devices, and all of them have unique DeviceId. So I'm not getting the differences in these numbers.

Anyone know which query is behind the Devices view? So that way I could replicate it and try to understand what I'm missing here.

Thanks

[#2]

Check the time/days in the KQL editor @dmarquesgn 

The asset inventory is last 30 days, whereas you may have your KQL query set to last 7 days or something.

If it's not that, then pass. 

[#3]

@GI472 

Thanks for the tip. The issue is certainly because of the 30 days of data which is returned by the Hunting Query, while on the exported list of devices from the "Devices" view in the portal, I got devices which the last device update was 6 months ago.

So now I'm using this query:

DeviceInfo
    | distinct DeviceId, DeviceName, ClientVersion, OSPlatform, JoinType, AadDeviceId, OnboardingStatus, DeviceCategory, DeviceType, DeviceSubtype, Model, Vendor, OSDistribution, OSVersionInfo, SensorHealthState

 Now I've got another odd issue. Running the hunting query on the defender portal, I've got 4079 results. Running the hunting query over powershell returns 8105 results, and most of the devices are duplicated, which is kind of odd and didn't yet understood why.