Mastering Configuration Manager Patch Compliance Reporting

[jonasoh]

Hi, Jonas here!

Or as we say in the north of Germany: "Moin Moin!"
I am a Microsoft Customer Engineer (CE formerly known as PFE) and a while back (years in fact) I was asked to analyze the update compliance status of a SCCM/ConfigMgr/MECM environment. I will use the current name: "Microsoft Endpoint Configuration Manager" (MECM) in the rest of the blog.
I used different reports to look for clients not installing the necessary updates, but it was time consuming and I was missing a general overview with some meaningful KPIs. I ended up with a comprehensive SQL query and an Excel sheet, but changed that to a SQL Server Reporting Services (SSRS) report. I then made that available to several departments in the organization.
As mentioned before, it's been a while since I created the report. If I were to start now it would be a PowerBI version or I would simply grab one of the PowerBI reports available from the MECM community. But since I still use the report and find it quite helpful, I decided to share that with the rest of the world.

Updated version (v4.0)

I updated the solution again. Use the table of contents to jump to the summary of changes directly if you want.

I also created the following article: ConfigMgr Windows Update Compliance Reporting FAQ as an addition to this one. 

Table of contents:

TR:DR
The report explained
1st sub-reports (list of systems)
2nd sub-report (per system)

3rd sub-report (errorlist)

4th sub-report (deployments per system)

5th sub-Report (updates approved)
Some key facts and prerequisites:
How to install
Script Parameters
Script Examples

Activate report data caching
Additional report
Disclaimer
Resources
Changes

TL;DR

The following report should help you identify update problems within a specific collection and a group of systems and is designed to work well for a few thousand clients. The query might run longer in bigger environments and you might need to improve it or run it not within business hours to show results.
The installation guide for the custom update reporting can be found at the end of this post but you should at least start with the "Some key facts and prerequisites" section.
If you're just looking for the SQL statement behind the report, copy the query from the "UpdatesSummary.sql" file and use it in SQL directly.
The whole solution can be found on GitHub: https://github.com/jonasatgit/updatereporting
Feel free to improve the SQL queries or some of the reports on Github.
If you are a Premier customer and if you are looking for a more complete reporting solution, ask your Microsoft contact about the following offerings:
"Microsoft Endpoint Configuration Manager: Advanced Dashboards"
"Microsoft Endpoint Manager: PowerBI Dashboard Integration"

"Microsoft Endpoint Manager: Update Compliance Dashboard"

"Modern Workplace Compliance Reporting"

The report explained:

The main report dashboard looks like this:

Figure 1: Main update compliance dashboard

I used different KPIs to measure update compliance and the report combines all that into one dashboard. The main KPI is the first bar and all the others should simply help identify patch problems or flaws in your deployment strategy.

The report also has five sub-reports. One to show you a list of systems in a specific state (1st sub-report). One for a list of missing updates for a single system (2nd sub-report).  One sub-report (3rd sub-report) to show you a list of update installation errors or WSUS scan errors. One sub-report for all update deployment-states per system (deployments per system) and the last report (Updates Approved) shows you the NOT deployed / approved updates a system is still missing.

Figure 2: Simplified view of report dependencies

1^st sub-reports (list of systems)

Almost each bar or pie chart links to a sub-report to show compliance state of that subset of systems to give you better visibility. This is an example of all the uncompliant systems from the first bar in the dashboard and is basically what the Excel list looked like in the early days:

Figure 3: List of all uncompliant systems

If you click on different bar or pie charts, the same sub-report will be opened, but the data will be filtered depending on which bar or pie chart you clicked.

2^nd sub-report (per system)

If you click on a system name a system specific report will open. The list of updates of that report will be filtered for the specific need. For example, if you click on “missing updates approved” just the approved and missing updates will be visible. The report also shows installations errors if any happened. Each error will link to a Bing search with the hex value of the error. The search string looks like this: https://www.bing.com/search?q=error+0x80070005
The report is basically a copy of one of the default MECM reports with some adjustments and some custom filters to filter for “Deployed and missing updates”, “Not deployed but missing”, "Missing updates with errors", “All missing updates deployed or not” or just “All updates per device.

It also contains update collections and maintenance windows to help identify missing or wrong collection memberships:

