Mastering Configuration Manager Patch Compliance Reporting

Hi, Jonas here!

Or as we say in the north of Germany: “Moin Moin!”
I am a Microsoft Premier Field Engineer (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 Reporting Services (SSRS) report and made that available to several departments in the organization.
As mentioned before, it's been a while since I created the report and if I would start now it would be a PowerBI version or I would simply grab one of the PowerBI reports available right now, but since I still use the report and find it quite helpful, I decided to share that with the rest of the world.


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.rsd” file and use it in SQL directly.
The whole solution can be found on GitHub:
Feel free to improve the SQL queries or some of the reports on Github.

The report explained:

The main report dashboard looks like this:


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.

Find the full explanation for each graph in the GitHub readme:

The report also has two sub-reports. One to show you a list of systems in a specific state (1st sub-report) and one for a list of missing updates for a single system (2nd sub-report).


1st 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 how the Excel list in the early days looked like:


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

2nd sub-report (per system)

If you click on the number of missing updates in the column “missing updates approved” a per system sub-report will be opened which will only show the specific missing updates for the selected system. 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:
The report is basically a copy of one of the default MECM reports with some adjustments and three customs filters to filter for “Deployed and missing updates”, “Not deployed but missing” or just “All updates per device”.



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.
  • If you have a simple group of systems and deploy every needed 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.
  • The report will also show updates deployed as “available” and is not made to just focus on updates deployed as “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 the WMI class Win32_Quickfixengineering which needs to be enabled in the hardware inventory client settings. The class is only used to determine the last installation of A security update to identify systems which seem to be fine but actually have never installed anything.
  • The report is also using the 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 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.
  • I have defined “compliant” to be a system which has:
    • all the updates installed which are deployed (no matter if “available” or “required”)
    • the last security update installation in Win32_Quickfixengineering was in the current month (not necessarily the monthly security rollup, just one security update)
  • The update report has multiple sub-reports to drill further down and almost each report will use the same dataset
  • The SQL query of the dataset is made to filter out 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.
  • The 2nd Level sub-report per system will also show 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
  • 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.
  • Each sub-report will be hidden in SSRS to avoid direct usage and keep the folder as clean as possible.
  • The reports are made on SSRS 2017. I haven't tested other versions.


How to install

  1. Make sure you have enabled Win32_Quickfixengineering in the client settings for hardware inventory
  2. You could also use AD System Discovery to have further data, but that's no hard requirement.
  3. Either clone the repository or download the whole content.
  4. Copy the whole content to the Reporting Services Server (SSRS)
  5. Create a new folder on the report server website were 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.
  6. 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
  7. Change the directory to the folder were the import script “Import-SSRSReports.ps1” can be found.
  8. 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, delete the SSRS website folder with all its content first an re-create the folder in SSRS. It is possible to overwrite the reports automatically, but I faced some issues with some settings not being overwritten as desired and I don't use that method anymore. That's why everything should be deleted first to avoid errors.


Parameter Required Example value Description
ReportServerURI Yes http://reportserver.domain.local/reportserver The URL of the SQL Reporting Services Server.
Can be found in the MECM Console under “MonitoringOverviewReporting” -> “Report Server” or in the “Report Server Configuration Manager” under “Web service URL”
TargetFolderPath Yes ConfigMgr_P11/Custom_UpdateReporting The folder were 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 ConfigMgr_P11/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602} 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.
DefaultCollection No SMS00001 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 All% 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 “All%” or “All Syst%” or “Servers%” to get the “Servers of the environment” collection for example.
DoNotHideReports No ‘Software Updates Compliance – Overview','Compare Update Compliance' 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.
Upload No $true If set to $false the reports will not be uploaded. 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
UseViewForDataset No $false All reports can either use a dataset called “UpdatesSummary”, which is the default and will execute the full sql query right from the Reporting Services Server, or a dataset called “UpdatesSummaryView” which will select from a sql view which needs to be created first. (I will not explain that process in detail)
$false will use the default dataset and $true will use the dataset using a sql view.
ReportSourcePath No $PSScriptRoot or “C:TempReports” 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


Get the full list of parameters by running:

Get-Help .Import-SSRSReports.ps1 -Full

Or go to and read the GitHub readme.

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

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}" -Upload $false

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}" -DefaultCollection "P1100012" -DefaultCollectionFilter "All Servers of Contoso%"



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.


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

Premier Field Engineer – Microsoft Germany




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



Download the whole solution or clone the repository here:


This article was originally published by Microsoft's ITOps Talk Blog. You can find the original article here.