Download all Office 2016 updates with PowerShell and SCCM

I’ve recently been working on a big project to rollout thin clients across libraries in a London borough. I will be writing up a collection posts which will make up the automation of a cloud based VDI solution in Azure, starting off today with keeping the Office 2016 MSI/Volume License edition app up to date in SCCM.

When deploying any modern Office version with SCCM, you want to keep it up to date by adding all the latest .msp files to the /updates folder.

I adapted some popular scripts found online ┬áand added what I thought were cool additions.

This one: maintaining-your-office-2016-installation-source involved creating a new SUG and SU Package in SCCM, a bit yucky.

This one: download-office-2016-proplus-x86-updates had a static list that has to be maintained by the script owner and used custom guids to name the files.

Both these issues are ‘resolved’ using the code below.
Pre-reqs:

Access to the ConfigManager PSD1 PoSh module. You can load the module in a Admin PoSh prompt like:

$ConfigMgr_psd1_path = $Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + '\ConfigurationManager.psd1'
 
Import-Module $ConfigMgr_psd1_path

SCCM Sofware Updates sync configured to download Office 2016 metadata
Full Administrator access to SCCM Primary Site Server

$siteserver = "srv-sccm.domain.local"  
$sitecode = "AB0"
$class = "SMS_SoftwareUpdate"
$NameSpace = "root\SMS\Site_$sitecode"
$StagingLocation = "T:\O2016_Updates"
$MSPsFolder = "$StagingLocation\ready"</code>
 
Function ConvertFrom-Cab
{
    [CmdletBinding()]
    Param(
    $cab,
    $destination,
    $filename
    )
    $comObject = "Shell.Application"
    Write-Verbose "Creating $comObject"
    $shell = New-Object -Comobject $comObject
    if(!$?) { $(Throw "unable to create $comObject object")}
    Write-Verbose "Creating source cab object for $cab"
    $sourceCab = $shell.Namespace("$cab").items()
    if(-not (Test-Path $destination))
    {
        Write-Verbose "Creating destination folder object for $destination"
        new-item $destination -ItemType Directory
    }
    $DestinationFolder = $shell.Namespace($destination)
    Write-Verbose "Expanding $cab to $destination"
    $DestinationFolder.CopyHere($sourceCab)
    ForEach($Cabitem in $sourceCab){
        If($Cabitem.name -like "*.msp"){
            Rename-Item -Path "$destination\$($Cabitem.name)" -NewName ($fileName)
        }
    }
}
 
Remove-Item $StagingLocation -Force -Confirm:$False -Recurse
$Office2016Updates = Get-CMSoftwareUpdate -Name "*Microsoft*2016*32-Bit*" -Fast | ? {($_.IsExpired -ne "False") -and ($_.IsSuperseded -ne "False")}
md $StagingLocation
 
ForEach($Update in $Office2016Updates){
    $CI_ID = $Update.CI_ID
    $ContentID = (get-wmiobject -ComputerName $siteserver -Query "select * from SMS_CItoContent where ci_id=$CI_ID" -Namespace $NameSpace).ContentID
    $objContent = Get-WmiObject -ComputerName $siteserver -Namespace $NameSpace -Class SMS_CIContentFiles -Filter "ContentID = $ContentID"
    $Filename1 = "KB$((([uri]$Update.LocalizedInformativeURL).AbsolutePath -split "/")[2])"
    $CabFileName = $objContent.Filename
    $FileName2 = ($CabFileName -split ".cab")[0]
    $FileNameWithoutExtension = $FileName2 + "_" + $Filename1
    write-host "FileNameWithoutExtension will be: $FileNameWithoutExtension"
    $URL = $objContent.SourceURL
    write-host "$URL" -ForegroundColor Green
    try
    {
        $FileNamePath = "$StagingLocation\$CabFileName"
        write-host "FileNamePath = $FileNamePath"
        Start-BitsTransfer -Source $URL -Destination $FileNamePath
        If(Test-Path $FileNamePath)
        {
            Write-Output "$FileNamePath found, extracting files from cab..."
            ConvertFrom-Cab -cab $FileNamePath -destination $MSPsFolder -filename $($FileNameWithoutExtension+".msp")
            write-host "Deleting items cabs from $StagingLocation"
            Get-ChildItem -Path $StagingLocation -File -Filter *.cab | Remove-Item -Force -Confirm:$false
            write-host "Deleting items xmls from $MSPsFolder"
            Get-ChildItem -Path $MSPsFolder -File -Filter *.xml | Remove-Item -Force -Confirm:$false
        }
    }
    catch
    {
        write-host "stopping here"
    }
} 

In your output folder $MSPsFolder you will have all the MSPs you need to copy into the /updates folder in the Office 2016 app/package source.