Defender for Endpoint - Active - Inactive Devices

MDE Device Active Inactive

Query

let ActiveThresholdDays = 30;
let OS = dynamic(["Windows10","Windows11"]);
DeviceInfo
| where TimeGenerated > ago(30d)
| where OSPlatform has_any (OS)
| where OnboardingStatus == 'Onboarded'
| summarize arg_max(TimeGenerated,*) by DeviceId
| extend LastSeen = Timestamp
| extend DaysSinceLastSeen = datetime_diff("day", now(), LastSeen)
| extend DynamicTagsArray = iif(isnull(DeviceDynamicTags), 
    dynamic([]), todynamic(DeviceDynamicTags))
| project TimeGenerated,LastSeen, DaysSinceLastSeen,DeviceName, OSPlatform, MachineGroup, DynamicTagsArray 
// Show all active devices
//| where DaysSinceLastSeen <= ActiveThresholdDays
// Show all inactive devices
| where DaysSinceLastSeen >=  ActiveThresholdDays

About this query

Defender for Endpoint - Active - Inactive Devices

Query Information

Description

This query allows to identify active and non-active devices.

References

Author

Alex Verboon

Defender XDR

With state column

let ActiveThresholdDays = 30;
let OS = dynamic(["Windows10","Windows11"]);
DeviceInfo
| where TimeGenerated > ago(30d)
| where OSPlatform has_any (OS)
| where OnboardingStatus == 'Onboarded'
| summarize arg_max(TimeGenerated,*) by DeviceId
| extend LastSeen = Timestamp
| extend DaysSinceLastSeen = datetime_diff("day", now(), LastSeen)
| extend DynamicTagsArray = iif(isnull(DeviceDynamicTags), 
    dynamic([]), todynamic(DeviceDynamicTags))
| extend State = iif(DaysSinceLastSeen <= ActiveThresholdDays, "🟢 Active", "⚪ Inactive")
| project TimeGenerated, LastSeen, DaysSinceLastSeen, State,
          DeviceName, OSPlatform, MachineGroup, DynamicTagsArray

Explanation

This KQL query is designed to identify and categorize devices as either active or inactive based on their last seen activity within Microsoft Defender for Endpoint. Here's a simplified breakdown of what the query does:

Set Parameters:
- ActiveThresholdDays is set to 30, meaning a device is considered active if it has been seen within the last 30 days.
- The query focuses on devices running Windows 10 or Windows 11.
Filter Data:
- It looks at device information from the last 30 days.
- It only includes devices that are "Onboarded" to the system.
Identify Latest Activity:
- For each device, it finds the most recent record (latest activity).
Calculate Days Since Last Seen:
- It calculates how many days have passed since the device was last active.
Tag Devices:
- It checks if there are any dynamic tags associated with the device and includes them in the results.
Determine Device State:
- It categorizes each device as "Active" (🟢) if it was seen within the last 30 days, or "Inactive" (⚪) if not.
Output Results:
- The query projects (displays) relevant information such as the last seen date, number of days since last seen, device name, operating system, machine group, dynamic tags, and the active/inactive state.

This query helps in monitoring device activity and ensuring that all devices are accounted for and properly managed within the organization's network.

Details

Alex Verboon

Released: December 9, 2025

Tables

DeviceInfo

Keywords

Devices

Operators

letdynamicagohas_anysummarizearg_maxextenddatetime_diffnowiifisnulltodynamicproject

Actions

GitHub

KQL Search