Close Menu
    Facebook X (Twitter) Instagram
    Tuesday, May 20
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»SCCM Configmgr Get the Update Compliance Status for multiple Update groups against Multiple collections using SQL query without reporting

    SCCM Configmgr Get the Update Compliance Status for multiple Update groups against Multiple collections using SQL query without reporting

    Eswar KonetiBy Eswar KonetiJuly 08, 6:55 pm3 Mins Read CM2012 22,759 Views
    Share
    Facebook Twitter LinkedIn Reddit

    Recently ,we had an issue with SCCM Configmgr Reporting services role (Remote SQL sitting on VM was crashed ,blog post coming soon ) and we were unable to generate reports mainly for the Software update compliance status that happens every month. This post is about ,how to check the software update compliance status for the deployed software update group/Groups per collection/collections without using Configmgr Reports . The reason for posting this blog is ,fixing the Configmgr Reporting services role took more than expected time and meantime ,we need to check the status of patch compliance status and troubleshoot the non-compliant machines (servers) within the Maintenance window.

    I thought the SQL Code which I used to generate the compliance status would be handy for others if they do not want to Use configmgr Reports and use SQL Code for Quick results.

    If you are unable to generate compliance status using the SSRS Reports ,the only possible method is ,to depend on Monitoring node—deployments ,look for the software update deployment for particular collection and see the non-compliant machines for troubleshooting which is not easy method if you have large number of deployments and collections.

    So ,to overcome this ,you can USE SQL management studio and run the query (posted below) to generate non-compliance list of clients with extra information like hardware inventory,software update scan,,Operating System ,IP address,User Name ,does it have Client etc.

    This SQL Query should be helpful to quickly generate compliance Status for multiple software update groups and for multiple collections.

    I recently posted a blog about ,how to generate software update compliance Status for multiple update groups per collection but this SQL query helps to generate update compliance status for multiple updates groups against multiple collections.

    you can use this SQL query to create nice SSRS Report for multiple update groups per multiple collections in OneClick.

    The below SQL code is for list of clients with required/Missing Status ,If you want clients with Unknown ,change the @status value to 0 ,More about Update compliance Status ,see below :

    Update compliance Status:

    0—Detection Status Unknown

    1—Not Applicable

    2—Required/Missing

    3—Already Installed /Compliant

    image

     

    --SQL Code to Generate Update compliance Status for multiple update groups against multiple collections

    Declare @Status nvarchar(255);set @Status='2';
    --Status 0 for Unknown, 1 for Not Applicable,2 for Required ,3 for installed
    select sys.name0 [Computer Name],sys.User_Name0 [User Name], os.caption0 [OS],
    CONVERT(VARCHAR(26), ws.lasthwscan, 100) as [LastHWScan],
    CONVERT(VARCHAR(26), uss.lastscantime, 100) AS 'LastSUScanTime',
    CONVERT(VARCHAR(26), sys.last_logon_timestamp0, 100) AS 'Last Logon Time',
    case when sys.client0='1' then 'Yes' else 'No'
    end as 'Client (Yes/No)', c.IPAddress AS [IP Address]
    From v_Update_ComplianceStatusAll UCS
    left join v_r_system sys on ucs.resourceid=sys.resourceid
    left join v_FullCollectionMembership fcm on sys.resourceid=fcm.resourceid
    left join v_collection coll on coll.collectionid=fcm.collectionid
    left join v_GS_OPERATING_SYSTEM os on ucs.resourceid=os.resourceid
    left join v_gs_workstation_status ws on ucs.resourceid=ws.resourceid
    left join v_updatescanstatus uss on ucs.ResourceId=uss.ResourceID
    left join v_AuthListInfo LI on ucs.ci_id=li.ci_id
    INNER JOIN (SELECT     IP1.resourceid AS rsid2, IPAddress = substring
    ((SELECT     (IP_Addresses0 + ', ')
    FROM    v_RA_System_IPAddresses IP2
    WHERE     IP2.IP_Addresses0 NOT LIKE '169%' AND IP2.IP_Addresses0 NOT LIKE '0.%' AND IP2.IP_Addresses0 NOT LIKE '%::%' AND
    IP_Addresses0 NOT LIKE '192.%' AND IP1.resourceid = IP2.resourceid
    ORDER BY resourceid FOR xml path('')), 1, 50000)
    FROM    v_RA_System_IPAddresses IP1
    GROUP BY resourceid) c ON c.rsid2 = ucs.resourceid
    where li.title IN (‘SUG1’,'SUG2’,SUG3’) and coll.collectionID in ('PS10029A','PS10000D')and ucs.status=@Status
    group by sys.name0,sys.User_Name0,os.Caption0,ws.LastHWScan ,uss.LastScanTime,sys.Last_Logon_Timestamp0,sys.client0,c.IPAddress
    order by 1

    Hope it helps !

    Collection Compliance configmgr multiple collections multiple update groups patch compliance report Patch Report Reports SCCM Software update compliance Software update group SQL SSRS SUP report Update report without reports
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    Identifying devices managed by Intune but not reporting to WUfB using KQL

    November 09, 10:28 am

    12 Comments

    1. Pingback: SCCM ConfigMgr Compliance status of client for multiple software update groups | Eswar Koneti Blog

    2. BritV8 on December 4, 2018 5:33 AM

      Awesome man! been trying to find this for a while. I now need to convert it into a WQL Query, so I can call it from Powershell

      Reply
      • Eswar Koneti on December 18, 2018 11:58 PM

        cool .Why do you need WQL ? are you going to create collection for this ? Reports are far better than collections for monitoring and you can do subscription based as well to get reports via email.

        Thanks,
        Eswar

        Reply
    3. Prasanna on December 16, 2017 2:19 AM

      I use to deploy SUG to 2 different collections (One for Workstation and another for Server), My business requirement is to Generate a report a single Overall compliance with two tables Server and Workstations.
      Please help me

      Reply
      • Eswar Koneti on December 22, 2017 7:01 PM

        you can create one collection and use include option to add both the collection into this and run the report to get overall compliance for specific update group.

        Regards,
        Eswar

        Reply
    4. Pingback: Patching by Orchestrator Part -2 – Tadgata

    5. Shailendra Kumar (@ShailendraKuma8) on November 6, 2016 3:44 AM

      Hi Eshwar,

      that query is very good to track the the machines that need required updates as per Software Update group. i just run such query and found 3 machines needs required updates but i also want to know the Updates KB or required updates details so that i can look into deeper.. is this feasible to modify such query accordingly...?

      Reply
      • Eswar Koneti on November 7, 2016 2:10 PM

        have you looked at this post ? it does have linked reports to show you the list of clients with missing patches etc. http://eskonr.com/2016/05/sccm-configmgr-software-update-compliance-report-for-multiple-software-update-groups-per-collection/

        Regards,
        Eswar

        Reply
        • Adrian Lutea (@AdrianLutea) on November 7, 2017 10:22 PM

          Hello Eswar,

          How you manage the assets with an outdated WUA agent? Because it will show up into the "Installed/Not Applicable" column which is wrong. You know that for example for Win2012R2 if you have a WUA version less than 7.9.9600.16422 the server will not receive any updates and it will pop up as compliant.

          Thanks,
          Adrian

          Reply
          • Eswar Koneti on November 7, 2017 10:27 PM

            Hi Adrian,
            based on the link https://support.microsoft.com/en-sg/help/949104/how-to-update-the-windows-update-agent-to-the-latest-version ,you need to upgrade your old version of windows update agent versions to latest on applicable OS as per the technet.
            I will write blog post on how to use compliance settings to detect older version of windows update agent which will help you to update the WUA agent.

            Regards,
            Eswar

            Reply
            • Adrian Lutea (@AdrianLutea) on November 7, 2017 10:33 PM

              That's not a problem for me, I already build a TS to deploy all 7 KBs for 2012 and a package for 2008. But my environment has more than 20 000 assets and is difficult to manage the mw for all of them. I also build a report, reporting the wua.version0 for all assets.
              I just want to see your opinion on how to integrate and report those outdated wua in this compliance report.
              My suggestion is to add into the ucs status 2 (the Required column) the old wua versions and the "good" wua version into the "Installed" one.
              Maybe you think at something else.

              Reply
              • Eswar Koneti on November 7, 2017 10:38 PM

                ya ,thats good idea but it is always good to have that as separate collection to identify if any old WUA and bring to current version.
                I never focus on WUA into the report because it is always require to have latest WUA to get the patching done .

                Regards,
                Eswar

    Leave a ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.