Close Menu
    Monday, December 30

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

    Eswar KonetiBy 3 Mins Read CM2012 42,522 Views

    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

    Share.

    Related Posts

    24 Comments

    1. Jon Haustetter on

      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

        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

      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

        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

      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

      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

        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

      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

        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

      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

        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

      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

        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

      • Eswar Koneti on

        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

      Hello Eswar,

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

      Reply
    11. Sarika on

      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

        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

    Leave a Reply

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