Figure 4: Per device update compliance report

3^rd sub-report (errorlist)

If you either click on the “WSUS-Scan Error” or “Systems with install error” bar a list with errors will be visible as shown in the screenshot below:

Figure 5: List of WSUS scan errors

The report contains around 400 possible WSUS errors, tips and links to give you a troubleshooting starting point. It might not be a complete list, but it should cover the most common errors.

4th sub-report (deployments per system)

The fourth sub-report is a simple list of update deployments and their state and some other useful information per system. 

It can be either be opened directly or if you click on the "Deployments non compliant" row of the first sub-report.

Figure 6: Update deployment-states per device

5th sub-report (updates approved)

If you click on the yellow “Updates Approved” bar a report shows you all the updates the selected systems are still missing, which are NOT yet approved / deployed.

The report should help identify problems with the selection of updates in your deployments.

Figure 7: Report of missing updates not yet approved

Some key facts and prerequisites:

• The report is made to show the update compliance status of members of a collection or multiple collections no matter what type of systems are a member or which or how many updates are deployed to each individual system. So, it can be a combined view over multiple deployments and not just one deployment.
• The report combines multiple deployments into one single view.
  If you have a simple group of systems and deploy every necessary update with one deployment, the deployment status might be enough, but if you have a more complex setup, you might want to see details based on a specific group of systems no matter if, how or how many updates are deployed to each system.
• I have defined "compliant" to be a system which has:
  • all enabled update deployments set to compliant (see changes down below. Column: "Deployments Non Compliant")
  • the last security update installation in Win32_Quickfixengineering was in the current month (or the monthly security rollup. Column: "Month Since Last Update Install")
  • The last or current security rollup is installed (see changes down below. Column: "Last Rollup Status")
• The report will all updates no matter if deployed as "available" or "required".
• The report consists of multiple KPIs to indicate the update compliance or update/client health state and should give you an overview from different viewpoints to help identify problematic systems or a flaw in your patch strategy.
• The report will use data from WMI class Win32_QuickFixEngineering which needs to be enabled in hardware inventory client settings. The class is only used to determine the latest or last cumulative update or A security update installation to identify systems which seem to be fine but actually have never installed anything. "HotFixID" and "InstalledOn" are the only required fields. 
• The report also uses LastLogonTimeStamp from AD System Discovery to visually show systems which have not logged on to the domain in a while and which might be disposed of already and could be deleted from the MECM database. If you don't use AD system discovery the report will show all systems of the specified collection as not compliant in the pie chart "Last ADDS logon" .
  • AD system discovery is no hard requirement to run the report
• The report does not show historical data and will always show the current status. So, if you change a deployment in the middle of the month, the compliance percentage will drop almost immediately as it does with the deployment in MECM.
• The report has multiple sub-reports to drill further down and almost each report will use the same dataset
  • Read more about data caching down below
• The SQL query of the dataset is made to filter out Defender Update Deployments, because they normally will be changed every x hours and could interfere with the overall compliance state and should be monitored with other reports.
  You can change that via the available report parameters.  
• The 2nd Level sub-report per system will also show Defender updates, even if they are filtered out on the dashboard
• The SQL query might run longer in bigger environments depending on SQL performance and SQL maintenance. In some cases one of the “ForceLegacyCardinality” parameters can help to speed up the queries. (See parameter description down below)
• There are several sub-reports with the same look and feel, because it was simpler to copy the report and just change the filter for the specific need.
• Most sub-reports will be hidden in SSRS to avoid direct use and keep the folder as clean as possible.
• The reports are made on SSRS 2017 but can be converted to the older format using the "ForceLegacyFormat" parameter.
• The reports can be used on PowerBI Reporting Services if you upload them manually. Start with the “rsd” files from the “work” folder to avoid any errors. 

 

How to install:

