KQL Search

Query Details

HomeAI ToolsDevice Query

MDE Device Inventory Network Io T

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

```kql
// 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
```

Network Device Inventory

```kql
// 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 profile picture

Alex Verboon

Released: June 4, 2023

Tables

DeviceInfoDeviceNetworkInfo

Keywords

Devices,Intune,User,MicrosoftDefender,DeviceInventory,NetworkandIoT

Operators

wheresummarizearg_maxbyjoinonprojectmv-expandextendtostringparse_json

Actions