SCCM Windows Server Software Update Monthly Compliance Report

$0.00

Reporting on software update compliance of Server devices in SCCM brings with it varying levels of complexities. But if you break it all down and start looking for patterns you are on your way to ‘Super compliance’. 

Over the year’s We’ve worked with clients who express the following concerns.

  • We only get compliance of 93% to 95%. Our goal of reaching 99.9% compliance has never happened.
  • There is always a delta of machines that appear with a status of UNKNOWN.
  • We are not clear if the deltas we see each month are the same subset of devices or different.
  • When disabled, withdrawn, or superseded updates get removed from the Software Update Groups, the compliance data for those updates for the previous months get lost.
  • The current default ‘compliance’ reports within SCCM SQL reporting services do not show a monthly break down of software updates.

Share this online on social media and help others in your technical community.

SCCM Software Update Compliance Report

Specs

Category:

To discover and cure, both physicists and neuroscientists look for patterns in a subject and compare the observed patterns with other similar patterns. We look for behavioral patterns in people to judge character. If it looks like a duck, swims like a duck, and quacks like a duck, then it probably is a duck.

Comical as it may sound, the above paragraph bears much meaning in the world of SCCM software update compliance reporting and bridging the gap between compliance, non-compliance, and the UNKNOWN.

If I have to lay down a marker on the compliance state that bears much risk; I’d put my money on the UNKNOWN more so than the non-compliant. ‘a bottle of poison without the label POISON is more dangerous than a bottle with a label that reads POISON.’

We have created a report that would identify patterns of non-compliance for each month.

This version (Server) of  the SCCM compliance report does not have some of the columns included in the workstation compliance report (free download here). Columns like LoginID, Email, Manager, Department, Division, Street address, Disk capacity, and free space are not included in the server report. 

The server report will look similar to the image on the left.

Before we dive deep into the process of setting up the report, let me explain a few of the report parameters.

Year: The report server displays the current year by default. You can choose a different year and click the ‘View Report’ button to see the compliance for the selected year. You can add more years to the drop down by editing the report.

Target collection: ‘All Systems‘ and collections starting with ‘report-server-‘  (for server report) and  report-workstation-‘  (for workstation report) are displayed in the drop-down menu. But again, this can be changed by editing the report.

System name: The name of the device you would like to query. Use ‘*’ as a wildcard character.

Last scan time: Choosing a date here would limit the result to only the devices that had the final compliance status date/time older than the selected date.

All of the other parameters let you filter the report based on compliance responses for every month. For example, show entries that are compliant for January but not compliant for March.

Let me bring your focus temporarily to the blocks of GREEN, GRAY, and RED. I like to explain the meaning behind the colors and the patterns of colors.

GREEN – Device has a known status and is compliant with a value of 1.

GRAY – Device has an unknown status and is not compliant with a value of 0.

RED – Device has a known status and is not compliant with a value of 0.

Devices that are displaying consecutive GRAY or RED would need attention. Let me emphasize it again; look for patterns.

Devices show consecutive RED under the following circumstances.

  • Local group policy corrupted.
  • Software Update evaluation portion of the SCCM client is not functional.
  • The SCCM service requires a restart.

Devices show consecutive GRAY under the following circumstances.

  • The device has not yet received and evaluated the baseline.
  • The client has been offline.
  • The SCCM client might be wholly broken.

Below are some of the prerequisites.

Most of the below Active Directory user attributes are used by the ‘Workstation’ report.

Additional Active Directory user attributes.

The report gets the following information from Active Directory. So Active Directory ‘User Discovery’ should be enabled, and the following user attributes should SYNC into SCCM. Besides, the object attributes are discovered by default. Add the following attributes.

  • division.
  • department.
  • st.
  • manager.
  • streetAddress

Note: For the report to work, at least one AD user object in your AD schema should have all of the above attributes and their values set; the database tables for these attributes are not otherwise created in the SCCM database. While grappling with software update compliance, it is best practice to have the user information handily available in the report, so it is best practice to have this information in AD. Run a Full discovery after adding new attributes.

Software Update Compliance Report - User attributes
Windows Monthly Patch compliance dashboard system attributes

Additional Active Directory system attributes.

