SCCM Monthly Software Update Compliance Reporting

Reporting on software update compliance of workstation 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 I’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 Sofware 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.
Software Update Compliance Report. SQL query.

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

In this article, we’ll aim to create a report that would help us identify patterns of non-compliance for each month, device model, geographic locations, and more.

This SCCM Software Update Report would report compliance for every month for software updates that you’ve deployed for that month. 

The below image is the target report that we’d attempt to create.

Windows Monthly Patch compliance dashboard

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

User display name: This is the full name of the user. 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.

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

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

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.

I like to emphasize that a bit more, Just ONE collection. This collection would contain all Workstation objects (Active and inactive).

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

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.

Download the report by clicking on the button below.

Note: The RDL file is within the attached zip file. RDL report files are XML files so they tend to open within the browser window instead of downloading them.

Also check out our free SCCM task sequence orchestrator for Agile task sequence deployment.

Got any questions? ask in the comment section below.

SQL Report Software Update Compliance Report

Related Articles

SCCM task sequence UI – Set computer name and more during an SCCM task sequence deployment

It is always a unique challenge of having to build an OSD experience that includes providing a great user experience during the deployment of a new operating system.

The attached application would allow you to present a front-end to an active end-user who is executing the SCCM task sequence……

Keywords: SCCM tasksequence UI, SCCM Task Sequence User interface, SCCM task sequence Set computer name.

8 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Pradeep chand
Pradeep chand (@pradeep)
1 month ago

not able to download RDL file

Matheus Alipio
Matheus Alipio (@matheuss)
1 month ago

When executing the report it presents the following error:

Query execution for data set ‘DataSet0’ failed. (rsErrorExecutingCommand)

Matheus Alipio
Matheus Alipio (@matheuss)
Reply to  Topaz George
1 month ago

Thank you for your help,
after filling in the fields of one of the users it worked perfectly.

Todd Christensen
Todd Christensen (@todd)
1 month ago

When I try running the report, I get the same error as Matheus. I’ve changed my dataset, and then I get the following error. I do not have “Division” in my User AD attributes. What are my options?

2020-08-18_10-09-59.png
8
0
Would love your thoughts, please comment.x
()
x