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"
$Session:value=$UDElement.Attributes.selectedOption.value
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 {
param(
[int]$Value
)
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">
<tr>
<td style="background-color:$(Set-PercentageColour –Value $compliant_percent);padding:10px;color:#ffffff;" width="$compliant_percent%">
Compliant Rate $compliant_percent%
</td>
<td style="background-color:#eeeeee;padding-top:10px;padding-bottom:10px;color:#333333;" width="$compliant_percent%">
</td>
</tr>
</table>
<table cellpadding="0" cellspacing="0" width="700">
</table>
"@
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
}