1. In case you used an older version of the solution before, delete and re-create the report folder in SSRS (or create a new one) and run the latest script again.
2. Make sure you have Win32_Quickfixengineering enabled in the client settings for hardware inventory.  "HotFixID" and "InstalledOn" are the only required fields. 
3. You could also use AD System Discovery to have further data, but that's no hard requirement.
4. Either clone the repository or download the whole content.
5. Copy the whole content to the SQL Server Reporting Services Server (SSRS)
6. Create a new folder on the report server website where the reports should be imported to.
   1. The folder should be under the normal MECM folder (normally called ConfigMgr_[SITECODE]), but it can also be at the root level of your Reporting Services Server. But keep in mind that report subscriptions are only visible in the MECM console, if the report, you have subscribed for, is below the normal MECM folder. The subscription will not be visible in the MECM console if the report was placed at the root level.
7. Start a PowerShell session as admin.
   1. The user running PowerShell also needs to have admin rights on the SQL Reporting Services Server to upload the reports
8. Change the directory to the folder were the import script "Import-SSRSReports.ps1" can be found.
9. Start the script ".\Import-SSRSReports.ps1" with the appropriate parameters (see below or run “Get-Help .\Import-SSRSReports.ps1 -Full”)
   1. The script will copy each RDL and RSD file from the "Sourcefiles" folder to a new "work" folder in the same directory the script resides.
   2. The script will then simply replace some values with the parameter values you provided
   3. The script will then upload the datasets and the reports to the server and the folder you provided as parameters
   4. The files in the "work" folder will not be deleted and can be used as a backup or for manual uploads if necessary and will contain the data you provided as parameters to the script

   5. IMPORTANT: If you need to re-run the script, use the -TryOverwrite parameter first and if it does not work as expected delete the SSRS website folder with all of its content. I faced some issues with some settings not being overwritten hence the name of the parameter "Try"Overwrite.

 

Script parameters:

Parameter	Required	Example value / Description
ReportServerURI	Yes	Value: http://reportserver.domain.local/reportserver Description: The URL of the SQL Reporting Services Server. Can be found in the MECM Console under "\Monitoring\Overview\Reporting" -> "Report Server" or in the "Report Server Configuration Manager" under "Web service URL"
TargetFolderPath	Yes	Value: ConfigMgr_P11/Custom_UpdateReporting Description: The folder where the reports should be placed in. I created a folder called "Custom_UpdateReporting" below the default MECM reporting folder. My sitecode is P11, so the default folder is called "ConfigMgr_P11". Like this for example: "ConfigMgr_P11/Custom_UpdateReporting" IMPORTANT: Use '/' instead of '' because it's a website.
TargetDataSourcePath	Yes	Value: ConfigMgr_P11/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602} Description: The path should point to the default ConfigMgr/MECM data source. In my case the Sitecode is P11 and the default data source is therefore in the folder "ConfigMgr_P11" and has the ID "{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}" The path with the default folder is required. Like this for example: "ConfigMgr_P11/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}" IMPORTANT: Use '/' instead of '\' because it's a website.
DefaultCollectionID	No	Value: SMS00001 Description: The report can show data of a default collection when it will be run, so that you don't need to provide a collection name each time you run the report. The default value is "SMS00001" which is the CollectionID of "All Systems", which might not be the best choice for bigger environments.
DefaultCollectionFilter	No	Value: All% Description: The filter is used to find the collection you are interested in and the value needs to match the name of the collection you choose to be the default collection for the parameter "defaultCollection". In my case "S%" or “Servers%” or "Servers of%" to get the "Servers of the environment" collection for example.
DoNotHideReports	No	Value: 'Software Updates Compliance - Overview','Compare Update Compliance' Description: Array of reports which should not be set to hidden. You should not use the parameter unless you really want more reports to be visible.
DoNotUpload	No	Description: Switch parameter to not directly upload the reports to SRRS. That might be helpful if you do not have the rights to upload and need to give the files to another person for example. In that case, just use the report files in the work folder and upload them manually. (Was called "Upload" in the first script version)
ReportSourcePath	No	Value: "C:\Temp\Reports" Description: The script will use the script root path to look for a folder called "Sourcefiles" and will copy all the report files from there. But you could also provide a different path where the script should look for a "Sourcefiles" folder
ForceLegacyFormat	No	Description: Switch parameter to change the xml definition  of each report to the older pre SSRS  2016 format. That way the reports also  work with SSRS 2014 for example.
ForceLegacyCardinalitySQL2016SP1AndHigher	No	Description: Switch parameter to change some SQL queries to use legacy cardinality (More information can be found in the sources list below) IMPORTANT: Can only be used if the SQL Server hosting the MECM database is at least running SQL Server 2016 SP1.
ForceLegacyCardinalityOlderThanSQL2016SP1	No	Description: Switch parameter to change some SQL queries to use legacy cardinality. More information can be found here. IMPORTANT: Can only be used if the SQL Server hosting the MECM database is OLDER than 2016 SP1.
TryOverwrite	No	If set, the script will try to overwrite existing reports. In some cases not all settings are overwritten unfortunately, hence the name TRY-Overwrite

