Device Windows Version Pivot Table
Query
//Create a pivot table of all Windows OS versions in your environment
//Data connector required for this query - M365 Defender - Device* tables
//Microsoft Sentinel query
DeviceInfo
| where TimeGenerated > ago(30d)
| where isnotempty(OSBuild)
| summarize arg_max(TimeGenerated, *) by DeviceId
| where isnotempty(OSPlatform)
| evaluate pivot(OSBuild, count(), OSPlatform)
| where OSPlatform contains "Windows"
| sort by OSPlatform desc
//Advanced Hunting query
//Data connector required for this query - Advanced Hunting license
DeviceInfo
| where Timestamp > ago(30d)
| where isnotempty(OSBuild)
| summarize arg_max(Timestamp, *) by DeviceName
| where isnotempty(OSPlatform)
| evaluate pivot(OSBuild, count(), OSPlatform)
| where OSPlatform contains "Windows"
| sort by OSPlatform descExplanation
This query creates a pivot table that shows the count of different Windows OS versions in your environment. It uses the DeviceInfo table from either the M365 Defender or Advanced Hunting data connectors, depending on the query. The query filters the data to include only the past 30 days and devices with a non-empty OSBuild and OSPlatform. It then groups the data by DeviceId or DeviceName and selects the latest timestamped record for each device. The pivot function is used to count the occurrences of each OSBuild for each OSPlatform. The results are filtered to include only Windows OS platforms and sorted in descending order by OSPlatform.
Details
Matt Zorich
Released: June 17, 2022
Tables
DeviceInfo
Keywords
DevicesIntuneUserM365 DefenderDeviceInfoTimeGeneratedOSBuildDeviceIdOSPlatformevaluatepivotWindowssortTimestampDeviceNameAdvanced Hunting license
Operators
whereisnotemptysummarizearg_maxbyevaluatepivotcountcontainssort