Fetch Dynamic And Manual Tags For Active Devices
Query
// Define which devices are of interest based on OSPlatform value
let OS = dynamic(["Windows10","Windows11"]);
// Set the threshold for what counts as an active device
// Devices not seen in the last 7 days (or choose otherwise) will be excluded
let ActiveThresholdDays = 7;
DeviceInfo
| where OSPlatform has_any (OS)
| extend LastSeen = Timestamp
// Normalize the dynamic and manual tags columns
| extend DynamicTagsArray = iif(isnull(DeviceDynamicTags),
dynamic([]), todynamic(DeviceDynamicTags))
| extend ManualTagsArray = iif(isnull(DeviceManualTags),
dynamic([]), todynamic(DeviceManualTags))
// Combine both manual and dynamic tags into a single array per device
| extend AllTags = array_concat(DynamicTagsArray, ManualTagsArray)
// Exclude devices with no tags
| where array_length(AllTags) > 0
| mv-expand Tag=AllTags
| extend Tag = tostring(Tag)
| extend DaysSinceLastSeen = datetime_diff("day", now(), LastSeen)
| where DaysSinceLastSeen <= ActiveThresholdDays
| summarize HasTag=any(true) by DeviceName, Tag
// Replace the boolean flag (1) with an emoji for readability in the results
// If the device has tag, mark with ✅, otherwise leave blank
| extend HasTagMark = iif(HasTag == true, "✅", "")
| evaluate pivot(Tag, any(HasTagMark), DeviceName)About this query
Fetch dynamic and manual tags for active devices
Description
This query takes into account the DeviceInfo table and will provide the devices based on OSPlatform value (Windows10, Windows11 etc) and what you consider as an inactive device (last seen 7 days for example) and will identify for each device its tags, whether dynamic or manual.
References
Microsoft Defender XDR
Versioning
| Version | Date | Comments |
|---|---|---|
| 1.0 | 28/08/2025 | Initial publish |
Explanation
This query is designed to identify and list active devices running specific operating systems (Windows 10 or Windows 11) and display their associated tags, both dynamic and manual. Here's a simplified breakdown of what the query does:
-
Filter Devices by OS: It starts by selecting devices that are running either Windows 10 or Windows 11.
-
Define Active Devices: It considers a device as active if it has been seen within the last 7 days.
-
Extract Tags: For each device, it extracts both dynamic and manual tags. If a device has no tags, it is excluded from the results.
-
Combine Tags: The dynamic and manual tags are combined into a single list for each device.
-
Filter by Activity: Only devices that have been active within the defined period (7 days) are included.
-
Summarize and Display: The query summarizes the data to show which devices have tags. It uses an emoji (✅) to indicate the presence of tags for better readability.
-
Pivot the Results: Finally, it pivots the data to display devices and their tags in a clear, organized format.
In essence, this query helps in monitoring and managing devices by providing a clear view of active devices and their associated tags, aiding in device management and security assessments.
Details
Michalis Michalos
Released: August 28, 2025
Tables
Keywords
Operators