Overview

This process will allow you to automate deployment of required WSUS updates in your SCCM environment that were missed by your Software Update ADR. A built in SCCM SQL report can indicate which WSUS software updates are required, but not deployed in an environment.

Utilizing my two other functions, HTML Email Report and SCCM Report to Array, you can help automate the process of detecting these updates and re-injecting them into the proper Software Update Groups which target the computers requiring them.

Fundamentals

At its core, the idea here is to automate running this report and to do something with the values that are returned. For example we can choose the report we want to target:

$ReportPath="/ConfigMgr_DGM/Software Updates - B Deployment Management/Management 2 - Updates required but not deployed"

And then choose the same parameters that we would have chosen int he GUI above;

$inputParams = @{
    "CollID"="DGM00084";
    "UpdateClass"="Security Updates";
    "Vendor"="Microsoft";
}

and then using my SQL to Array function we can store the report results as an array:

$array = Get-DMGSCCMSQLReport -inputParams $inputParams `
                               -ReportServerUrl $ReportServerUrl `
                               -ReportPath $ReportPath `
                               -ProviderMachineName $ProviderMachineName `
                               -Sitecode $Sitecode

These returned results are easily be passed into the next phase where they are automatically injected into the proper Software Update Group:

$updates = $array
$undeployedupdates=$updates | %{Get-CMSoftwareUpdate -ArticleId $_.update -Fast | ?{$_.nummissing -ge 1}} 
$PilotSoftwareUpdategroup=Get-CMSoftwareUpdateGroup -Name "Desired Software Update Group* nnn"
$undeployedupdates | %{Add-CMSoftwareUpdateToGroup -SoftwareUpdateId $_.CI_ID -SoftwareUpdateGroupName "SVR - 2 - Production Servers Updates - All other Products* nnn"}

You can then choose the Software Update Group you want these automatically injected inside of:

#Multiple Arrays
Get-DMGEmailReport `
    -Arrays $OutputArrays `
    -ReportTitle "Updates Required but Not Deployed Report" `
    -from "SCCMSQLReports@corporation.com" `
    -To "c-dmaiolo@corporation.com" `
    -subject "SCCM Report: Required But Not Deployed (Not Superseded, Not Expired, Not Security Only)"

For this example let’s choose to also have the results first emailed out utilizing my HTML Email Function. This generates an email, indicating which updates were included:

PowerShell Invocation

Import-Module \\scriptserver\scripts\DMGSCCM\Get-DMGSCCMSQLReport\Get-DMGSCCMSQLReport.psm1 -Force
Import-Module \\scriptserver\scripts\Get-DMGEmailReport\Get-DMGEmailReport.psm1 -Force

#Set Universal Parameters for this Report
$ReportServerUrl="http://sccmsqlrserver/ReportServer"
$ReportPath="/ConfigMgr_DGM/Software Updates - B Deployment Management/Management 2 - Updates required but not deployed"

#Create Some Arrays Of Data To Display in Report. You can create as many as you want.
$OutputArrays = @()
$ProviderMachineName = "sccmsqlrserver.corp.corporation.com"
$Sitecode = "DGM"

Set-Location $Sitecode":"

#Array1
$inputParams = @{
    "CollID"="DGM00084";
    "UpdateClass"="Security Updates";
    "Vendor"="Microsoft";
}

$array = Get-DMGSCCMSQLReport -inputParams $inputParams `
                               -ReportServerUrl $ReportServerUrl `
                               -ReportPath $ReportPath `
                               -ProviderMachineName $ProviderMachineName `
                               -Sitecode $Sitecode
Set-Location $Sitecode":"  
Write-Host "Gonna take a while..."                                          
$arrayresult = $array | %{Get-CMSoftwareUpdate -ArticleId $_.Details_Table0_Title -Fast| ?{$_.nummissing -ge 1 -and $_.IsExpired -eq $FALSE -and $_.isSuperseded -eq $FALSE -and $_.LocalizedDisplayName -notlike "*Security Only*"}} | `
                               Select ArticleID,LocalizedDisplayName,NumMissing,NumPresent,IsSuperseded,IsExpired -Unique | Sort-Object -Descending -Property NumMissing

$output = [PSCustomObject] @{
'Message' = "These are all of the Windows Updates that are required but not deplyoyed for All Servers.";
'Title' = "All Production Servers with Maintenanace Window (Security Updates): Required But Not Deployed";
'Color' = "Red";
'Array' = $arrayresult
}

if ($output.Array -ne $NULL){$OutputArrays+=$output}


