Detailed SCCM Patching Report via Powershell Universal & HTML

This interactive Powershell SCCM Patching Report will show the overall compliance rate in a bar graphic, and then list the all non-compliance machine with detailed failure reasons.

# Powershell SCCM Patching Report with detailed failture reasons
# Rui Qiu
# 9/3/2021

# Setting up parameters
Import-Module dbatools
$sqlserver = 'xxx'
$database = 'xxx'
$siteserver = 'xxx'
$sitecode = 'xxx'
$Session:value = "patchserver"

# Ask user to choose the report
New-UDSingleSelector -Id "stuff"  -options {
	@{ value = "patchserver"; label = "Server Patchlist" },
	@{ value = "internalpatch"; label = "Internal Workspace" },
	@{ value = "externalpatch"; label = "External Workspace" },
	@{ value = "serverbaseline"; label = "Server Baseline" },
	@{ value = "workstationbaseline"; label = "Workstation Baseline" }

New-UDButton -Text "Get Report" -OnClick {
	$UDElement = Get-UDElement -id "stuff"
	Show-UDToast -Message "You have selected $Session:value" -Duration 5000
	Sync-UDElement -Id 'table'        

# Create the patching report table
New-UDDynamic -Id 'table' -Content {
$query = "
SELECT        DeviceName, LastComplianceMessageDesc, LastComplianceMessageTime, LastEnforcementMessageTime, 
                         CASE LastEnforcementErrorCode WHEN '0' THEN 'Success' WHEN '-2016409844' THEN 'Software update execution timeout' WHEN '-2016409966' THEN 'Group policy conflict' WHEN '-2016410008' THEN 'Software update still detected as actionable after apply'
                          WHEN '-2016410012' THEN 'Updates handler job was cancelled' WHEN '-2016410026' THEN 'Updates handler was unable to continue due to some generic internal error' WHEN '-2016410031' THEN 'Post install scan failed'
                          WHEN '-2016410032' THEN 'Pre install scan failed' WHEN '-2016410855' THEN 'Unknown error' WHEN '-2016411012' THEN 'CI documents download timed out' WHEN '-2016411115' THEN 'Item not found' WHEN
                          '-2145107951' THEN 'WUServer policy value is missing in the registry.' WHEN '-2145120257' THEN 'An operation failed due to reasons not covered by another error code.' WHEN '-2145123272' THEN 'There is no route or network connectivity to the endpoint.'
                          WHEN '-2145124320' THEN 'Operation did not complete because there is no logged-on interactive user.' WHEN '-2145124341' THEN 'Operation was cancelled.' WHEN '-2146498304' THEN 'Unknown error' WHEN
                          '-2146762496' THEN 'No signature was present in the subject.' WHEN '-2146889721' THEN 'The hash value is not correct.' WHEN '-2147010798' THEN 'The component store has been corrupted.' WHEN '-2147010815'
                          THEN 'The referenced assembly could not be found.' WHEN '-2147010893' THEN 'The referenced assembly is not installed on your system.' WHEN '-2147018095' THEN 'Transaction support within the specified resource manager is not started or was shut down due to an error.'
                          WHEN '-2147021879' THEN 'The requested operation failed. A system reboot is required to roll back changes made.' WHEN '-2147023436' THEN 'This operation returned because the timeout period expired.' WHEN
                          '-2147023728' THEN 'Element not found.' WHEN '-2147023890' THEN 'The volume for a file has been externally altered so that the opened file is no longer valid.' WHEN '-2147024598' THEN 'Too many posts were made to a semaphore.'
                          WHEN '-2147024784' THEN 'There is not enough space on the disk.' WHEN '-2147217865' THEN 'Unknown error' WHEN '-2147467259' THEN 'Unspecified error' WHEN '-2147467260' THEN 'Operation aborted' ELSE
                          'Pending Reboot' END AS 'LastMessage'
FROM            dbo.vSMS_SUMDeploymentStatusPerAsset
WHERE        (AssignmentName = N`'$Session:value`')

$data = Invoke-DbaQuery -SqlInstance $sqlserver -Database $database -Query $query | ForEach-Object {
        DeviceName = $_.DeviceName 
        LastComplianceMessageDesc = $_.LastComplianceMessageDesc
        LastComplianceMessageTime = $_.LastComplianceMessageTime
        LastEnforcementMessageTime = $_.LastEnforcementMessageTime
        LastMessage = $_.LastMessage

$columns = @(
    New-UDTableColumn -Property DeviceName -Title Name
    New-UDTableColumn -Property LastComplianceMessageDesc -Title Compliance 
    New-UDTableColumn -Property LastComplianceMessageTime -Title ReportTimeatus 
    New-UDTableColumn -Property LastEnforcementMessageTime -Title LastMessageTime 
    New-UDTableColumn -Property LastMessage -Title LastMessage 

$script:Thresholds = @{}
$Thresholds.Good = 90
$Thresholds.Warning =  80

Function Set-PercentageColour {

    If ($Value -gt $Thresholds.Good)
    {$Hex = "#20DE5F" # Green

    If ($Value -lt $Thresholds.Good -and $Value -gt $Thresholds.Warning)
    {$Hex = "#ff9900" # Amber

    If ($Value -lt $Thresholds.Warning)
    {$Hex = "#FF0000" # Red

    Return $Hex

$all = $data.DeviceName.count
$compliant = $data | Where-Object LastComplianceMessageDesc -eq "Compliant" 
$compliant = $compliant.count 
$non_compliant = $data | Where-Object LastComplianceMessageDesc -eq "Non-Compliant"
$non_compliantcount = $non_compliant.count
$compliant_percent = [math]::Round(($compliant * 100 / $all),2)

$sum = @"
<table cellpadding="0" cellspacing="0" width="700">
  <td style="background-color:$(Set-PercentageColour –Value $compliant_percent);padding:10px;color:#ffffff;" width="$compliant_percent%">
    Compliant Rate $compliant_percent%
  <td style="background-color:#eeeeee;padding-top:10px;padding-bottom:10px;color:#333333;" width="$compliant_percent%">
<table cellpadding="0" cellspacing="0" width="700">

New-UDElement -tag 'div' -attributes @{
    style = @{ 
        height = '10px'
New-UDTypography -Text "Report for $Session:value, Total machines: $all, Compliant: $compliant, Non-compliant: $non_compliantcount"
New-UDElement -tag 'div' -attributes @{
    style = @{ 
        height = '20px'
New-UDHtml -Markup $sum
New-UDElement -tag 'div' -attributes @{
    style = @{ 
        height = '20px'

New-UDTable -Data $non_compliant -Columns $columns -Title "Non-compliant Machines Report" -ShowSort -Dense -Export


Leave a Comment