SCCM Patch Status SQL Query Based on Particular Collection | ConfigMgr

Let’s find out SCCM Patch Status SQL Query Based on Particular Collection. I have seen many questions in HTMDForum that we want the patch report, how to write a SQL query to write a report on patching, etc.

In this post, you will learn how to make a working SCCM patch status SQL query to find the patch status from your configuration manager (a.k.a ConfigMgr) database. You can customize the query as per your needs.

The following is a sample query that will help you understand the tables and views required to get the SCCM patching report working. Hence, we have done a formidable job for you.

When setting up your query, remember to replace the Collection ID, Article ID, and, optionally, the BulletinID to ensure that the query works correctly. This customization will help tailor the query to your needs and provide you with relevant information.

• May 2024 Windows 11 KB5037771 KB5037770 Patches and 2 Zero-Day Vulnerabilities
• FIX SCCM Agent Installation Error 0x80200010
• SCCM Management Insights Rules to Maintain Clean and Healthy Infra
• SCCM 2203 Upgrade Guide with Top 5 Best New Features
• SCCM Server In-place OS Upgrade to Server 2022 Guide

Index
SCCM Patch Status SQL Query
Results – SCCM Patch Status SQL Query Based on Particular Collection

NOTE! – Many other SCCM custom reports are available from Karthikeyan in the following post. SQL Query All SCCM Applications with no Deployments | ConfigMgr.

SCCM Patch Status SQL Query

Once you have initiated the client push installation method, you can track the patch installation status (a.k.a. patching) using the following query. This is the simplest way to get the patch reports from ConfigMgr.

• Open the SQL Management Studio.
• Click on the New Query button.
• Select the CM_MEM database from the drop-down menu.
  • MEM is the ConfigMgr site code.

• Copy the following SQL query to find the report for particular patches.
• Click on the Execute button.

NOTE! – Make sure to replace collection IDs, Article IDs, and Bulletin IDs before running the query.

/*Selected KB Article ID patch required or installed status for Specific Collection ID*/
Declare @Collection varchar(8)
Set @Collection = 'SMS00001' /*Enter the collection ID*/
Select
Distinct VRS.Name0 as 'MachineName',
Os.Caption0 as 'OperatingSystem',
St.SystemType00 as 'OSType',
VRS.AD_Site_Name0 as 'ADSite',
VRS.Full_Domain_Name0 as 'Domain',
VRS.User_Name0 as 'UserName',
UI.ArticleID as 'ArticleID',
UI.BulletinID as 'BulletinID',
UI.Title as 'Title',
CASE WHEN UCS.Status = 2 THEN 'Required'
WHEN UCS.Status = 3 THEN 'Installed'
ELSE 'Unknown' END AS 'KBStatus',
UI.InfoURL as 'InformationURL'
FROM v_UpdateComplianceStatus UCS
INNER JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
INNER JOIN v_CICategories_All CIC ON UI.CI_ID = CIC.CI_ID
INNER JOIN v_CategoryInfo CI ON CIC.CategoryInstanceID = CI.CategoryInstanceID
INNER JOIN v_R_System VRS ON UCS.ResourceID = VRS.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM Os on UCS.ResourceID = Os.ResourceID
INNER JOIN Computer_System_DATA St on UCS.ResourceID = st.MachineID
INNER Join v_FullCollectionMembership Col on UCS.ResourceID = Col.ResourceID
WHERE VRS.Operating_System_Name_and0 like '%Workstation%'
and Col.CollectionID = @Collection
and UI.articleid in ('4561600','4557957','4025338') /*Enter the article ID*/
and UI.BulletinID in ('ms17-010','ms17-008') /*Enter the Bulletin ID*/
and active0 = 1 and client0 = 1
Order by 10

Results – SCCM Patch Status SQL Query Based on Particular Collection

The SQL query for patch status is very helpful in terms of troubleshooting software update issues. I have seen many scenarios where SCCM admins struggle to install the details patches on Windows 10 devices.

Resources

• PowerShell Script to Import Multiple CSV Files to Pivot Table SCCM Patch Report
• How to get a report with Friendly scan errors

We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.

Author

Deepak Rai is a Technical Lead with over 14 years of experience in IT. He specializes in SCCM, Intune, and Azure. Throughout his career. Deepak has worked on various platforms such as Active Directory, Exchange, Veritas NETBACKUP, Symantec Backup Exec, NDMP devices like Netapp, EMC Data Domain, Quantum using Backup Exec 2010 and 2012, and HP StorageWorks 4048 MSL G3. He also has experience in data deduplication-related troubleshooting. Ultimately, he returned to his roots as an IT Engineer focusing on SCCM technology.