Sms2016 reports not updating
The reason for posting this blog is ,fixing the Configmgr Reporting services role took more than expected time and meantime ,we need to check the status of patch compliance status and troubleshoot the non-compliant machines (servers) within the Maintenance window.I thought the SQL Code which I used to generate the compliance status would be handy for others if they do not want to Use configmgr Reports and use SQL Code for Quick results.so you only need to worry about the approved(deployed) count and made sure if always 0 to get your client PC compliant.If there are any updates requested by client and some of them are approved and if they appear in report ,client will become non-compliant. if you still have questions,please report them via comments section .Recently ,we had an issue with SCCM Configmgr Reporting services role (Remote SQL sitting on VM was crashed ,blog post coming soon ) and we were unable to generate reports mainly for the Software update compliance status that happens every month.This post is about ,how to check the software update compliance status for the deployed software update group/Groups per collection/collections without using Configmgr Reports .
If Count of Required Patches =0 and Count of Deployed(Approved) Patches=0 Then PC is Complaint If Count of Required Patches! =0 then PC is Non-Complaint If Count of Required Patches!
If you want to run the report 2) for bunch of PC’s ,you need to create custom report and it is not easy to say in one line whether Client is Compliant for all the approved (deployed) patches.
So,how do I check quickly, if PC is compliant for all the approved (deployed) patches meaning,if I enter the Collection name into the Report,it should give me status for the clients with how many Patches Targeted ,how many patches missing and how many are still required with final status Is Complaint or not ?
If you are unable to generate compliance status using the SSRS Reports ,the only possible method is ,to depend on Monitoring node—deployments ,look for the software update deployment for particular collection and see the non-compliant machines for troubleshooting which is not easy method if you have large number of deployments and collections.
So ,to overcome this ,you can USE SQL management studio and run the query (posted below) to generate non-compliance list of clients with extra information like hardware inventory,software update scan,, Operating System , IP address, User Name ,does it have Client etc.