Query Details
MDE Device Active Inactive
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
```kql
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
```
With state column
```kql
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:
ActiveThresholdDaysis 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