Script examples:

Get the full list of parameters by running:

Get-Help .\Import-SSRSReports.ps1 -Full

Upload all reports with the minimum required parameters

.\Import-SSRSReports.ps1 -ReportServerURI "http://reportserver.domain.local/reportserver" -TargetFolderPath "ConfigMgr_P11/Custom_UpdateReporting" -TargetDataSourcePath "ConfigMgr_P11/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}"

Upload all reports with the minimum required parameters and force legacy format

.\Import-SSRSReports.ps1 -ReportServerURI "http://reportserver.domain.local/reportserver" -TargetFolderPath "ConfigMgr_P11/Custom_UpdateReporting" -TargetDataSourcePath "ConfigMgr_P11/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}" -ForceLegacyFormat

Just change the report files and do not upload them

.\Import-SSRSReports.ps1 -ReportServerURI "http://reportserver.domain.local/reportserver" -TargetFolderPath "ConfigMgr_P11/Custom_UpdateReporting" -TargetDataSourcePath "ConfigMgr_P11/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}" -DoNotUpload

Upload all reports and change the default CollectionID and Collection-Filter

.\Import-SSRSReports.ps1 -ReportServerURI "http://reportserver.domain.local/reportserver" -TargetFolderPath "ConfigMgr_P11/Custom_UpdateReporting" -TargetDataSourcePath   "ConfigMgr_P11/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}" -DefaultCollectionID "P1100012" -DefaultCollectionFilter "All Servers of Contoso%"

Activate report data caching:

Activate report data caching to increase report render speed. As mentioned before almost every report retrieves data from the same dataset. By activating report data caching you can increase report render speed especially for the compliance sub-reports (like the first sub-report). 

First you need to show all hidden items of your report folder by clicking on "Tiles" and "Show hidden items".

Figure 8: Show hidden items

Choose the "UpdatesSummary" dataset and click on the three dots "..." and choose "Manage".

Click on "Caching" and "Cache copies of this dataset and use them when available" and "Apply".

Figure 9: Activate data source caching

 

Additional report:

Also, a while ago I created a report to compare the patch status of a maximum of six systems which will also be upload to your SSRS if you run the install script. It should just help to have a fast and simple way to spot differences.
The report has a filter to limit the amount of systems returned by name and you can choose a maximum of six systems to compare them. You could also choose to only view required updates to limit the view and complexity of the report.

Figure 10: Compare update compliance report

I hope you like the report solution and I hope it is a good extension of what you are using right now. 

Stay safe and healthy!

Best regards

Jonas Ohmsen

Senior Customer Engineer - Microsoft Germany

Disclaimer:

This posting is provided "AS IS" with no warranties, and confers no rights

Resources:

Download the whole solution or clone the repository here:
https://github.com/jonasatgit/updatereporting

Other blogs:

https://aka.ms/JonasOhmsenBlogs

https://aka.ms/StefanRoellBlogs

Slow ConfigMgr console:

https://support.microsoft.com/en-us/help/3196320/sql-query-times-out-or-console-slow-on-certain-conf...

Addition FAQ blog post:

ConfigMgr Windows Update Compliance Reporting FAQ

Changes:

(The version number can be found in the lower left corner of the dashboard. No version number means v1.0)

2023-11-10 v4.0:

• Added more error solutions

2022-10-12 v3.9:

• Added logic to hide zero values in diagrams based on feedback
• Fixed minor issues

2022-05-06 v3.8:

• Fixed a sub-report link problem for WSUS error and install error list

2022-04-04 v3.7:

