Only 100? That seems pretty great for 72k clients. What low patch number did you use to scope it down to 100 machines? The query is giving installed + applicable security updates, so if you’re getting a count of less than 70 those are what I’m using to determine problem machines. That 70 number isn’t really based of anything scientific, just a number I picked.
Thanks for cleaning up the query, good point on not using Like if I don’t have too. Rob From: [email protected] [mailto:[email protected]] On Behalf Of Daniel Ratliff Sent: Wednesday, February 10, 2016 9:10 AM To: [email protected] Subject: [mssms] RE: Rant on - State messages/Patching Ran this in our environment, we have about 100 clients that show a very low count, some even as low as 1 patch reported. We have around 72k total clients, so not too bad for our environment. There is a chance these are legit though for us, they are just now moving away from VCM over to SCCM for server patching, and the suspected problem machines are all servers. Also cleaned up the query a bit. · Use v_r_system_valid to get active clients back · Use agt.agentname = ‘MP_ClientRegistration’ instead of a like · Use ui.CI_UniqueID not like ‘Scope%’ instead of ‘%Scope%’ (Others may know a more efficient way to filter these, subselect maybe?) SELECT DISTINCT css.ResourceID, sys.Netbios_Name0, sys.Client_Version0, sys.Operating_System_Name_and0, agt.AgentTime AS MPRegTime, site.SMS_Assigned_Sites0, ws.LastHWScan, COUNT(css.ResourceID) AS PatchCount FROM v_R_System_valid AS sys INNER JOIN v_UpdateComplianceStatus AS css ON sys.ResourceID = css.ResourceID INNER JOIN v_AgentDiscoveries AS agt ON sys.ResourceID = agt.ResourceId INNER JOIN v_RA_System_SMSAssignedSites AS site ON sys.ResourceID = site.ResourceID INNER JOIN v_GS_WORKSTATION_STATUS AS ws ON sys.ResourceID = ws.ResourceID LEFT OUTER JOIN v_UpdateInfo AS ui ON ui.CI_ID = css.CI_ID INNER JOIN v_CICategories_All AS cat ON ui.CI_ID = cat.CI_ID AND ui.CI_UniqueID NOT LIKE 'Scope%' AND cat.CategoryTypeName = 'UpdateClassification' AND cat.CategoryInstanceID = '31' AND agt.AgentName = 'MP_ClientRegistration' GROUP BY css.ResourceID, sys.Netbios_Name0, sys.Client_Version0, agt.AgentTime, site.SMS_Assigned_Sites0, ws.LastHWScan, sys.Operating_System_Name_and0 ORDER BY Patchcount Daniel Ratliff From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Robert Spinelli Sent: Wednesday, February 10, 2016 8:29 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] Rant on - State messages/Patching Every company I’ve worked/consulted at always the same problem, patch states messages in particular “get lost” and the reports aren’t accurate. This has existed since SCCM 2007 when state messages were 1st introduced. Only way to fix it is to run state message resync: • https://msdn.microsoft.com/en-us/library/cc146437.aspx The different places I’ve been at have had the issue before I showed up, so it’s not like a black cloud follows me and state messages stop working. I wrote the query below that gives a count of patches per machine in my environment and about 12% of my environment have the issue. It’s not scientific but if the machine has a patch count less than 70, I consider the machine is missing state messages. - Warning: I’m not a SQL expert, so I’m sure someone can make this run a lot better, as it takes about 90 secs in our environment. - Warning: If you run this in your environment and tempdb blows up, not my issue. We have a read only replica I run this against to ensure it doesn’t impact production, so use at own risk. select distinct css.resourceid, sys.name0, sys.Client_Version0, sys.Operating_System_Name_and0,agt.AgentTime as MPRegTime, site.SMS_Assigned_Sites0, ws.LastHWScan, count (css.Resourceid) as PatchCount from v_R_System sys join v_UpdateComplianceStatus css on sys.ResourceID = css.ResourceID join v_AgentDiscoveries agt on sys.ResourceID = agt.ResourceId join v_RA_System_SMSAssignedSites site on sys.ResourceID = site.ResourceID join v_GS_WORKSTATION_STATUS ws on sys.ResourceID = ws.ResourceID left join v_UpdateInfo ui on ui.CI_ID = css.CI_ID join v_CICategories_All cat on ui.CI_ID = cat.CI_ID and UI.CI_UniqueID not like '%scope%' and cat.CategoryTypeName = 'UpdateClassification' and cat.CategoryInstanceID = '31' and agt.AgentName like '%mp%' group by css.ResourceID, sys.Name0, sys.Client_Version0, agt.AgentTime, site.SMS_Assigned_Sites0, ws.LastHWScan, sys.Operating_System_Name_and0 order by Patchcount --27 = critical updates --28 = definition updates --30 = feature packs --31 = security updates --33 = tools --34 = update rollups --35 = updates It’s not good when the product you use to report patch compliance can’t be trusted because the data is wrong for 12% of your environment. When you run a state message resync on a machine about 770 messages are created per machine. If you have 100k machines and you create some advert/dcm, etc. that runs once a month that’s 77 million state messages that need to be processed. I really don’t like having to do these band aid approaches to make something that should work in the product actually work. Are other people battling with this, if so have you found a better solution then having to just target machines to force full state message resyncs. Rant over. Rob The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.
