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.
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.
30 Comments
Pingback: SCCM ConfigMgr Compliance status of client for multiple software update groups | Eswar Koneti Blog
i need query it show's about Compliant Servers , Install Pending , Pending Restart for one particular collection kindly any one help me on this
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
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
Thank you, great work.
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
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
I'm not sure if I'm missing it but did you post the linked report to see a list of updates for each client?
Hi Paul,
It is not updated yet but i think ,I have report ,will upload soon .
Regards,
Eswar
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.
Hi Asif,
Did you change the datasource and try to run the report ?
Regards,
Eswar
Is it possible to alter this so that it shows update status within a given timeframe, but only for updates that have been deployed, i.e. within any update group. The built in reports only allow me to report compliance against a specific update group, i've not yet found a way of reporting against anything deployed, i.e. multiple update groups.
As there are some updated we've chosen not to deploy, this reporting, while great in itself, is showing hosts as non compliant where we have chosen not to deploy specific updates.
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 🙂
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
Great, thanks for your reply 🙂
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
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
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?
This is perfect. I used it.
Hi Eswar,
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...
I mean: I want to check the compliance progress. For example:
- Check one UG vs a collection on August
- Check the same UG vs the same collection on September
The goal is to check the progress of the updates installation...
I hope you understand my question...
Thanks
David
Hi David,
You want to limit the time frame for all the updates in specific update group? if so, i believe this can be done but i dont have the code readily with me.
Thanks,
Eswar
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.
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
Dear Eswar,
Many thanks for the report. Can you please explain if the number of patches displayed are against the software update group list?
linked report is not ready yet.
Regards,
Eswar
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!
what do you mean by compliance timeline vs update group ? Can you provide more information ?
Regards,
Eswar
Hi,
I mean: I want to check the compliance progress. For example:
- Check one UG vs a collection on August
- Check the same UG vs the same collection on September
The goal is to check the progress of the updates installation...
I hope you understand my question...
Thanks
David
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
Hi - Tried your report and it worked perfectly. Now it is time to take care of business to install missing updates. The report file was very helpful.
Thanks
Ram