Wow, very funny I proposed we do the same exact thing earlier today regards to breaking it down by last character of GUID for 16 waves.
You know my next question is have you blogged the Configitem/ Baseline, if not think you could share it? 0.5% of the environment isn’t bad for sure, but you just re-synced 2 months ago. I’m curious what it would have been when its 10 months and no resync has been initiated. That’s where we are right now. Thanks Rob From: [email protected] [mailto:[email protected]] On Behalf Of Sherry Kissinger Sent: Wednesday, February 10, 2016 3:38 PM To: [email protected] Subject: Re: [mssms] RE: Rant on - State messages/Patching ok, if I add that back; it 2200. which is still only 0.5% for our environment. and yes, of course I use a ConfigItem and Baseline. :) I phase it out there over a few weeks using a collection query of "and resourceid is NOT IN (select resourceid of this configitem of greaterthan or euqal to version X.. ) AND you're smsuniqueidentifier like %[0-1] Since sms uniqueids end in 0-9 or a-e; that splits it up nicely into pie slices of 16. I just add more until I get to %[0-9]; and then I'll add "or like %[a-e] until it get just about everyone--then turn it off a week later. then 6-8 months later... I modify the configitem just enough (add a space to description) to make it increment version. Modify the collection query to again be slices of 1/16th; and every day add another 1/16th of the pie. in 2-3 weeks, I get everyone. then delete the baseline deployment and do a do-over another 6 months later. Lather, rinse, repeat. On Wednesday, February 10, 2016 12:30 PM, Robert Spinelli <[email protected]<mailto:[email protected]>> wrote: I would say you need to put the cat.CategoryInstanceID = '31' into your query also, so its scoped more, since 60 I think is too low of a number if not filtering on cat.CategoryInstanceID = '31'. I put it back in your query below. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Daniel Ratliff Sent: Wednesday, February 10, 2016 12:38 PM To: [email protected]<mailto:[email protected]> Subject: RE: [mssms] RE: Rant on - State messages/Patching Ohh, that’s a Common Table Expression right? My cohort, Jim Parris, was showing me those. Looks like we need to schedule the reset on all our clients, we have never done that before. That query works great, but gives A LOT of clients that don’t exist. I also need the client list for machines to target. Modified once more… Without filtering invalid clients, we had 617, after the filter it’s down to 286. 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 TotalPatchesReported.ResourceID, sys.Netbios_Name0, sys.Operating_System_Name_and0, sys.Client_Version0, TotalPatchesReported.PatchCount from TotalPatchesReported inner join v_R_System_Valid SYS on TotalPatchesReported.ResourceID = sys.ResourceID where TotalPatchesReported.PatchCount < 60 order by PatchCount Daniel Ratliff From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Sherry Kissinger Sent: Wednesday, February 10, 2016 12:20 PM To: [email protected]<mailto:[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' 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. 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.
