Assistance with SQL Query for Retrieving Software Update Groups and Associated Patches in SCCM

Question

Hello Everyone,

I hope this message finds you well. I am reaching out for assistance as I'm relatively new to SCCM and SQL, and I'm facing challenges in retrieving a comprehensive list of software update groups along with the associated patches (specifically, patches with the "Title" is important) using a SQL query. I've explored the available software update views but haven't found a direct solution for my requirements.

To provide a specific example, I have a Software Update Group named

"OCT-2023-PATCHES," and within this group, I have included five specific patches. My goal is to create a SQL query that can fetch all the software update group names in my SCCM environment and the corresponding list of patches within each group.

If anyone could offer guidance, share sample queries, or point me towards relevant documentation, it would be immensely helpful. I want to clarify that I am not considering the use of the SSRS service for this particular task, and I know we have option to create custom view/table to achieve this, but I can't create due to clients' restrictions.

Thank you in advance for your assistance!

Best regards,

Swahela Mulla

Answer 1

Hi Swahela,

Welcome to the Q&A Forum; this is a great place to get support, answers, and tips.

Thank you for posting your question; I'll be more than glad to help you out.

To adequately assist you with your issue/problem, it would be constructive for us to reproduce your scenario.

Please provide us with at least the following things:

(1) DDL and sample data population, i.e., CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e., logic and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in #1 above.

(4) Your SQL Server version (SELECT @@version;)

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 2

This should get you started:

select SUG.Title [SUGTitle], SUG.Description, SUG.IsDeployed, ui.Title [UpdateTitle], ui.ArticleID, ui.DateRevised, ui.IsSuperseded, ui.IsExpired from v_CIRelation_All [Relationship]

join v_AuthListInfo [SUG] on SUG.CI_ID=Relationship.CI_ID

join v_UpdateInfo ui on ui.CI_ID=Relationship.ReferencedCI_ID

Where Relationship.RelationType=1

Order by SUG.Title, UI.Title

Answer 3

Hi @Swahela Mulla

Here is the sample query, you may need to replace the following to get the query working for you.

• Collection ID
• ArticleID
• BulletinID (not mandatory)

/*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

Please refer to this blog for more details: SCCM Patch Status SQL Query Based on Particular Collection | ConfigMgr.

Best regards,

Cosmog Hong

---

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 "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.