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:








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.




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

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

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

    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.


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

  3. 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.

    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.


  4. 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!


Leave a Reply