#Array2
$inputParams = @{
    "CollID"="DGM00084";
    "UpdateClass"="Critical Updates";
    "Vendor"="Microsoft";
}

$array = Get-DMGSCCMSQLReport -inputParams $inputParams `
                               -ReportServerUrl $ReportServerUrl `
                               -ReportPath $ReportPath `
                               -ProviderMachineName $ProviderMachineName `
                               -Sitecode $Sitecode

Set-Location $Sitecode":"                                               
Write-Host "Gonna take a while..."                                          
$arrayresult = $array | %{Get-CMSoftwareUpdate -ArticleId $_.Details_Table0_Title -Fast| ?{$_.nummissing -ge 1 -and $_.IsExpired -eq $FALSE -and $_.isSuperseded -eq $FALSE -and $_.LocalizedDisplayName -notlike "*Security Only*"}} | `
                               Select ArticleID,LocalizedDisplayName,NumMissing,NumPresent,IsSuperseded,IsExpired -Unique | Sort-Object -Descending -Property NumMissing

$output = [PSCustomObject] @{
'Message' = "These are all of the Windows Updates that are required but not deplyoyed for All Servers.";
'Title' = "All Production Servers with Maintenanace Window (Security Updates): Required But Not Deployed";
'Color' = "Red";
'Array' = $arrayresult
}

if ($output.Array -ne $NULL){$OutputArrays+=$output}

#Array3
$inputParams = @{
    "CollID"="DGM00085";
    "UpdateClass"="Security Updates";
    "Vendor"="Microsoft";
}

$array = Get-DMGSCCMSQLReport -inputParams $inputParams `
                               -ReportServerUrl $ReportServerUrl `
                               -ReportPath $ReportPath `
                               -ProviderMachineName $ProviderMachineName `
                               -Sitecode $Sitecode

Set-Location $Sitecode":"                                               
Write-Host "Gonna take a while..."                                          
$arrayresult = $array | %{Get-CMSoftwareUpdate -ArticleId $_.Details_Table0_Title -Fast| ?{$_.nummissing -ge 1 -and $_.IsExpired -eq $FALSE -and $_.isSuperseded -eq $FALSE -and $_.LocalizedDisplayName -notlike "*Security Only*"}} | `
                               Select ArticleID,LocalizedDisplayName,NumMissing,NumPresent,IsSuperseded,IsExpired -Unique | Sort-Object -Descending -Property NumMissing

$output = [PSCustomObject] @{
'Message' = "These are all of the Windows Updates that are required but not deplyoyed for All Servers.";
'Title' = "All Production Servers with Maintenanace Window (Security Updates): Required But Not Deployed";
'Color' = "Red";
'Array' = $arrayresult
}

if ($output.Array -ne $NULL){$OutputArrays+=$output}

#Array4
$inputParams = @{
    "CollID"="DGM00085";
    "UpdateClass"="Critical Updates";
    "Vendor"="Microsoft";
}

$array = Get-DMGSCCMSQLReport -inputParams $inputParams `
                               -ReportServerUrl $ReportServerUrl `
                               -ReportPath $ReportPath `
                               -ProviderMachineName $ProviderMachineName `
                               -Sitecode $Sitecode

Set-Location $Sitecode":"                                               
Write-Host "Gonna take a while..."                                          
$arrayresult = $array | %{Get-CMSoftwareUpdate -ArticleId $_.Details_Table0_Title -Fast| ?{$_.nummissing -ge 1 -and $_.IsExpired -eq $FALSE -and $_.isSuperseded -eq $FALSE -and $_.LocalizedDisplayName -notlike "*Security Only*"}} | `
                               Select ArticleID,LocalizedDisplayName,NumMissing,NumPresent,IsSuperseded,IsExpired -Unique | Sort-Object -Descending -Property NumMissing

$output = [PSCustomObject] @{
'Message' = "These are all of the Windows Updates that are required but not deplyoyed for All Servers.";
'Title' = "All Production Servers with Maintenanace Window (Security Updates): Required But Not Deployed";
'Color' = "Red";
'Array' = $arrayresult
}

if ($output.Array -ne $NULL){$OutputArrays+=$output}

#Multiple Arrays
Get-DMGEmailReport `
    -Arrays $OutputArrays `
    -ReportTitle "Updates Required but Not Deployed Report" `
    -from "SCCMSQLReports@corporation.com" `
    -To "c-dmaiolo@corporation.com" `
    -subject "SCCM Report: Required But Not Deployed (Not Superseded, Not Expired, Not Security Only)"