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. Following is the main CMPivot entity used is Softwareupdate. The CMPivot entity helps to find out if A software update is 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 the 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 the CMPivot query with the filtering option on the KB article for Windows 10 devices.
- Navigate to the device collection against which 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 using the All System collection. Instead, use the collection with a 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 are 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 the 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 and 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.
Resources
- CMPivot overview
- Monitor ConfigMgr Client Status from SCCM Console | Online | Offline
- SCCM CMPivot Architecture Fast Channel Making | ConfigMgr
Great one. Thank you
Extra bonus, get count of the devices with below query.
SoftwareUpdate | summarize countif ( )
Of course that you for the boost
Whats the query language if we don’t have a specific KB to look for?
Can this be used to find all applicable but not installed KB’s?