Sms2016 reports not updating
User_Name0 [User Name], os.caption0 [OS], CONVERT(VARCHAR(26), ws.lasthwscan, 100) as [Last HWScan], CONVERT(VARCHAR(26), uss.lastscantime, 100) AS 'Last SUScan Time', CONVERT(VARCHAR(26), sys.last_logon_timestamp0, 100) AS 'Last Logon Time', case when sys.client0='1' then 'Yes' else 'No' end as 'Client (Yes/No)', c.
IPAddress AS [IP Address] From v_Update_Compliance Status All UCS left join v_r_system sys on ucs.resourceid=sys.resourceid left join v_Full Collection Membership fcm on sys.resourceid=fcm.resourceid left join v_collection coll on coll.collectionid=fcm.collectionid left join v_GS_OPERATING_SYSTEM os on ucs.resourceid=os.resourceid left join v_gs_workstation_status ws on ucs.resourceid=ws.resourceid left join v_updatescanstatus uss on ucs. Resource ID left join v_Auth List Info LI on ucs.ci_id=li.ci_id INNER JOIN (SELECT IP1.resourceid AS rsid2, IPAddress = substring ((SELECT (IP_Addresses0 ', ') FROM v_RA_System_IPAddresses IP2 WHERE IP2.
=0 and Count of Deployed(Approved) Patches=0 then PC is Complaint Before you proceed to download the report,you need to understand few things how this SQL query is written and what filters are used in the report to achieve the task.
Client becomes Non-Complaint only when there are updates needed by the PC otherwise ,we can say PC is Complaint.
I have not made any custom filtering on the required count ,i just bring all the updates what client requested for.
If you want to exclude the updates that are not deployed by your organisation ,you can edit the report for custom changes.
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 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.So my primary filter (where condition ) used here is to check if status=2 means required/missing from V_update Compliancestatus.Based on this criteria, I have calculated the total number of patches deployed to the PC and how many are still needed by the PC.