SCCM - SQL query to show results of a Software Update Group

Paul Hunter 1 Reputation point
2021-01-08T11:19:22.463+00:00

Hi,
We have software update groups for our regular windows updates. The statistics show compliant, non-compliant and unknown. If I click on them it shows which devices are in those groups.
I’d like to produce a SQL query in monitoring to show me from that particular update which devices are non-compliant.
Is this possible?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,748 questions
Microsoft Configuration Manager

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Paul Hunter 1 Reputation point
    2021-01-09T08:43:05.217+00:00

    Hi,
    We have software update groups for our regular windows updates. The statistics show compliant, non-compliant and unknown. If I click on them it shows which devices are in those groups.
    I’d like to produce a SQL query in monitoring to show me from that particular update which devices are non-compliant.
    Is this possible?

    0 comments
  2. Garth Jones 2,071 Reputation points
    2021-01-11T03:22:23.263+00:00
    0 comments
  3. AllenLiu-MSFT 40,401 Reputation points Microsoft Vendor
    2021-01-11T09:44:41.677+00:00

    @Paul Hunter
    Thank you for posting in Microsoft Q&A forum.
    You may try the built-in report: Compliance 3-Update group(per update)
    Select SUG and collection, view report, then click each update title, redirect to Compliance 6-Specific software update states(secondary), the click different states redirect to Compliance 8-Computer in a specific compliance state for an update(secondary).

    If the response is helpful, please click "Accept Answer" and upvote it.
    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.

  4. Kalyan Sundar 561 Reputation points
    2021-01-15T08:22:20.953+00:00

    Try this SQL

    select sys.name0 [Computername],
    osd.OSLanguage00 [OS Language],
    osd.Caption00 [OS Name]
    from v_updateinfo ui
    inner join v_UpdateComplianceStatus ucs on ucs.ci_id=ui.ci_id
    join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID
    join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'
    join v_R_System sys on sys.resourceid=ucs.resourceid
    join Operating_System_DATA osd on sys.ResourceID = osd.MachineID
    and ucs.status='2' -- required
    AND ui.articleid='ArticleID'
    order by sys.name0