The report gets the following information from Active Directory. So Active Directory ‘System Discovery’ should be enabled, and the following System attributes should SYNC into SCCM. Besides, the object attributes discovered by default. Add the following attributes.

  • whenChanged (see below section).
  • whenCreated (see below section).
  • lastLogon.

If for a particular Device a few attributes are not available, the report would show a NULL value. While grappling with software update compliance, it is best practice to have device information handily available in the report. Run a Full discovery after adding new attributes.

whenChanged and whenCreated Active Directory SYSTEM attributes

The whenChanged and whenCreated AD attributes are not available in SCCM and thus not discovered by default from Active Directory. 

Create ‘Custom’ entries under ‘Active Directory System Discovery’ in the SCCM console for the whenChanged and the whenCreated Active directory attributes. Run a Full discovery after adding new attributes.

whenCreated and whenChanged SCCM AD attributes

Hardware Inventory Computer System Extended WMI class. [For workstation report only]

Devices manufactured by Lenovo have a unique string set for their ‘Model’ attribute in WMI. This string is not descriptive. For Lenovo devices, the Extended computer system WMI class has a more human-readable value. If not already, add an extra class to your Hardware Inventory.

Software Update Compliance Report - Computer System Ex
SCCM Software Update Compliance Report - Hardware Inventory
SCCM Lenovo get Model

Okay, now that we’ve met the prerequisites, its time to move into the building blocks of the report.

The report contains the following components.

  • Compliance baselines: There would be a baseline for each month and year.
  • An SSRS report: SQL server reporting services report.
  • A deployment: There would be a baseline deployment to a specific collection.

Compliance baselines:

There is one for Configuration baseline for every month of every year; the Configuration baseline would contain a list of Software Updates from WSUS. Every device would analyze and report compliance for the Software Updates listed within the Configuration baseline.

The naming convention for the configuration baselines would take the below format.

[Year]-[Month]-WINMONTHLYCR

The list of configuration baselines for the Year 2020 would look like the below.

  • 2020-April-WINMONTHLYCR
  • 2020-August-WINMONTHLYCR
  • 2020-December-WINMONTHLYCR
  • 2020-February-WINMONTHLYCR
  • 2020-January-WINMONTHLYCR
  • 2020-July-WINMONTHLYCR
  • 2020-June-WINMONTHLYCR
  • 2020-March-WINMONTHLYCR
  • 2020-May-WINMONTHLYCR
  • 2020-November-WINMONTHLYCR
  • 2020-October-WINMONTHLYCR
  • 2020-September-WINMONTHLYCR

Add all the updates that were deployed to your workstation and Server fleet for that month. This also includes your Office365 updates.

‘2020’ is just used as an example. The attached report has 2020 – 2024 as options. More years can be added by editing the report.

osd365-Windows-Monthly-Patch-compliance-dashboard-12-CBL

The below PowerShell script automates the following.

  • Create the Compliance baseline.
  • Add Software updates for a specified month from a specific Software Update Group.
				
					$SiteCode = "PNZ"
$ProviderMachineName = "YourSCCMServer"
$initParams = @{}
$baseLineFolderLocation = '.\ConfigurationBaseline\Servers\Software Updates'
$SCCMSoftwareUpdateGroupName = "Software Updates - Auto Windows Server Monthly Updates"

