KQL Search

Query Details

HomeAI ToolsDevice Query

Device Compare OS Build Today And Yesterday

Query

// Compare OS Version changes between yesterday and today. It will calculate the difference (number of devices) between two days.
let Yesterday=
IntuneDevices
| where TimeGenerated < ago(1d) 
| summarize arg_max(TimeGenerated, *) by DeviceName
| where todatetime(LastContact) > ago(30d) 
| summarize count() by OSVersion
| sort by OSVersion desc
| extend CustomName = OSVersion
| extend Version_Yesterday = count_;
let Today=
IntuneDevices 
| summarize arg_max(TimeGenerated, *) by DeviceName
| where todatetime(LastContact) > ago(30d) 
| summarize count() by OSVersion
| sort by OSVersion desc
| extend CustomName = OSVersion
| extend Version_Today = count_;
Yesterday
| join kind=inner Today on OSVersion
| project CustomName, Version_Today, Version_Yesterday, Difference = Version_Today-Version_Yesterday
| sort by CustomName desc

Explanation

This query is comparing the changes in operating system (OS) versions on devices between yesterday and today. It first identifies the most recent data for each device that has been in contact within the last 30 days. Then, it counts the number of devices for each OS version and sorts them in descending order. This is done separately for the data from yesterday and today.

The query then combines the data from both days based on the OS version. It calculates the difference in the number of devices for each OS version between the two days. Finally, it sorts the results in descending order by OS version.

Details

Ugur Koc profile picture

Ugur Koc

Released: August 3, 2022

Tables

IntuneDevices

Keywords

OSVersion,Yesterday,Today,IntuneDevices,TimeGenerated,DeviceName,LastContact,Count,CustomName,VersionYesterday,VersionToday,Difference

Operators

letIntuneDeviceswhereTimeGeneratedago()summarizearg_max()byDeviceNametodatetime()LastContactcount()OSVersionsortdescextendjoinkind=innerprojectDifferencesortdesc.

Actions