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: Most up-to-date reports are found here.
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.
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.
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.
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.
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.
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.
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.
not able to download RDL file
Pradeep, Can you please try downloading the file again. The original file was an RDL file but I’ve now uploaded a Zip which has the RDL within it. Good luck.
When executing the report it presents the following error:
Query execution for data set ‘DataSet0’ failed. (rsErrorExecutingCommand)
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.
@matheuss If the above response did not help your case; for the report to work, at least one AD user object in your AD schema should have all of the below attributes and their values set; the database tables for these attributes are not otherwise created in the SCCM database.
Refer to the prerequisite portion of this article.
Thank you for your help,
after filling in the fields of one of the users it worked perfectly.
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?
For the report to work, at least one AD user object in your AD schema should have all of the below attributes and their values set; the database tables for these attributes are not otherwise created in the SCCM database.
Add the above ‘User’ attributes to your SCCM user discovery. See image below or refer to the prerequisite portion of this article.
Shout out if you need more help.
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.
We’re glad you reached out. For using the report without editing the report in itself; Try using Collections that start with the text ‘report-‘
All collections whose names begin with ‘report-‘ will be automatically displayed in the dropdown.
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.
Rest assured, you will start seeing results soon.
The compliance baseline will run on the machine in this order.
In short, the process would take between 60 mins to a few days to get the data for all your devices.
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.
The last scan time correlates with the last time a compliance statistic was received for a device and SCCM has not received any data for the device since that day. This helps troubleshoot client health issues. Some devices appears to be working but consistently fail to send status back to the management point.
By default the report opens with today as the last scan date. The problem here seems to be the time if used across different time zones; I’ll get my team to look at this for the next release.
Also check of the @cLastScan parameter is set to ‘Date/Time’ and not ‘Text’.
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.
We use this report internally. https://sccmtspsi.com/monthly-compliance-reporting-dashboard-3/
This will report statistics both for Servers and Desktops. Let me know how this report works for you.
Testing to be done in the following scenarios.
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.
Thanks Drew. Can you confirm if the duplicates appear on your SCCM console too?
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.
Thanks Drew. I’ll ask my team to look at the server report. Hope the report is working well for the desktops.
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.
Have the baselines run on the devices? Depending on your configuration; it might take sometime for the results to show on the dashboard.
Hi George ,
Is it possible to add windows 10 version 20h2 in report ?
@apsylog ‘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.
Thanks for your quick action Rajesh. I’ve got a request to create a Server compliance report from one of our clients in New Zealand.
I’ll talk to you about it in detail when I see you on Tuesday.
Hi George ,
Any SCCM reporting for Server 2012,2016,2019 compliance report ?
Thanks for your question, Mohammed. At OSD365 we specialize in end-user computing so we do not at this time have a report similar to the one currently published online for Severs.
I am happy to pass this request along to my Server management team requesting them to make some amendments to the report.
Watch this space.
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
Have you added the ‘whenChanged0‘ active directory system attribute into SCCM? Please refer to the ‘Additional Active Directory system attributes‘ section in the article.
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 the report uses sub-query refactoring and uses custom names like ‘manager’, ‘department’ and ‘streetAddress’ without the ‘0’ at the end.
If you have already made changes to the report fields, I recommend you revert to the original report file.
After the initial setup (as per the above article) there is no need to make any changes to the datasets within the report.
We’ve never had issues with the dataset queries within the report. Hope that helps.
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 thx for that. Have you verified the data source connection? The error seem to point at security configurations specific to ‘Select’ permissions on your SQL DB.
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=”http://schemas.microsoft.com/SQLServer/reporting/reportdesigner” xmlns:cl=”http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition” xmlns=”http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition”>) and also comment out the <ReportParametersLayout> section in order for the RDL file to import.
Again, thanks for your help.
Can you please confirm your Report Definition Language (RDL) version. I guess from your comment above it is 2010.
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.
Finally got SQL updated and re-imported your report. I’m still getting an error.
Not being aware of the technical details; I do not know what to recommend.
But I guess it’s fair to ask the following questions.
Have you add the extra attributes into SCCM. See the image below and documentation in the article.
Did you run the dataset query separately? See images in my initial response.
Did you also reinstall reporting services after upgrading SQL server?
Hello,
How can i build a report for servers that tells me for each server in % the state of deployed updates.
@mercurio we do have a server version of the report but it is offered for free only to our enterprise customers.
The server report is offered for sale for public downloads. But before customers purchase the ‘server’ version they have verify if the ‘Workstation’ report is fully functional in their environment.
Shout out if you want a link to purchase the ‘Server’ version of the report.
Hi ,any possibly adding the machines IP address and serial Number ?
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?
Team,
We don’t use Baselines in patch deployment we use Software Deployment Groups. Can the report be tailored to report status off this factor?
The baselines in the report are only used as a measure; it is not used for deployment. Use the powershell script in bottom of this page to create the the baslines. https://sccmtspsi.com/product/sccm-windows-server-software-update-monthly-compliance-report/