PowerShell – Use SCCM Automated Reports to Create Software Update Rollups

A while back, I showed off how you could use PowerShell to create Software Update packages and deployments for SCCM.  I was asked about my process for grabbing outstanding updates and adding them into the package.  I’ve since automated this method, and thought I’d share an update with the class.

SCCM gives you the ability to create subscriptions to automatically generate reports and email them or deposit them on a file share.  For the purpose of rollup creation, I use ‘Compliance 3 – Update group (per update) under Software Updates – A Compliance’.  The process is simple – Create a software update group of all of the updates you track and right-click on the report, click create subscription, and fill out the necessary fields.  Make sure you select the report to be delivered by Windows File Share, and Overwrite an existing file with a newer version.

Once you’ve completed the process and SCCM has generated its first report, you can use it to mine for the data you want.  First, we’ll need to import the CSV file.

Import-Csv "\\Server01\Patching\Critical and Security Updates All Workstations.csv"


As you will very quickly find, you’re not going to immediately get the data you want.  This is because the canned report adds 6 rows of header data that’s completely unusable for our purposes.  Not to worry though!  It can easily be remedied using Get-Content and re-exporting the data you want to a new CSV.

 Get-Content "\\server01\Patching\Critical and Security Updates All Workstations.csv" | Select-Object -Skip 6 | Out-File "\\server01\Patching\OutstandingWksUpds.csv"
 (Import-csv -Path "\\server01\Patching\OutstandingWksUpds.csv")

CMReports2This gives us a little something more to work with.  But we’re dealing with a few hundred lines of information, so let’s thin this out a bit.  For our purposes, I’m going to just look for updates for Windows components and Office for my workstations.  So we’ll filter out the following as a base:

  • Updates that are missing on 0 machines
  • Updates that have Server in the description
  • Updates that are applicable to applications we don’t wish to include. (Lync, SQL, Onedrive, etc.)

So now we’ll have a filter statement that looks like this:

(Import-csv -Path "\\Server01\Patching\OutstandingWksUpds.csv").where({$PSItem.Details_Table0_Missing -ne 0 -and $PSItem.Details_Table0_Vendor0 -notlike "*Server*" -and $PSItem.Details_Table0_Vendor0 -notlike "*Lync*" -and $PSItem.Details_Table0_Vendor0 -notlike "*Skydrive*"  -and $PSItem.Details_Table0_Vendor0 -notlike "*Onedrive*"-and $PSItem.Details_Table0_Vendor0 -notlike "*Sharepoint*"}) | Select-Object Details_Table0_Vendor0,Details_Table0_Missing

There’s a lot in there,  but once you’ve filtered out the things you don’t want, you’ll wind up with a nicely ordered list:CMReports3

Now that we have the updates, we can incorporate them into a Software Update Group using some of my previous code.

Important note: If you attempt to run the first line of the script while mapped to your SCCM PSDrive, you will get an error on the Out-File “Cannot open file because the current provider (AdminUI.PS.Provider\CMSite) cannot open a file.”  So make sure you’re not in your CM PSDrive until you’re ready to execute your CM cmdlets.

Get-Content "\\Server01\Patching\Critical and Security Updates All Workstations.csv" | Select-Object -Skip 6 | Out-File "\\Server01\Patching\OutstandingWksUpds.csv"
$update = (Import-csv -Path "\\Server01\Patching\OutstandingWksUpds.csv").where({$PSItem.Details_Table0_Missing -ne 0 -and $PSItem.Details_Table0_Vendor0 -notlike "*Server*" -and $PSItem.Details_Table0_Vendor0 -notlike "*Lync*" -and $PSItem.Details_Table0_Vendor0 -notlike "*Skydrive*"  -and $PSItem.Details_Table0_Vendor0 -notlike "*Onedrive*"-and $PSItem.Details_Table0_Vendor0 -notlike "*Sharepoint*"}) | Select-Object Details_Table0_Vendor0,Details_Table0_Missing

$PowerShellPath = "\\Server01\C$\Program Files\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1"
$CMSiteCode = "A00"
Import-Module $PowerShellPath
CD ("$CMSiteCode" + ":")

         $UpdateGroupArgs = @{
                            'Name' = 'Workstations Update Rollup';
                            'Description' = 'Created with Powershell!'
                            'UpdateID' = '16802522','16801092','16803970'
New-CMSoftwareUpdateGroup @UpdateGroupArgs
    ForEach ($upd in $update){Add-CMSoftwareUpdateToGroup -SoftwareUpdateName $upd.Details_Table0_Vendor0 -SoftwareUpdateGroupName $UpdateGroupArgs.Name }

Execute the script, and then check your Software Update Groups and you’ll find you have a basic rollup package to test and deploy to your systems.

Using the same methods, you can streamline your process for creating new Software Update groups for your monthly deployments as well.  Nothing beats a set of eyeballs to review the list and make sure you’ve got what you want to push, but importing a list of outstanding updates and adding them straight to an update group sure as heck beats selecting them individually through the console.