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 need.
The following is the sample query that shall help you to understand the tables 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.
- Collection ID
- BulletinID (not mandatory)
NOTE! – Many other SCCM custom reports are available from Karthikeyan in the following post. SQL Query All SCCM Applications with no Deployments | ConfigMgr.
Once you have initiated the client push installation method. You can track the status of the patch installation (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 are struggling to get the details patches installed on Windows 10 devices.