KQL Search

Query Details

HomeAI ToolsDevice Query

MDE Onboarding Status Timeline

Query

# Defender for Endpoint- Onboarding Status Information

## Query Information

### Description

Use the below query to see onboarding status changes

#### References

### Microsoft 365 Defender

Show visual timeline of Onboarding Status Changes

```kql
DeviceInfo
| where Timestamp > ago(30d)
| where DeviceName contains "<DEVICE NAME>"
| summarize FirstRecord = arg_min(Timestamp, *) by bin(Timestamp, 1d) 
| distinct Timestamp, OnboardingStatus, count=1
| render timechart

```

```kql
let osPlatforms = dynamic([
    "Windows10",
    "Windows11",
    "WindowsServer2022",
    "Linux",
    "WindowsServer2019",
    "Windows",
    "WindowsServer2016",
    "WindowsServer2012R2",
    //"Android",
    //"iOS",
    "macOS"
]);
DeviceInfo
| where OSPlatform has_any (osPlatforms)
| where isnotempty( OnboardingStatus)
| summarize
    Onboarded = dcountif(DeviceName, OnboardingStatus == "Onboarded",4),
    OnboardedDevices = make_set_if(DeviceName, OnboardingStatus == "Onboarded"),
    CanbeOnboarded = dcountif(DeviceName, OnboardingStatus == "Can be onboarded",4),
    CanbeOnboardedDevices = make_set_if(DeviceName, OnboardingStatus == "Can be onboarded"),
    InsufficientInfo = dcountif(DeviceName, OnboardingStatus == "Insufficient info",4),
    Unsupported = dcountif(DeviceName,OnboardingStatus == "Unsupported",4)
    by bin(TimeGenerated, 1d)
| extend TotalDevices = Onboarded + CanbeOnboarded + InsufficientInfo + Unsupported
//| extend TotalOnboarded = array_length(OnboardedDevices)
| serialize 
| sort by TimeGenerated desc 
| extend PreviousOnboardedDevices = next(OnboardedDevices)
| extend TotalPreviousOnboardedDevices = array_length(PreviousOnboardedDevices)
| extend OnboardedDelta = Onboarded - TotalPreviousOnboardedDevices
| sort by TimeGenerated desc
| project TimeGenerated, TotalDevices, Onboarded, TotalPreviousOnboardedDevices,OnboardedDelta, CanbeOnboarded, CanbeOnboardedDevices,Unsupported, InsufficientInfo
```

Explanation

This KQL query is designed to analyze and visualize the onboarding status of devices in Microsoft Defender for Endpoint over the past 30 days. Here's a simplified breakdown of what the query does:

  1. Filter by Device and Time:

    • The first part of the query filters device information to show only records from the last 30 days for a specific device (indicated by <DEVICE NAME>).

  2. Visualize Onboarding Status Changes:

    • It summarizes the earliest record of each day for the specified device and creates a distinct list of timestamps and onboarding statuses. This data is then visualized as a time chart to show how the onboarding status has changed over time.

  3. Analyze Onboarding Status Across Platforms:

    • The second part of the query focuses on devices across various operating system platforms (like Windows, Linux, macOS, etc.).
    • It counts and categorizes devices based on their onboarding status: "Onboarded," "Can be onboarded," "Insufficient info," and "Unsupported."
    • It calculates the total number of devices and tracks changes in the number of onboarded devices over time.
    • The query also identifies which devices can be onboarded and those that have insufficient information or are unsupported.

  4. Output and Sorting:

    • The results are sorted by the time they were generated, and the query calculates the difference in the number of onboarded devices compared to the previous day.
    • Finally, it projects key metrics like the total number of devices, onboarded devices, and changes in onboarding status.

Overall, this query helps administrators monitor and understand the onboarding status of devices in their network, providing insights into how many devices are onboarded, can be onboarded, or face issues, and how these numbers change over time.

Details

Alex Verboon profile picture

Alex Verboon

Released: September 17, 2025

Tables

DeviceInfo

Keywords

DeviceInfoTimestampDeviceNameOnboardingStatusOSPlatformOnboardedCanbeOnboardedInsufficientInfoUnsupportedTimeGeneratedTotalDevicesOnboardedDevicesCanbeOnboardedDevicesPreviousOnboardedDevicesTotalPreviousOnboardedDevicesOnboardedDelta

Operators

agocontainssummarizearg_minbindistinctrenderletdynamichas_anyisnotemptydcountifmake_set_ifextendserializesortnextarray_lengthproject

Actions