Find Devices Missing Patches using ConfigMgr CMPivot Query | SCCM

3
Find Devices Missing Patches using ConfigMgr CMPivot Query

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.
Launch SCCM CMPivot Query Tool - Find Missing Patches using ConfigMgr CMPivot Query | SCCM
Find Devices Missing Patches using ConfigMgr CMPivot Query | SCCM

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.

Find Missing Patches using ConfigMgr CMPivot Query | SCCM
Find Missing Patches using ConfigMgr CMPivot Query | SCCM
  • 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.

Find Missing Patches using ConfigMgr CMPivot Query | SCCM
Find Devices Missing Patches using ConfigMgr CMPivot Query | SCCM
  • 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.
Find Missing Patches using ConfigMgr CMPivot Query | SCCM
Find Missing Patches using ConfigMgr CMPivot Query | SCCM
  • 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?

Collection - Find out Missing Security Updates Patches using ConfigMgr CMPivot
Collection – Find out Missing Security Updates Patches using ConfigMgr CMPivot
  • 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.
Find out Missing Security Updates Patches using ConfigMgr CMPivot
Find out Missing Security Updates Patches using ConfigMgr CMPivot
  • 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!.

CMPivot Results - Find Devices Missing Patches using SCCM CMPivot Query
CMPivot Results – Find Devices Missing Patches using SCCM CMPivot Query
  • Let’s see the collection membership created directly from CMPivot query results!
  • Navigate to \Assets and Compliance\Overview\Devices\Missing Security Updates Patches.
Missing Security Updates Patches SCCM Collection
Missing Security Updates Patches SCCM Collection

The results of both collection and CMPivot are the same! Happy Learning.

Resources

3 COMMENTS

  1. Great one. Thank you
    Extra bonus, get count of the devices with below query.

    SoftwareUpdate | summarize countif ( )

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.