SQL Query Builder to Convert O365 Version to Build

Chris.Kibble

Administrator
Staff member
#1
This script scrapes the MS website to get version to build information for Office 365 and then builds a SQL statement to query your CM database for the information.

PowerShell:
# MinVersion is the minimum version you care about to export as case statements.
$minVersion = [version]"16.0.6001.1061"

# Define URL with Build Table
$releaseNotesURL = "https://support.office.com/en-us/article/Version-and-build-numbers-of-update-channel-releases-ae942449-1fca-4484-898b-a933ea23def7"


Function Convert-TableCellToVersionArray {
    
    # Takes cell data as input, breaks up by new-line character, and then breaks out build and version by known location in string.

    Param(
        [string]$cellData
    )

    $result = @()

    $cellData.Split("`n") | ? { $_ -ne "`r" } | % {
        
        $input = $_

        $build = [string]$input.Substring(8, 4)
        $version = [version]"16.0.$($input.substring(20, 9))"
      
        $o = New-Object -TypeName PSObject
        $o | Add-Member -MemberType NoteProperty -Name Version -Value $version
        $o | Add-Member -MemberType NoteProperty -Name Build -Value $build
    
        $result += $o

    }
    
    Return $result

}


# Query URL
$releaseContent = Invoke-WebRequest -Uri $releaseNotesURL

# Get Version Table (ID May Change?)
$versionTable = $releaseContent.ParsedHtml.IHTMLDocument3_getElementById('tblID0EAABACAAA')

# Define Array to Hold Result Objects
$builds = @()

# Loop Over Results and Build.  Skip Row[0]: Header Row

for($i = 1; $i -lt $versionTable.rows.length; $i++) {
    
    $rowData = $versionTable.rows[$i]
    $releaseDate = $rowData.cells[0].innerText
    $channelMonthly = $rowData.cells[1].innerText
    $channelSemiAnnualTargeted = $rowData.cells[2].innerText
    $channelSemiAnnual = $rowData.cells[3].innerText
    
    If($channelMonthly -ne 'Not applicable') { $builds += Convert-TableCellToVersionArray $channelMonthly }
    If($channelSemiAnnualTargeted -ne 'Not applicable') { $builds += Convert-TableCellToVersionArray $channelSemiAnnualTargeted }
    If($channelSemiAnnual -ne 'Not applicable') { $builds += Convert-TableCellToVersionArray $channelSemiAnnual }

}

# Loop over array and create when statements for SQL.

$sqlOutut = "
    select distinct v_r_system.Name0
            , v_Add_Remove_Programs.DisplayName0
            , v_Add_Remove_Programs.Version0
            , v_r_system.operating_system_name_and0
            , v_GS_WORKSTATION_STATUS.LastHWScan
            , case "


$builds | ? { $_.Version -ge $minVersion } | Sort-Object Version | % {
    $sqlOutut += "when v_Add_Remove_Programs.Version0 = '$($_.version)' then '$($_.build)'`r`n                   "
}

$sqlOutut += "else 'Unknown'`r`n               end as Office365BuildNumber
          from v_R_System
          join v_GS_WORKSTATION_STATUS
            on v_r_system.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
          join v_Add_Remove_Programs
            on v_R_System.ResourceID = v_Add_Remove_Programs.ResourceID
           and v_Add_Remove_Programs.DisplayName0 = 'Microsoft Office 365 ProPlus - en-us'
        where v_GS_WORKSTATION_STATUS.LastHWScan >= GetDate()-30
        order by Version0 desc
"

$txtOutput = "$([System.IO.Path]::GetTempFileName()).txt"
$sqlOutut | Out-File $txtOutput

& $txtOutput

Start-Sleep -Seconds 2

Remove-Item $txtOutput
 
Top