This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(288, 40%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESS%3C/text%3E%3C/svg%3E)
What is the best way to construct a query that identifies devices missing specific patches? The intention is to use the results to create a device collection and then deploy updates to the collection.
Any suggestions or examples would be appreciated.
Back in the day I use to pull data using updatecompliancestatusall and updateinfo sql views to look for devices reporting a compliance status with value 2 indicating that patches were missing. Been a while but you should be able to use the same views and formulate a wql to create a collection. Look it up and see if you can get any far otherwise let me know and I will try to fetch the query from my archives.
Hi, @sover steve
Thank you for posting in Microsoft Q&A forum.
We can use CMPivot to query missing patches like below:
SoftwareUpdate | where (Categories == 'Security Updates,Windows 10, version 1903 and later') | where (KBArticleIDs == 'KB4565627' ) | order by Device asc
After running the query, we get the details of the Windows 10 devices that are missing security updates, and we can create device collection directly with the result.
For your reference:
https://www.anoopcnair.com/find-devices-missing-patches-using-configmgr-cmpivot-query/
(Please Note: Since the website is not hosted by Microsoft, just for your reference.)
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".