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.

<#

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