Let’s Find Missing Patches using ConfigMgr CMPivot Query. I have seen many questions in HTMDForum that we want the patch report using the SQL query. In this post, you will learn how to use the SCCM CMPivot query to find out missing patches.
You can customize the CMPivot query as per your need and requirement. The following is the main CMPivot entity used is Softwareupdate. The CMPivot entity helps to find out A software update applicable but not installed on the device.
entity | operator1 | operator2 | ...
The following is the sample CMPivot query that shall help you to understand the entity called Softwareupdate and views which are required to get the SCCM patching report working. Hence we have done a tough job for you. Now, you just need to replace the following to get the query working for you.
Launch ConfigMgr CMPivot
Let’s try to find out the missing patches using CMPivot query with filtering option on KB article for Windows 10 devices.
- Navigate to device collection against that you want to run the CMPivot query.
- Select All Systems device collection.
NOTE! – In a production environment with more than many devices, I don’t recommend to use All System collection. Instead, use the collection with smaller number of devices to try out the query.
- Right-Click on All Systems collection.
- Select Start CMPivot.
Find Missing Patches using SCCM CMPivot Query
- CMPivot Query tool got launched.
- Get familiarized with new query language and CMPivot tool.
- Check out the Run Query button from the SCCM CMPivot tool.
SoftwareUpdate | where (Categories == 'Security Updates,Windows 10, version 1903 and later') | where (KBArticleIDs == 'KB4565627' ) | order by Device asc
NOTE! – This CMPivot query is to find out the devices that are missing with KB4565627. The Category which I used is Security Updates, Windows 10, version 1903, and later.
- Now, you have the details of the devices that missing security updates (hotfixes) on Windows 10 devices.
- Now you got the results of the SCCM CMPivot query.
NOTE! – Depending on the number of devices and complexity of the query, the CMPivot query shall take time to return the results. More details available in the following blog post. 👉SCCM CMPivot Architecture Fast Channel Making | ConfigMgr.
- Create a collection with Windows 10 devices that are missing security updates.
- And probably you can use this collection to pull more granular reports for troubleshooting etc…
- Click on Create collection (device collection) option as shown below.
- A collection creation wizard gets launched.
- Limiting Collection is selected automatically. The limiting collection is selected based on device collection that you run CMPivot.
- Click Next to continue.
NOTE! – More details about the SCCM Collection creation process is explained in the following blog post. What is Collection, How to Create SCCM Direct Membership Collections?
- This device collection is a static collection.
- All the devices that showed up in the CMPivot query got automatically added to the static device collection called “Missing Security Updates Patches“.
- Click Next to continue.
- Click Next, Next, and Close to complete the collection creation process based CMPivot query results.
Success: General • Collection Name: Missing Security Updates Patches • Comment: Membership Rules • (Direct) PROD-WIN20
CMPivot Results – Find Devices Missing Patches using ConfigMgr CMPivot Query
Let’s check the CMPivot query results to find out the devices that are Missing Security Updates Patches!.
- Let’s see the collection membership created directly from CMPivot query results!
- Navigate to \Assets and Compliance\Overview\Devices\Missing Security Updates Patches.
The results of both collection and CMPivot are the same! Happy Learning.