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.

Please note : Workstation report is free to download. Server report can be purchased here.

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.


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


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.


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


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.

Note: The report requires ‘Report Definition Language (RDL)’ schema version 2012 or above. Click to find your version.

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.

Newest Most Voted
Inline Feedbacks
View all comments
Pradeep chand
1 year ago

not able to download RDL file

Matheus Alipio
1 year ago

When executing the report it presents the following error:

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

Matheus Alipio
Reply to  Topaz George
1 year ago

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

Todd Christensen
1 year 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?

Drew Rucinski
1 year ago

When I attempt to change CollectionID the only available option is All Systems. When I right click on the report and click manage under parameters it does not let me edit this option to narrow down to another collection. Is this the expected behavior? It shows as a drop down when I look under parameters not a text field.

Drew Rucinski
Reply to  Drew Rucinski
1 year ago

I was able to edit the query in report builder and change the drop down to action against certain collections in our environment. I can choose them from the drop down now. I am concerned i am not getting any compliance data from SCCM (i created the baseline for July as a test) but tomorrow is another day.

Drew Rucinski
Reply to  Topaz George
1 year ago

Yes data is starting to show up. I appreciate this I think it will be useful for me. I am confused about the Last Scan Time setting. It seems to work best if I set some time in the future (like 8pm tonight)? anything else I get incomplete looking results.

Drew Rucinski
Reply to  Topaz George
1 year ago

Last question. Can this be easily massaged to provide data for server patches as well? or is it a pretty major gut job for that? I am learning as I go along here in report builder but I don’t want to mess up too much if its intensive to make that work.

Drew Rucinski
Reply to  Topaz George
1 year ago

Thank you for the reply with the Server info. I did load it and at least start to play with it. The time to load the report is fine, but I do see many duplicates. I have not come across any obsolete objects as of yet.

Drew Rucinski
Reply to  Topaz George
1 year ago

they do not. I basically get in the report like 5-6 copies of each server. and then under the OS version line they have like every version listed Microsoft Windows 7 Ent, Microsoft Windows 7 Pro, Microsoft Win 7 Ultimiate then finally Server 2008R2 for this particular example.

1 year ago

Hello Dear,

I followed the instructions but no luck. Scan field is not populating the details. Please help urgent. I am testing it only for October and November. Error attached.

Last Scan error report - 1.jpg
Mohammed Z
1 year ago

Hi George ,

Is it possible to add windows 10 version 20h2 in report ?

Rajesh Sharma
Reply to  Mohammed Z
1 year ago

‘Windows 10 2004’ and ‘Windows 10 20H2’ have now been added into the report.

Can you please verify and give us some feedback?

You might have to reset your shared data source.

Mohammed Z
1 year ago

Hi George ,

Any SCCM reporting for Server 2012,2016,2019 compliance report ?

Shalu Gupta
1 year ago

I am getting error while running report ” Invalid Column Name whenChanged0 ”
An error has occurred during report processing.
Query execution failed for dataset ‘dataset0’
” Invalid Column Name whenChanged0 “
Please help

Jen Helton
1 year ago

I have verified the AD data and did a separate report of just the v_R.User view and validated the content is in the database. However, the database shows manager0, department0, and streetaddress0. Where in the Dataset would I make the necessary corrections?

Thanks for the help!

Jen Helton
Reply to  Topaz George
1 year ago

This is occurring without any modification to the Dataset. I re-imported just to be sure. When I run the report, the attached error is happening, so I opened in Report Builder, clicked “Refresh Fields” and I see the column name errors. Thanks for your help.

Jen Helton
Reply to  Topaz George
1 year ago

Yes. All of that was validated. I have full admin rights to the server and the database as well. We are, however, using an older version of SRSS and I had to modify the RDL to support the older version, so not sure if that might be what has messed things up. Based on what I found from other forums, I had to modify the default schema to 2010 instead of 2012 (<Report xmlns:rd=”” xmlns:cl=”” xmlns=””>) and also comment out the <ReportParametersLayout> section in order for the RDL file to import.

Again, thanks for your help.

Jen Helton
Reply to  Topaz George
11 months ago

Sorry for the delay. Yes, it is 2010. I’m working on having our database servers upgraded, but that won’t happen as soon as I’d like. :-/ As stated before, I had to modify your RDL file to change the schema. Otherwise, the report failed to import.

Jen Helton
Reply to  Jen Helton
8 months ago

Finally got SQL updated and re-imported your report. I’m still getting an error.

Jen Helton
Reply to  Topaz George
8 months ago

Basically, we built a new server with Win2019 and SQL 2019 and the latest SSRS. We backed up and restored the databases to the new server. I already had the compliance settings built and those were retained in the migration to a new SQL version. I deleted and re-imported your RDL file and ran the report. It showed the error above. Running the Dataset itself, I get the same error. When I remove the fields from the query, it works fine. Not sure what the issue is with those attributes. :-/

Jen Helton
Reply to  Topaz George
8 months ago

They are all in the discovery and have been since before attempting to use this report. Thanks!

Juvenal Mena
1 year ago

How can i build a report for servers that tells me for each server in % the state of deployed updates.

Mohammed Z
11 months ago

Hi ,any possibly adding the machines IP address and serial Number ?

Joseph Martin
2 months ago

I have created the workstation-Workstation | All collection which shows me 12925 workstations. Your report when i run it off that collection only shows me 11996 workstations. What are the exclusions in the report?

Chad Erato
26 days ago

We don’t use Baselines in patch deployment we use Software Deployment Groups. Can the report be tailored to report status off this factor?

Would love your thoughts, please comment.x

Are you an SCCM administrator?

SCCM Task Sequence deployment orchestrator.

built on best practices, learnings & insights of industry experts.