SCCM Configmgr Software Update Compliance Report for Specific Collection within Specific Time Frame

In this post, i will discuss about the requirement that i have got recently. Local team /manager wants to run the software update compliance report for their LBU machines (collections) to see if all the clients in collection are compliant or not for all the patches with released date between X date to Y date.

No matter whether all the patches that are requested/available in SCCM are deployed or not but it should appear in SCCM report if the clients are in good shape or not for specific period.

By default in SCCM, there are couple of reports available for software update compliance but if you want to know the compliance status for specific collection for all updates that exist in SCCM (no software update group here) between specific period let say Jan 1,2015 to Dec 31 2015 or X range to Y range.

How to generate software update compliance report for specific collection for all the updates available in SCCM within specific date ?

To create a report for this requirement, we need set of SQL views that have information about software updates ,collection,inventory of client etc.

Below are the SQL views that i used in this report:

v_GS_COMPUTER_SYSTEM

v_CICategories_All

v_CategoryInfo

v_gs_workstation_status

v_fullcollectionmembership

v_UpdateInfo

v_UpdateComplianceStatus

Download SSRS Report from Technet Gallery,Upload to your SSRS Folder ,change data source and run the report.

When you run the report ,it prompt for collection ,Start Date and End Date shown below.

image

 

image

The result what see in the report is excluded by superseded and expired updates (IsExpired=0 and IsSuperseded=0) .

The original report is taken from Garth post http://smsug.ca/blogs/garth_jones/archive/2009/02/25/patch-compliance-progression-report.aspx and modified to include the date prompt ,superseded,expired ,added inventory information like OS,update scan,IP address,Last reboot into the report.

Linked report to see list of updates for each client will be in the next post.

25 Responses to "SCCM Configmgr Software Update Compliance Report for Specific Collection within Specific Time Frame"

  1. i need query it show's about Compliant Servers , Install Pending , Pending Restart for one particular collection kindly any one help me on this

    Reply
    1. All this information you can get it if you are on Configmgr current branch 1802 and above .There is pending restart column available for clients .

      Thanks,
      Eswar

      Reply
      1. All this information you can get it if you are on Configmgr current branch 1802 and above .There is pending restart column available for clients .

        Thanks,
        Eswar

        Reply
  2. Thank you for the great report! Can you add the list of updates for each computer? So instead of just having a count, actually list out each update. For example:

    computer 1 | needs 2 updates | Updates Needed are: KBXXXXXX and KBXXXXXXXX

    Reply
    1. Hi MAtt,
      that will be linked report to list all updates that are missing for the client.
      I will look at it and update .Running out of time due to office 365 project.

      Thanks,
      Eswar

      Reply
      1. Hi Eswar,
        when run this report I get following error

        The report server cannot process the report or shared dataset. The shared data source 'DataSource1' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference)

        any suggestion. I am using SQL 2014 and SSCM 2012R2 1702.

        Reply
  3. Hey Eswar, Thanks for posting this. I am an SSRS noob. What does "The report definition has an invalid target namespace " mean when I try to upload this report to the reports server? Using SQL 2012 🙂

    Reply
    1. It looks like report needs to be downgraded or so based on the error you said above .But i have created the report on 2012 version of visual studio and this should not be the case.
      I have also tried uploading the report into my lab running on SQL 2012 ,it works fine.

      Regards,
      Eswar

      Reply
  4. Hello eswar,
    I had a problem while using this reprt. I am quering across all laptops and desktops. I am not getting result for all the machines few are. Missing.
    Ex: we have2832 machine's In my environment, I am getting report for only 2748. Remaining 80 + machines were missing.
    Can you please look into this

    Reply
    1. Hi Venky,
      Are these 80+ machines are good with software update scan success state ? if those machines are having any client health issues like no sccm client ,they will not be reported. Take a look at the SQL code inside the report to see what it is filtering init.

      Regards,
      Eswar

      Reply
  5. Hello Eswar,

    When i run over my collection with all laptops and desktops, there is a count mismatch. as we have total of 2832 machine in the the collection we are getting only 2748. why is this mismatch?

    Reply
  6. Great report, my only question would be how can it only see needed patches for what is actually being deployed... It shows patches that we do not deploy right now.

    Reply
    1. Hi Cory,
      The above report will get the compliance status for all patches from SCCM ,no matter if they are deployed or not. If you want compliance status for specific update group that is deployed ,you can use builtin reports or custom reports that are available on my blog.

      Regards,
      Eswar

      Reply
  7. Hi - Great work!. The report works very fine. I have one question: is it possible to add a combo for the update group? I want to check the compliance timeline vs one update group...

    Thanks for your help!

    Reply
      1. Sure..I mean:
        - Check the UG (for example one with monthly updates) vs a collection on August
        - Check the same UG vs same collection on Sept.
        I need to follow if the updates are installing correctly...

        I hope you understand my question...

        Thanks
        David

        Reply

Leave a Reply