SCCM: Required Updates Report

Overview

A build 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, I was able to help automate the process of detecting these updates and reinjecting 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

Then 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"}

were you choose your 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 I chose to also have the results first emailed to me utilizing my HTML Email Function. This generates the following email, indicating which updates were included:

required but not deployed

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)"

Leave a Comment

Your email address will not be published.