Querying Windows Build Version History with the Intune Data Warehouse and PowerShell

I had an interesting requirement recently which was to review the OS build numbers of a group of computers over time. This would reveal not only when they got patched, but which patches they installed and when they installed a feature update, for example. Since we aren’t using the Data warehouse in MEMCM and our historic hardware inventory data doesn’t contain the full OS build numbers with the patch level, I turned to MEM and the Intune Data Warehouse to get the information.

A couple of years ago I posted a blog detailing how to query the IDW with PowerShell, using the recommended app registration in Azure and the whole 9 yards, but this time I simplified things a bit.

The collection we need to query is the devicePropertyHistories collection which contains the osVersion property. According to the MS docs, the amount of snapshot data retained varies from table to table, but in my tenant I was able to retrieve 60 days of historic data from this collection.

Unfortunately, the OData query options are limited for the data warehouse, supporting only a subset of the options you might normally use querying Microsoft Graph, for example. This means we basically need to pull in the entire content of the collection, then search on it. We can limit the number of days we want to retrieve data for though, which can speed up the REST API responses.

To start, lets set the $ProgressPreference variable – this speeds up our web requests by not displaying the progress bar.

$ProgressPreference = 'SilentlyContinue'

Then lets define the URL we want to use to connect to the data warehouse. You can get the base URL from the MEM console > Reports > Data warehouse. We’re using the API version 1.0 and I’ve added the devicePropertyHistories collection to the URL.

$DataWarehouseURL = "https://fef.<mytenant>.manage.microsoft.com/ReportingService/DataWarehouseFEService/devicePropertyHistories?api-version=v1.0"
# Optionally set the maximum number of days to query
#$DataWarehouseURL = "https://fef.<mytenant>.manage.microsoft.com/ReportingService/DataWarehouseFEService/devicePropertyHistories?api-version=v1.0&maxhistorydays=14"

Next, let’s get an access token. This assumes that your account has the appropriate permissions or role as detailed here. We’ll use the Az.Account module, so get that installed if you don’t have it.

# Get token for current user (needs appropriate role like Intune Service Administrator)
# Install-Module Az.Accounts
Connect-AzAccount 
$Token = Get-AzAccessToken -ResourceUrl "https://api.manage.microsoft.com/"

Now we’ll prepare the request header, invoke the request, convert and filter the results to find only Windows 10 devices and add them to an arraylist.

$headers = @{'Authorization'="Bearer " + $Token.Token}
$Result = [System.Collections.ArrayList]::new()
Write-host "Processing $DataWarehouseURL"
$WebRequest = Invoke-WebRequest -Uri $DataWarehouseURL -Method Get -Headers $headers
$Content = $WebRequest.Content | ConvertFrom-Json
[void]$Result.Add(($Content.value | Where {$_.osVersion -match "10.0.1" -and $_.jailBroken -eq "Unknown"}))

Because the results are paged and there are more than will be returned in a single request, we loop through the ‘nextLink’ URLs for each request until we’ve built the entire dataset. This can take some time depending how far back you are querying and how much data you have.

Do {
    Write-host "Processing $($Content.'@odata.nextLink')"
    $WebRequest = Invoke-WebRequest -Uri $Content.'@odata.nextLink' -Method Get -Headers $headers
    $Content = $WebRequest.Content | ConvertFrom-Json
    [void]$Result.Add(($Content.value | Where {$_.osVersion -match "10.0.1" -and $_.jailBroken -eq "Unknown"}))
}
While ($null -ne $Content.'@odata.nextLink')

Finally, we flip the arraylist into a regular array (just because I want to :))

$Results = @()
Foreach ($item in $Result)
{
    $Results += $item
}

Now the $Results variable contains our entire dataset, which you can view and query as you wish.

In my case, I want to search for the osVersion changes over time for an individual machine, so:

$ComputerName = 'PC001'
$Records = $Results | 
    Where {$_.deviceName -eq $ComputerName} | 
    Select deviceName,osVersion,@{l='Date';e={(Get-Date ("$(($_.dateKey.ToString()).Substring(0,4))" + " " + "$(($_.dateKey.ToString()).Substring(4,2))" + " " + "$(($_.dateKey.ToString()).Substring(6,2))").ToDateTime((Get-Culture).DateTimeFormat)).ToLongDateString()}}
$Records

The dateKey isn’t in a DateTime friendly format, so we manipulate it a bit to avoid having to cross reference the dates entity.

Viewing the results, I can see this device is on 20H2 and is installing the monthly CU’s not long after they are released

In another example, I can see the device was on 20H2 and got upgraded to 21H1 with the current CU and a couple of days later after ‘patch Tuesday’ (is it a myth?) it got a newer CU.

Finally, for a group of computers I wanted to see what patch level they were at before they got the 20H2 feature update installed, and what patch level they were at after. I prepared a CSV file with computer names, and ran the following code, exporting the results to another CSV file.

$RecordArray = @()
$PCList = Get-Content D:\Temp\Successes.csv -ReadCount 0
foreach ($PC in $PCList)
{
    $PC
    $Records = $Results | Where {$_.deviceName -eq $PC} | Select deviceName,osVersion,@{l='Date';e={(Get-Date ("$(($_.dateKey.ToString()).Substring(0,4))" + " " + "$(($_.dateKey.ToString()).Substring(4,2))" + " " + "$(($_.dateKey.ToString()).Substring(6,2))").ToDateTime((Get-Culture).DateTimeFormat)).ToLongDateString()}}
    If ($Records)
    {
        $UpdateRecord = $Records | Where {$_.osVersion -match "10.0.19042."} | Select -First 1
        $PriorUpdateRecordIndex = $Records.IndexOf($UpdateRecord) -1
        $PriorUpdateRecord = $Records[$PriorUpdateRecordIndex]
        $RecordArray += [PSCustomObject]@{
            deviceName = $PC
            osVersionBeforeUpdate = $PriorUpdateRecord.osVersion
            dateBeforeUpdate = $PriorUpdateRecord.Date
            osVersionAfterUpdate = $UpdateRecord.osVersion
            dateAfterUpdate = $UpdateRecord.Date
        }
    }
}
$RecordArray | Export-CSV  D:\Temp\Successes_Upgrades.csv -Force -NoTypeInformation

This made for some interesting reading!

There’s a wealth of data to access in the Intune Data warehouse and you can use PowerShell, any REST API client, or even Power BI to query and report on the data. Give it a go!