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.

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.

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.

Responses

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

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

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

        Refer to the prerequisite portion of this article.

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

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

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

      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.

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

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

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

      1. Rest assured, you will start seeing results soon.
        The compliance baseline will run on the machine in this order.

        1. The device gets the baseline after receiving a machine policy.
        2. The device would run the baseline as per you schedule settings on the baseline.
        3. The device would feed the results of the baseline back to the Management Point sometime during the next update cycle.
        4. The SCCM dataloader would summarize and load the data into the SCCM database.

        In short, the process would take between 60 mins to a few days to get the data for all your devices.

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

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

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

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

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

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

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

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

    1. Have you added the ‘whenChanged0‘ active directory system attribute into SCCM? Please refer to the ‘Additional Active Directory system attributes‘ section in the article.

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

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

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

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

          • Check datasource entry (See Image).
          • Also check permissions by opening the dataset (See Image).
          1. 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.

          2. Can you please confirm your Report Definition Language (RDL) version. I guess from your comment above it is 2010.

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

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

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

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

Are you an SCCM administrator?

SCCM Task Sequence deployment orchestrator.

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

DCOM hardening issue.

This application fails to authenticate with WMI on the SCCM server because Microsoft has not yet hardened DCOM on their Windows Preinstallation Environment. We are working on a different approach, but it will only be released during the first quarter of 2024. But until that time, the only workaround will be to uninstall the update corresponding to KB5004442.