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

Swahela Mulla 90 Reputation points
2023-11-22T12:41:52.02+00:00

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

Microsoft System Center
Microsoft System Center
A suite of Microsoft systems management products that offer solutions for managing datacenter resources, private clouds, and client devices.
902 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,340 questions
Microsoft Configuration Manager Updates
Microsoft Configuration Manager Updates
Microsoft Configuration Manager: An integrated solution for for managing large groups of personal computers and servers.Updates: Broadly released fixes addressing specific issue(s) or related bug(s). Updates may also include new or modified features (i.e. changing default behavior).
1,011 questions
Microsoft Configuration Manager Deployment
Microsoft Configuration Manager Deployment
Microsoft Configuration Manager: An integrated solution for for managing large groups of personal computers and servers.Deployment: The process of delivering, assembling, and maintaining a particular version of a software system at a site.
941 questions
Microsoft Configuration Manager
{count} votes

3 answers

Sort by: Most helpful
  1. Bjoern Peters 8,856 Reputation points
    2023-11-22T12:50:11.85+00:00

    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!

    0 comments No comments

  2. Sherry Kissinger 4,211 Reputation points
    2023-11-22T13:16:56.43+00:00

    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


  3. LiHongMSFT-4306 25,651 Reputation points
    2023-11-23T01:57:18.52+00:00

    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.

    0 comments No comments