Microsoft Defender - Device Inventory - Network and IoT

MDE Device Inventory Network Io T

Query

// IoT Device Inventory
DeviceInfo
| where DeviceCategory == @"IoT"
| summarize arg_max(Timestamp, *) by DeviceId
| join (
    DeviceNetworkInfo
    | mv-expand todynamic(IPAddresses)
    | extend IPAddress = tostring(parse_json(IPAddresses).IPAddress)
    | summarize arg_max(Timestamp, *) by DeviceId
    )
    on $left.DeviceId == $right.DeviceId
| project Timestamp, DeviceId, DeviceName, DeviceType, DeviceSubtype, IPAddress, MacAddress, Model, Vendor, OSPlatform, OSVersion, OSDistribution, ExposureLevel
//| summarize count() by DeviceType, DeviceSubtype

About this query

Microsoft Defender - Device Inventory - Network and IoT

Query Information

Description

Use the below queires to retrieve device inventory information of discovered Network and IoT devices

References

Microsoft 365 Defender

IoT Device Inventory

Network Device Inventory

// Network Device Inventory
DeviceInfo
| where DeviceCategory == @"NetworkDevice"
| summarize arg_max(Timestamp, *) by DeviceId
| join (
    DeviceNetworkInfo
    | mv-expand todynamic(IPAddresses)
    | extend IPAddress = tostring(parse_json(IPAddresses).IPAddress)
    | summarize arg_max(Timestamp, *) by DeviceId
    )
    on $left.DeviceId == $right.DeviceId
| project Timestamp, DeviceId, DeviceName, DeviceType, DeviceSubtype, IPAddress, MacAddress, Model, Vendor, OSPlatform, OSVersion, OSDistribution
//| summarize count() by DeviceType, DeviceSubtype

Explanation

The above queries are used to retrieve device inventory information for Network and IoT devices in Microsoft Defender.

For IoT Device Inventory:

The query filters the DeviceInfo table to only include devices with the DeviceCategory as "IoT".
It then uses the summarize and arg_max functions to get the latest information for each device based on the Timestamp.
The DeviceInfo table is joined with the DeviceNetworkInfo table using the DeviceId.
The final result includes the Timestamp, DeviceId, DeviceName, DeviceType, DeviceSubtype, IPAddress, MacAddress, Model, Vendor, OSPlatform, OSVersion, OSDistribution, and ExposureLevel for each device.

For Network Device Inventory:

The query filters the DeviceInfo table to only include devices with the DeviceCategory as "NetworkDevice".
It then uses the summarize and arg_max functions to get the latest information for each device based on the Timestamp.
The DeviceInfo table is joined with the DeviceNetworkInfo table using the DeviceId.
The final result includes the Timestamp, DeviceId, DeviceName, DeviceType, DeviceSubtype, IPAddress, MacAddress, Model, Vendor, OSPlatform, OSVersion, and OSDistribution for each device.

Details

Alex Verboon

Released: June 4, 2023

Tables

DeviceInfoDeviceNetworkInfo

Keywords

DevicesIntuneUserMicrosoft DefenderDevice InventoryNetwork and IoT

Operators

wheresummarizearg_maxbyjoinonprojectmv-expandextendtostringparse_json

Actions

GitHub

KQL Search