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

# Getting Software Updates Compliance Data
Write-host "Getting Software Updates Overall Compliance Data"
$query = "SELECT CI.AssignmentID `
,CI.AssignmentName `
,Description `
,CollectionID `
,StartTime `
,EnforcementDeadline `
,RebootOutsideOfServiceWindows `
,NumCompliant*100/NumTotal as 'PercentCompliant' `
,NumTotal `
,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 | Out-GridView -Title "Software Updates Overall Compliance per Deployment"

# Close the connection

One Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.