Find the Compliance State for Software Updates on Remote Computers with PowerShell

Since the recent WannaCrypt ransomware attacks, many organisations have wanted to get the patch status of their systems to see if they are protected. Several reports and SQL queries for ConfigMgr were quickly posted online to help enterprises identify at-risk machines. But what for those who don’t use ConfigMgr, or what if you want to get the real-time status for a patch on a particular system instead of relying on deployment results or hardware inventory data?

For this, I wrote a couple of PowerShell scripts that will connect to a remote system, or group of systems, and check the status of any number of patches based on information from WMI. The scripts will use the Win32_QuickFixEngineering class on all targeted systems, as well as the SCCM WMI classes for those that are using ConfigMgr.

There are two scripts, one is single-threaded which is fine if you want to just check the local system, or a couple of remote systems.  If you want to check many remote systems at the same time, then use the multi-threaded version which will give you significantly quicker results.

Both scripts work the same way, but bear in mind the multi-threaded version will use more system resources, especially CPU, so set the throttle-limit (number of simultaneous threads) to something sensible for your CPU. The default limit is 32. The multi-threaded version uses my [BackgroundJob] custom class which will only work in PowerShell 5 +, and you will need to run the code for that class in your PowerShell session first.

To use the scripts, simply pass the computer name/s and Article ID/s (KB number) for the patch.

To run against the local machine:


Get-PatchStatus -ArticleID 4019264

Against a remote machine:


Get-PatchStatus -ComputerName PC001 -ArticleID 4019264

Against several remote machines:


Get-PatchStatus -ComputerName PC001,PC002,PC003,SRV001,SRV002 -ArticleID 4019264

Using several Article IDs


Get-PatchStatus -ComputerName PC001,PC002,PC003 -ArticleID @(4019264,4019265,4016871)

Set the throttle limit on the multi-threaded version, and using verbose output:


Get-PatchStatus -ComputerName $Computers -ArticleID $ArticleIDs -ThrottleLimit 64 -Verbose

Result

result

You might want to output to GridView if you have a lot of results for better filtering.

Note that if no results are returned in the (SCCM) or (QFE) columns then the patch is not installed, or if there was an error connecting to the remote system, this will be returned in the ‘Comments’ column.

The Scripts

Single-threaded version

Multi-threaded version

How to Quickly Get Overall Compliance for ConfigMgr Software Update Deployments

If you have access to your ConfigMgr SQL database, you can quickly get a summary for the overall compliance of software updates deployments using the following PowerShell script, which you can run on your local machine.

It returns the results into the PowerShell GridView, allowing you to filter by deployment name, start time, deadline etc.

Enter your SQL server database and instance name at the top of the script.

Capture


<#

This script gets overall compliance summary data for software updates deployments from the ConfigMgr SQL database

#>

# Database info
$dataSource = “mysqlserver\INST_SCCM”
$database = “CM_ABC”

# Open a connection
cls
Write-host "Opening a connection to '$database' on '$dataSource'"
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Getting Software Updates Compliance Data
Write-host "Getting Software Updates Overall Compliance Data"
$query = "SELECT CI.AssignmentID `
,CI.AssignmentName `
,Description `
,CollectionID `
,StartTime `
,EnforcementDeadline `
,OverrideServiceWindows`
,RebootOutsideOfServiceWindows `
,NumCompliant*100/NumTotal as 'PercentCompliant' `
,NumTotal `
,NumHealthy`
,NumCompliant `
,NumNonCompliant `
,NumUnknown `
,NumFailed `
,NumPending `
,NumHealthyCompliant `
,NumHealthyFailed `
FROM v_UpdateDeploymentClientSummary UDCS `
inner join v_CIAssignment CI on UDCS.AssignmentID = CI.AssignmentID ORDER BY AssignmentName Desc"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)
$table | Out-GridView -Title "Software Updates Overall Compliance per Deployment"


# Close the connection
$connection.Close()