Close Menu
    Facebook X (Twitter) Instagram
    Sunday, July 20
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»configmgr»SCCM Configmgr Software update Compliance Report for multiple Software Update groups per collection

    SCCM Configmgr Software update Compliance Report for multiple Software Update groups per collection

    Eswar KonetiBy Eswar KonetiMay 28, 12:10 am3 Mins Read configmgr 18,670 Views
    Share
    Facebook Twitter LinkedIn Reddit

     

    Long ago ,did blog post on How to get software update compliance for specific update group per collection with drill down report to list the Required/missing ,unknown clients with some additional information like recent hardware inventory, last update scan results ,OS etc .

    This report will only work for single software update group per collection ,but in Configuration Manager ,there could Multiple Software update group created as part of Patching process and it would be difficult to know the compliance status for the same collection for multiple update groups and this task become tedious if you run the same report multiple times and write down the results into file.

    with the help of Configuration Manager console ,it is possible to see the Compliance % (Required, installed ,Unknown  and %) for the software update group for the deployed collection but if you want to see the same for multiple update groups and get the list of Required Clients etc ,not possible ,Thus you need SSRS Report.

    Wouldn’t it be nice to have SSRS Report that list the Software update compliance for multiple update groups per collection ?

    This report is lying in my bucket for longer time and finally going out to public.

    I have made little changes to the report (nothing major ) with respect to prompts (select multiple update groups) and drill down report changes as well.

    This report allow you to select multiple Update groups and single collection as shown below . you need to action on Required (a.k.a Missing) and Unknown status (Client having issues with software update scan ).

    It has 3 RDL files 1) Update compliance Status for multiple Groups per collection and 2) Linked report to know what are the client with specific Software update Status (Required ,Unknown) 3) List all missing updates for specific client.

    First report looks like below:

    image

    Installed—>Specifies that the software update is applicable on the client computer and that the client computer already has the software update installed.

    Not Required—>Specifies that the software update is not applicable on the client computer, and therefore, the software update is not required.

    Required –>Specifies that the software update is applicable and required on the client computer

    Unknown—> Specifies that the site server has not received a state message from the client computer,could be various reasons like scan did not run successfully,scan completed but state message did not sent successfully etc.

    Click on the dark blue colour to see the Required ,Unknown clients for the selected software update group for specific collection.

    image

    As you see above snippet, click on the arrow pointed towards computer name to see the list of missing updates for specific client.

    image

     

    Download the Reports from TechNet Gallery ,Upload to your Configmgr SSRS reports ,Change the Data source and Run the Report .

    How to troubleshoot software update issues ,refer http://eskonr.com/2015/04/sccm-2012-troubleshoot-client-software-update-issues/

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

    Related Posts

    SCCM SQL Report – Compare Installed Apps on TWO Different Computers

    July 13, 10:35 am

    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

    43 Comments

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

    2. CHENG SUN on November 21, 2018 4:11 PM

      Hi, thank you for the report. A question, or request. The 1st report, is compliance status per collection for multiple groups (can be 1 SUG as well). The 2nd report, it shows all the clients in that collection with status Required against the selected SUG. In the 3rd report, it shows all the missing updates on the selected client. Is it possible to show missing updates against the selected SUG in Report 1? That is, if there are 20 updates in SUG1, can Report 3 show how many updates are required but missing on select client, out of the 20 in SUG1?

      Reply
      • Eswar Koneti on January 1, 2019 2:41 PM

        Hi,
        For 1 SUG ,you already have default software update reports hence i created this for multiple SUG groups .
        I think for your requirement to see report for 1 SUG ,you can use the default SUG reports.

        thanks,
        Eswar

        Reply
        • Nacho on December 17, 2019 6:40 PM

          Hi Eswar, first of all thank you for your great job. I share the point of Cheng Sun, if would be great if i can drill down to see just the updates for the SUGs involved in my first selection in your Report 1. When i share this reports to another IT collegaues they always 'complain' about this. We just need to see the updates that belongs to the multiple SUGs we select in Report 1.

          Thank you very much
          Nacho

          Reply
          • Eswar Koneti on February 28, 2020 2:12 PM

            Hi Nacho,
            This is in my pending list and i understand the needs. Due to other priorities in office 365 projects, could not able to manage it.
            I will look at it and keep update the blog post.
            Sorry for the delay.

            Thanks,
            Eswar

            Reply
      • olivier on September 20, 2021 6:37 PM

        HEllo,

        Thank for this report.
        Where i can find it ? The link to the Technet seem broke, and i don't see it on your Github?
        Olivier

        Reply
        • Eswar Koneti on September 22, 2021 12:22 AM

          Hi,
          you can follow this article to download the technet scripts http://eskonr.com/2021/02/did-you-miss-to-download-your-samples-from-technet-gallery-here-is-how-you-can-do-it/

          Thanks,
          Eswar

          Reply
    3. Stephen Palladino on November 8, 2018 11:37 PM

      Great Reports they where working fine but now that i have the full amount of users into our new SCCM setup when i run the report against multiple software updates groups and specify our main patching collection around 7000 end points it spins and fails after a long time saying it was cancelled by user. If i run it against one update group against the same 7000 end point collection it will finish it takes about 8 minutes... has anyone else had this issue when pointing to multiple groups and a large collection? Is there a fix?

      Reply
    4. Hoang Nguyen on July 13, 2018 11:01 AM

      Thanks for sharing this report. Is it possible to specify software update group to collection within Specific Time Frame?

      Reply
      • Eswar Koneti on July 26, 2018 8:36 PM

        have you seen this ? http://eskonr.com/2017/07/sccm-configmgr-software-update-compliance-report-for-specific-collection-within-specific-time-frame/
        This will help you to get compliance report for specific collection in specific time frame.

        Regards,
        Eswar

        Reply
        • Hoang on July 26, 2018 10:23 PM

          Hi Eswar,
          Thanks for responding and yes, I do see that link but it give you select the Collection not SUG. I've tried to change data source using SUG SQL but it is not look right. I am not very good in building SQL reporting services.

          Reply
    5. Paul on May 19, 2018 9:09 PM

      This report is very useful. Just wondering if it could easily be adjusted to show the results of all groups together as a single set of values like an overall compliance type of report?

      Reply
      • Eswar Koneti on June 2, 2018 12:21 AM

        You mean all software update groups against specific collection ? If so, yes that can be done but need time to create.Will add to-do list.

        Regards,
        Eswar

        Reply
    6. Rune U on February 7, 2018 11:28 PM

      Hi Eswar

      Thank you for this report, it's very helpful.

      However I have a problem: When running the report, I choose a Software Update Group and Collection, then click Required to see the lists of clients.
      Then when I click a specific client, it lists all required updates in the SCCM DB, not limited to those in the Software Update Group I selected earlier.

      Is that by design?

      I was expecting it to limit by software update group like this report: http://eskonr.com/2015/12/sccm-configmgr-ssrs-report-get-list-of-missing-updates-for-pc-from-specific-software-update-group/

      Thank you
      Rune

      Reply
      • Eswar Koneti on February 10, 2018 5:46 PM

        Hi Rune,
        Nope,that is not by design however linked report needs to be adjusted to take the software update group that you selected as input.
        Need to look into the code to modify the report .These days very busy with O365 project hence delayed.

        Regards,
        Eswar

        Reply
        • Rune U on February 12, 2018 6:59 PM

          Thank you. I found a way to link one of your other reports to the one I mentioned and now I have a working solution.
          Let me know if you want a copy 🙂

          Reply
          • Eswar Koneti on February 24, 2018 10:18 AM

            sure ,you can post the link or send report that would help others.

            Regards,
            Eswar

            Reply
            • andyD on October 12, 2018 4:08 AM

              Hi Rune U,

              Can you share the fix for this problem i am having it also, updates are showing that are not in the update group specified

              Reply
    7. Kaspars on November 16, 2017 3:05 PM

      Hi
      Why I see strange behavior on the second report and maybe it also influences first one too. I have approved november updes. But I wanted to check how October ones are doing. For example I see that 34 computer missing updates in Update group 10 2017. I click on missing number and see computers. When I click on one of computers I see no october updates missing but allready November (revised date 11-2017).
      How is that? So I cant realy see compliance for previos month.

      Reply
      • Eswar Koneti on January 24, 2018 10:04 PM

        Are these October updates superseded ,hence they are missing in report and showing Nov patches ?

        Regards,
        Eswar

        Reply
    8. Pingback: Hilfreiche ConfigMgr-Reports aus der Community | clientmgmt.de by André Picker [MVP]

    9. Muzzy on June 9, 2017 1:54 AM

      Great job Eswar! I wonder if it possible to rebuild this report in a opposite way - aka "Compliance report for Multiple Device Collections per update group"? Thank you in advance for your reply!

      Reply
      • Eswar Koneti on July 7, 2017 1:18 AM

        Hi Muzzy,
        Yes, that is good one. I will work on it .

        Regards,
        Eswar

        Reply
        • Thomas on September 4, 2017 4:03 PM

          Hi Eswar, thanks for the report. Just wondering if the function to query against multiple collection is available? As we need to query multiple software update groups against multiple collection groups.

          Reply
          • Eswar Koneti on September 5, 2017 10:13 PM

            Hi Thomas,
            You can try this http://eskonr.com/2016/07/sccm-configmgr-get-the-update-compliance-status-for-multiple-update-groups-against-multiple-collections-using-sql-query-without-reporting/

            Regards,
            Eswar

            Reply
            • Nick B on December 21, 2017 7:46 PM

              Hi Eswar - some great work here.............. so may I jump on the bandwaggon ? 🙂 - Im after a similar report, listing each machine in a chosen collection, showing the general compliance (compliant/non-compliance/unknown) status against 4 update groups - (as column headers) - is that possible ? - thank you

              Reply
              • Eswar Koneti on December 22, 2017 6:55 PM

                Hi Nick,
                yes that is possible but would take time due to the code involved init. This is interesting report and helps lot of people there.
                I will add this to my to-do list. If you are subscribed for post, you will get notify on this.

                Regards,
                Eswar

              • Nick Bassett on January 2, 2018 9:03 PM

                thank you Eswar 🙂

    10. Frank on March 31, 2017 5:46 AM

      Thanks for this, it's just what I needed. Have an issue with the first report in the series "Software Update Compliance Status Per Collection for Multiple Groups". When running it via Report Builder it seems to work fine. The issue I'm having is when I run it via a browser http://sccmserver/reports, the page appears blank after choosing 'update group' & 'collection'

      Reply
      • Eswar Koneti on April 4, 2017 8:50 PM

        Hi Frank,
        Strange that ,it works fine in reporting builder but doesnt when running browser. can you try to import the reports again into folder ,change the datasource and run it ?
        I just tried the reports, it works fine for me.

        Regards,
        Eswar

        Reply
    11. lenny on March 14, 2017 11:51 PM

      Hi Eswar

      Want to start of by saying awesome report and thanks for sharing!

      However i got an issue, when click on the Required or unknown links i receive this following error:
      Reports/2.List Computers with specific status per UG per Collection' cannot be found. (rsItemNotFound)

      Are you able to point me into the right direction to fix this?

      Cheers

      Reply
      • Eswar Koneti on March 18, 2017 3:49 PM

        Hi Lenny,
        can you check if you have other 2 reports uploaded into the same folder ? This report contain 3 reports as said in the blog post.

        Regards,
        Eswar

        Reply
    12. sccm_buff on February 14, 2017 12:29 AM

      Hi Eswar, New Report which gives inventory of your hostname and also the required updates, but the required updates are not referred from SUG selected in first report (from where we start), infact its referring to all required updates for that hostname, Correct me if i am wrong.

      Reply
    13. Ricky Kollberg on January 12, 2017 9:44 PM

      Hi Eswar love this report "Software Update Compliance Status Per Collection for Multiple Groups" and then click on those who req the sug "List Computers with Specific Status Per UG Per Collection " But is there any way to go a step and se what patches are missing? not only the Update Group :"ADR Patch Tuesday Security And Critical Updates"
      Best Regards
      Ricky

      Reply
      • Eswar Koneti on January 24, 2017 3:44 PM

        Hi Ricky,
        I have updated the post with your requirement ,hope you will find it useful. Redownload the files and upload to your SSRS reports folder.

        Regards,
        Eswar

        Reply
    14. Justin Sparks on December 21, 2016 6:35 AM

      Thanks for all of the great posts! I am needing a report similar to this that shows the enforcement states for a deployment by collection. Is there a way to modify the report "States 1 - Enforcement states for a deployment" to include a collection filter?

      Thanks!

      Reply
      • Eswar Koneti on January 4, 2017 1:18 PM

        Hi,
        Nope, you can get only get the enforcement state for the collection that are deployed with software update groups. You cannot get enforcement states for non-deployed collections as they dont send any enforcement states like downloaded updates,failed etc.
        IF you have deployed a software update group to multiple collections and you want to use filter in this report ,you can do so by using the following SQL query:
        Replace the parameters like assignment ID and collection ID .

        select
        DeploymentID=a.Assignment_UniqueID,
        DeploymentName=a.AssignmentName, a.CollectionName,a.CollectionID,
        Available=a.StartTime,
        Deadline=a.EnforcementDeadline,
        LastEnforcementState=sn.StateName,
        NumberOfComputers=sc.StateCount,
        --PComputers=convert(float, sc.StateCount*100.0) / isnull(nullif(@COLLCOUNT, 0), 1),
        DeploymentStateID=sc.StateType*10000 + sc.StateID
        from v_CIAssignment a
        cross apply(select StateType, StateID, StateCount=count(*) from v_AssignmentState_Combined where AssignmentID=a.AssignmentID and StateType in (300,301) group by StateType, StateID) sc
        left join v_StateNames sn on sn.TopicType=sc.StateType and sn.StateID=sc.StateID
        where a.AssignmentName='SOFTWARE UPDATE ASSIGNMENT'
        and a.CollectionID='COLLECTION ID'
        order by sc.StateCount desc, sn.StateName

        Regards,
        Eswar

        Reply
    15. Jose Sisto on July 13, 2016 7:59 AM

      Very useful Eswar, exactly what I needed to unify SU compliance reports.

      I added some semaphoric color coding to make it more visual for quick inspections. You can include it if you like it.

      On "Software Update Compliance Status Per Collection for Multiple Groups" report on properties of the data row, I changed Fill > Backgroundcolor Expresion by

      =Switch(Fields!Success__.Value < 50,"Tomato", Fields!Success__.Value = 75, "LightGreen")

      and sorted results by Sucess ratio

      Thanks for such excellent report.

      Reply
      • Eswar Koneti on July 13, 2016 9:10 AM

        Hi Jose,
        Glad it helped you and thanks for your thought. Yes, you can lot more changes depends on the requirement using this basic report.

        Thanks,
        Eswar

        Reply
    16. Anna Orlove on July 6, 2016 3:22 AM

      This report SCCM Configmgr Software update Compliance Report for multiple Software Update groups per collection is really close to what my CIO is looking for, except he wants the 'dwell time' between a patch release and it's deployment on the target systems in a collection. How can I get the 'dwell time' i.e., the time between when a software update patch is released and when it's deployed? A thousand thanks in advance!

      Reply
      • Eswar Koneti on July 6, 2016 8:57 AM

        Hi Anna, where do you want to see the dwell time and deployment time? Which report are you referring to?

        Reply
    17. Michael on June 25, 2016 1:43 AM

      Can you specify with more clarity how to change the data source and what to point it to?

      Thank you!

      Reply
      • Eswar Koneti on July 8, 2016 6:57 PM

        Hi Michael,
        You can Follow the blog post http://www.enhansoft.com/blog/how-to-change-the-ssrs-datasource

        Thanks,
        Eswar

        Reply

    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.