• Added parameter to show data based on current or previous month. The previous month setting is only applicable if updates are deployed with a month delay and does not rely on historical data
• Changed the way compliance for update rollups are shown between first day of month and second Tuesday based on: https://github.com/jonasatgit/updatereporting/pull/11
• Changed the "exclude future deployments" parameter to be able to filter out deployments in one of the following states: Deployed as available, deployment disabled, start time or deadline in the future
• Changed column "Missing updated approved" to only show missing updates if the corresponding deployment has not been filtered out via the new exclude parameter. The "per device" report still shows all updates no matter the deployment selection
• Added the new deployment exclude parameter also to the "Per device deployments" report. The report will now exclude deployments based on the parameter.
• Added cumulative update prefix like "2022-04" to the dashboard for each rollup bar graph
• Added systems domain name column to each list report
• Added switch "TryOverwrite" to import script. If set, the script will try to overwrite existing report items. Might not work in every case. If successful subscriptions will also be kept.
• Added new filter to the "Per device" report called: "All missing Security and Critical updates deployed or not"
• Removed Security Update requirement in QFE query to improve QFE detection accuracy
• Fixed typo in "Per device" report based on: https://github.com/jonasatgit/updatereporting/issues/9
• Fixes typo in import script
• Fixed sorting issue in "per device" report
• Fixed sorting issue in "per device deployments" report
• Fixed "uncompliant" typo in "compliance list" report via: https://github.com/jonasatgit/updatereporting/pull/15
• Fixed typo in "compare update compliance" via: https://github.com/jonasatgit/updatereporting/pull/14
• Fixed an issue with parameters not correctly handled between the dashboard and most of the sub-reports

2021-07-02 v3.6:

• Added "Cumulative Update for Microsoft server operating system" string for server 2022 updates to main SQL query

2021-07-02 v3.5:

• Changed the overall compliance state from "all approved and missing updates" + "a security update installation happend within one month" to "All deployments are compliant" + "either the last or the current cumulative update is installed" + "a security update installation happend within one month" (based on Win32_QuickFixEngineering)
• Added help text to all report column headers
• Added Update install errors bar graph to dashboard (below WSUS scan errors)
• Changed filter for top 10 systems on dashboard to be more accurate
• Added top 10 update install errors to dashboard
• Added new report with details about install errors and WSUS scan errors
  • Contains around 400 common windows update related errors with possible actions on how to fix them
• Added new parameter to exclude deployments containing Microsoft Defender and System Center Endpoint Protection updates
  • Was previously part of the SQL query and not easily changeable nor visible to the report user
• Removed Server 2008 specific parts
• Added new filter to "per device" report called: "Missing updates with errors" and “All missing updates deployed or not”
• Added more details about errors to "per device" report
• Added update collection and maintenance window list to “per device” report
• Added column: “Earliest Deadline” to “per device” report
• Changed first sub-report name from “all uncompliant” to “compliance list”
• Changed default sort order from "count of missing updates" to "month since last update install"
• Changed "WSUS version" to "OS build version". Easier to determine actual OS version and patch level
• Changed "Defender Pattern Version" to "Defender Pattern Age" to be able to spot systems with older pattern more easily
• Added column "WSUS scan error" to system list
• Added column count of "Updates with install error" to system list
• Added column number of "Deployments non compliant" to system list
  • Helps to determine any problems with deployments when all updates are installed, but deployments are still marked as uncompliant
• Added new report to list all update deployments and their states per device
• Made "Per device" and “compliance list" report visible to be able to schedule subscriptions without the dashboard
• Fixed several minor issues with each report
• Changed SQL query for deployed updates to work better in larger environments
• Changed import script to also handle SSRS folder path with spaces in it
• Changed import script to delete existing contents of "work" folder from a previous run
• Changed import script parameter name "Upload" to "DoNotUpload". Function is the same.
• Removed import script parameter "UseViewForDataset". (To much work to keep the view consistent with regular query)
• Added new import script parameters: "ForceLegacyCardinalitySQL2016SP1AndHigher" and "ForceLegacyCardinalityOlderThanSQL2016SP1" Read more about it here

2020-12-09 v2.1:

• Fixed language and QFE problem
• Added new parameter -ForceLegacyFormat,
• Fixed minor issues and linked all reports to the per device sub-report

2020-11-03 v1.0:

• Fixed wrong parameter name, updated repository with several fixes