if((Get-Module ConfigurationManager) -eq $null) {
    Import-Module "$($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1" @initParams 
}
if((Get-PSDrive -Name $SiteCode -PSProvider CMSite -ErrorAction SilentlyContinue) -eq $null) {
    New-PSDrive -Name $SiteCode -PSProvider CMSite -Root $ProviderMachineName @initParams
}
Set-Location "$($SiteCode):\" @initParams
clear
$strYear = Read-Host -Prompt "Input 4 digit year. 2020 or 2021 or 2022 etc "
Write-Host "" 
Write-Host "***********************************************************"
Write-Host "" 
Write-Host "January"
Write-Host "February"
Write-Host "March"
Write-Host "April"
Write-Host "May"
Write-Host "June"
Write-Host "July"
Write-Host "August"
Write-Host "September"
Write-Host "October"
Write-Host "November"
Write-Host "December"
Write-Host "" 
Write-Host "***********************************************************"
Write-Host "" 
$strMonth = Read-Host -Prompt "Copy and paste one of the above listed months for which you want to create a Baseline "
$strBLName = "$strYear-$strMonth-WINMONTHLYCR"
if (Get-CMBaseline -Name $strBLName -ErrorAction SilentlyContinue) {
Write-Host "" 
Write-Host "Baseline already exist. Please remove baseline using the Configuration Manager Console."
Write-Host ""
Exit
}
New-CMBaseline -Name $strBLName
$updates = Get-CMSoftwareUpdate -UpdateGroupName $SCCMSoftwareUpdateGroupName -Fast | Where-Object { $_.IsDeployable -eq $true -and $_.IsDeployed -eq $true -and $_.IsLatest -eq $true -and $_.IsEnabled -eq $true -and $_.IsExpired -eq $false `
-and $_.IsHidden -eq $false -and $_.IsQuarantined -eq $false -and $_.IsSuperseded -eq $false -and ($_.DateRevised).Year -eq $strYear -and ($($_.DateRevised).ToString("MMMM").ToLower() -eq $strMonth.ToLower())} 

foreach ($update in $updates) {

Set-CMBaseline -Name $strBLName -AddSoftwareUpdate $update.CI_ID -ErrorAction SilentlyContinue

}
Get-CMBaseline -Name $strBLName|Move-CMObject -FolderPath $baseLineFolderLocation

				
			

Create Configuration baselines in the console ‘Assets and Compliance/Compliance Settings/Configuration Baselines’ node.

After creating a few configuration baselines the list would look similar to the image on the left. Its time to deploy the configuration baselines to an SCCM collection. All deployments should target just 1 collection.

This collection would contain all SCCM device objects (Active and inactive)(Server and Workstation)

Download the RDL file and import it into your preferred SSRS report location. Change the data source to that of your organization.

After importing the RDL, the report would default to the “All Systems” collection. To change the default value, open the report using “Report builder” or using the ‘Manage’ option of the report URL to change the default value. The value would be the CollectionID corresponding to the collection you want as the default collection.

osd365-Windows-Monthly-Patch-compliance-dashboard-13-default-collection

[For workstation report only] If ‘C:‘ is not the SYSTEM drive, change the hidden parameter ‘sysdrive‘ that is set within the report. You could either use the above method or use the ‘Report Builder’ application to change parameter default values.

Software Update Compliance Report - Report builder

You have to set the report ‘datasource‘ to that of your organization. This can either be done using SSRS ‘report builder‘ or by going to the report URL.

The attached screenshot visually guides you through the process of setting the report ‘datasource‘ through the reports URL.

In addition to the above action, verify if you have select permissions to all the ‘SQL Views’ referenced in the report (Only if you see errors). 

osd365-Windows-Monthly-Patch-compliance-dashboard-qna-15.png
osd365-Windows-Monthly-Patch-compliance-dashboard-qna-16.png
osd365-Windows-Monthly-Patch-compliance-dashboard-qna-17.png

How does this report help increase software update compliance?

After you configure the necessary parameters and deploy all the configuration baselines start looking for patterns in the report. The following are some of the patterns to analyze.

Assumption: The current Month is July 2020.

An all RED line, 2 or more RED blocks in a line: Such a pattern shows that the device has received the configuration baseline but has either not yet run the baseline(s). It could also mean that one or more constituent software updates within those configuration baselines are non-compliant. I’ve seen ‘Local Group Policy’ object corruption indirectly hindering the successful evaluation of Software Updates, leading to configuration baseline evaluation failures.

An all GRAY line, 2 or more GRAY blocks in a line: Such a pattern shows that the device has not received the configuration baseline, the machine does not have a working SCCM client, or the machine has not been online. DNS issues and other WMI issues could also lead to consecutive GRAY blocks.

Coming months, in this case, August 2020 – December 2020 would be GRAY until the 1 day of August 2020.

After deploying the configuration baselines and gathering data from all devices, you’ll find that the Compliant, Non-compliant, No Data figures would be similar to previous months. You’ll also find non-compliance based on geography or device models, or business units.

For the environment represented by the image on the right, the maximum compliance that can can achieved (With the current client health state) is on average 90%. The Compliant, Non Compliant and No Data numbers are more or less consistent too.