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 How to generate patch compliance report that shows all updates for specific collection ?

    SCCM Configmgr How to generate patch compliance report that shows all updates for specific collection ?

    Eswar KonetiBy Eswar KonetiFebruary 04, 12:50 am3 Mins Read CM2012 42,780 Views
    Share
    Facebook Twitter LinkedIn Reddit

    There are couple of posts on TechNet blog and other forums asking for ‘Is there any report that list all updates with compliance status for specific collection’ ? Read it once again ,A software update compliance report that list all updates whether Targeted, Installed, Missing for specific Collection. A collection may contain X number of clients and each client may produce Y Number of patches for sure from your Configmgr environment with compliance status . So when you generate report with this requirement,you know how many rows do you get ? X (number of clients)* Y (number of patches).

    Assume X=200 Clients and Y =100 Patches (till date) ,the report consists of 200* 100=20,000 rows.

    So if you are going to generate report for management to show ,what is the compliance status (this is not really compliance status required for Management) with this requirement,Are they going to read it ? How does it matter to them,if particular computer is missing XX number of patches .Management wants to know if the PC is compliant or not .

    You should not encourage for such unrealistic requests,if they are going to ask you,tell them ,what they are going to get .Garth has blogged similar kind of requirement How to perform basic Software Audit ,read it here http://www.enhansoft.com/blog/how-to-perform-a-basic-software-audit

    So ,how do you perform basic software update compliance audit ? There are variety of reports available by default in Configmgr with category  Software Updates - A Compliance.

    If your management asked for any patch compliance report, get them overall compliance status from specific collection for specific update group (this will get overall compliance from specific software update group only) or compliance status for each machine from specific collection (this will generate report with all updates in your Configmgr).

    There are lot of patch compliance reports on my blog with different requirements ,so have a look at them ,download the RDL file ,edit the RDL file using your favorite Tool(Report builder or BI or Visual studio etc) ,

    edit the RDL file,copy the code into SQL server management studio ,do adjustments what is needed for you and get back the query to SSRS Report.

    The recommended procedure /best practice is ,try to use the existing default reports or reports posted on my blog for compliance status per collection OR Per OU etc and start looking at computers that are NON-Compliant (if at least one patch is required by Client,it report as Non-Compliant)  and start troubleshooting the non-Compliant PC rather generating unnecessary data.

    If you still want to generate such report, here is the SQL query for you Smile .All you need is ,just change the collection ID  and run it from SQL Server management Studio.

    select
    vsv.Netbios_Name0 [PC Name],
    CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2) AS MonthPosted,
    ui.BulletinID,
    ui.ArticleID,
    ui.Title,
    UpdateClassification=cls.CategoryInstanceName,
    Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
    Installed=(case when css.Status=3 then '*' else '' end),
    IsRequired=(case when css.Status=2 then '*' else '' end),
    Deadline=CONVERT ( varchar (26) , cdl.Deadline , 100),
    ui.dateposted [Date Posted],
    ui.InfoURL as InformationURL
    from V_UpdateComplianceStatus  css
    join V_UpdateInfo ui on ui.CI_ID=css.CI_ID
    join V_CICategoryInfo_All vnd on vnd.CI_ID=ui.CI_ID and vnd.CategoryTypeName='Company'
    join V_CICategoryInfo_All cls on cls.CI_ID=ui.CI_ID and cls.CategoryTypeName='UpdateClassification'
    join v_ClientCollectionMembers ccm on ccm.ResourceID=css.ResourceID
    JOIN dbo.v_R_System_valid vsv ON vsv.ResourceID = css.ResourceID
    left join V_CITargetedMachines   ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
    outer apply (
    select Deadline=min(a.EnforcementDeadline)
    from V_CIAssignment  a
    join V_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
    ) cdl
    where ccm.CollectionID='PS100018'
    order by ui.Title

    Output of the SQL Query:

    image

    audit report Compliance Compliance reports configmgr patch compliance report Patch Report Reports SCCM software update audit report Software update compliance SQL SSRS SUP report
    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

    Investigating Co-Management Issues with Windows Endpoints in SCCM/Intune

    October 26, 10:45 pm

    24 Comments

    1. Jon Haustetter on October 30, 2020 3:45 AM

      Hi Eswar, I realize this post is a bit dated but is this reporting based on updates pushed via sccm or in my case, our company has a separate wsus system that handles updates independently of sccm. Will it still work?

      Reply
      • Eswar Koneti on October 31, 2020 7:16 PM

        Hi Jon,
        If you dont have SCCM, you cannot use this report. This is purely for SCCM and WSUS is integrated wit it.
        If you have different patching solution, the SQL tables/Views might be different hence this report wont work.
        You need to see what data is available in your system and create a report.

        Thanks,
        Eswar

        Reply
    2. Marco Autorino on March 19, 2020 7:04 PM

      very helpful.
      But one thing I just can't understand. how to understand or create a list of clients that do not work updates. example: these updates have not been successful on these PCs. Incredible that there is no way.

      Reply
      • Eswar Koneti on March 20, 2020 10:55 PM

        Hi Marco,
        you can try the default software update compliance reports to know the list of clients or you can use the SQL views to create custom report.

        Thanks,
        Eswar

        Reply
    3. Marco Autorino on March 19, 2020 7:00 PM

      very helpful.
      But one thing I just can't understand. how to understand or create a list of clients that do not work updates. example: these updates have not been successful on these PCs. Incredible that there is no way.

      Reply
    4. talktome on March 7, 2020 3:48 AM

      Hi Eswar - I tried running this sql query after changing the Collection ID. But it is stuck on executing. Any suggestions?

      Reply
      • Eswar Koneti on March 15, 2020 10:19 AM

        Hi,
        how many clients are managing using configmgr? it may take time depends on the number of clients that the collection have else it will be quick.

        Thanks,
        Eswar

        Reply
    5. Dhananjay on November 8, 2019 10:17 PM

      Hi Eswar
      Quries
      Will SCCM 1806 or 1906 capable to track daily patching deployments ?
      Will I get to know where is the patching delays while in deployments ??
      How delays can be track or monitor ?
      Please advice.
      reg
      DJ

      Reply
      • Eswar Koneti on November 30, 2019 3:40 AM

        Hi,
        Yes they are. Have you looked at the default software update compliance reports?
        also the monitoring node for the compliance status?

        Thanks,
        Eswar

        Reply
    6. Dhananjay on November 8, 2019 4:55 PM

      Hi Eswar

      Quries

      Will SCCM 1806 or 1906 capable to track daily patching deployments ?
      Will I get to know where is the patching delays while in deployments ??
      How delays can be track or monitor ?
      Please advice.

      reg
      DJ

      Reply
      • Eswar Koneti on November 30, 2019 3:40 AM

        Hi,
        Yes they are. Have you looked at the default software update compliance reports?
        also the monitoring node for the compliance status?

        Thanks,
        Eswar

        Reply
    7. Sambhaji Sawant on June 3, 2019 12:39 PM

      Hi Eswar,

      I ran the query which you mentioned but I got below error. I am running SCCM 2016 and SQL Server 2016.

      Error :- Invalid Object Name ‘V_UpdateCompliaceStatus’

      Regards,
      Sam

      Reply
      • Eswar Koneti on June 3, 2019 12:46 PM

        Hi,
        What version of sccm are you on? Surprise to see invalid object name for updatecompliancestatus. Open sql server management studio and run select top 10 from v_updatecompliancestatus if you see any results or not.

        Regards
        Eswar

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

    9. Igor on September 14, 2018 1:05 AM

      Is there a report already created with this information?

      Reply
      • Eswar Koneti on September 30, 2018 10:58 PM

        Hi Igor,
        No ,I haven't created any SSRS report because it is not genuine report to run so often .If someone run this report against collection of 1000 computers and each computer start generating list of 100 patches then it is going to be 1000*100 rows report which no one is going to monitor. So i just left the SQL query for users if they want to make use of it to create report.
        why do you think this report is important ?

        Thanks,
        Eswar

        Reply
    10. pankaj bari on August 26, 2017 5:42 AM

      Hello Eswar,

      Can help me to understand or modify this SQL query to run on one collection for one software update group?

      Reply
      • pankaj bari on August 26, 2017 5:52 AM

        Also if we can Add Not required & Unknow state in query please

        Reply
        • Eswar Koneti on October 19, 2017 11:18 AM

          Hi Pankaj,
          Noted ,I will look into later .

          Regards,
          Eswar

          Reply
      • Eswar Koneti on August 26, 2017 11:19 PM

        Hi Pankaj,
        For that, you can use default reports with category software update compliance.

        Regards,
        Eswar

        Reply
    11. Sarika on December 9, 2016 7:06 PM

      Hi Eswar,

      I need a report which should show below details as output
      1.Business area(since we deploy patches to many areas)
      2.individual installed, pending count of devices for individual patches of that Business area

      Business area MSRC KB INSTALLED PENDING
      ba 1 MSRC1 KB1
      KB2
      KB3
      BA1 MSRC2 KB1
      KB2
      KB3

      Reply
      • Eswar Koneti on December 21, 2016 1:48 PM

        Hi,
        Business area is something you need to define in your reports how you want to display it and for KB's installed or not ,that is going to be long list since each month MS release lot of updates.
        What i would suggest is ,create software update group (it contain list of updates) and run the report against this update group for specific collection (i believe this collection will be your business area).

        Thanks,
        Eswar

        Reply
        • Brad Sam on July 12, 2019 12:04 AM

          Hi Eswar, like this report, how i can get updates just for last 3 month, Thanks

          Reply
          • Eswar Koneti on July 23, 2019 1:14 PM

            Hi Brad,
            It is possible ,you can edit the report and limit the time period to last 3 months. For custom reports, you can reach out to system center dudes for consulting https://www.systemcenterdudes.com/consulting-services-fixed-price-plans/

            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.