If you’re taking out the cat.CategoryInstanceID = '31' and only using UpdateClassification, I would say 60 is too low.
As a quick test on a good machine with cat.CategoryInstanceID = '31', I return back 135 rows. If I remove it and only use UpdateClassification I return back 216. I put the and cat.CategoryInstanceID = '31' back into your query below. Sherry, did you just do an advert or did you get fancy and do some DCM when you did the resyncs? From: [email protected] [mailto:[email protected]] On Behalf Of Sherry Kissinger Sent: Wednesday, February 10, 2016 12:20 PM To: [email protected] Subject: Re: [mssms] RE: Rant on - State messages/Patching I stole that query, tweaked it, and ran this: ;with TotalPatchesReported as ( SELECT css.ResourceID, COUNT(css.ResourceID) AS PatchCount FROM v_UpdateComplianceStatus AS css Inner 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' GROUP BY css.ResourceID ) select count(resourceid) from TotalPatchesReported where TotalPatchesReported.PatchCount < 60 It's <600 machines total -- and we have well over 300k clients that have something to say (if I remove the patchcount <60, 300k+ clients had something to say). If I check the percentages of that vs. machines that have something to say...we have approximately 0.15% machines which might need the resync. I do acknowledge that the resync has to happen--we do it 2x a year, against all clients, phased out over 3 weeks at a time to get all clients to slowly re-send everything they know ; to kind of "reset". the last time we did that was about 2 months ago; so maybe that's why we only have 0.15% that need it again. But I'd say that's pretty decent. Of course, maybe my random selection of a patchcount of <60 was flawed, and I should have picked 90 or 100 or something. On Wednesday, February 10, 2016 10:50 AM, Robert Spinelli <[email protected]<mailto:[email protected]>> wrote: 30 seems low, you might have the issue on those boxes. I would run the refresh script on one of them and see if the count on that machine goes up. • https://msdn.microsoft.com/en-us/library/cc146437.aspx Run it, wait 15 mins or whatever you have set for state messages to come up and then run the query again. Rob From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Daniel Ratliff Sent: Wednesday, February 10, 2016 11:29 AM To: [email protected]<mailto:[email protected]> Subject: RE: [mssms] RE: Rant on - State messages/Patching I used <30, mainly because we had hundreds of servers around that number. Daniel Ratliff From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Robert Spinelli Sent: Wednesday, February 10, 2016 11:26 AM To: [email protected]<mailto:[email protected]> Subject: RE: [mssms] RE: Rant on - State messages/Patching 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: mailto:[email protected] [mailto:[email protected]] On Behalf Of Daniel Ratliff Sent: Wednesday, February 10, 2016 9:10 AM To: [email protected]<mailto:[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. 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.
