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.

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

  1. 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
  2. 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

Leave a Reply