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]> wrote:
<!--#yiv6747332696 _filtered #yiv6747332696 {font-family:Helvetica;panose-1:2
11 6 4 2 2 2 2 2 4;} _filtered #yiv6747332696 {font-family:"Cambria
Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv6747332696
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv6747332696
{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} _filtered #yiv6747332696
{font-family:Consolas;panose-1:2 11 6 9 2 2 4 3 2 4;}#yiv6747332696
#yiv6747332696 p.yiv6747332696MsoNormal, #yiv6747332696
li.yiv6747332696MsoNormal, #yiv6747332696 div.yiv6747332696MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv6747332696 a:link, #yiv6747332696
span.yiv6747332696MsoHyperlink
{color:blue;text-decoration:underline;}#yiv6747332696 a:visited, #yiv6747332696
span.yiv6747332696MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv6747332696
p.yiv6747332696MsoAcetate, #yiv6747332696 li.yiv6747332696MsoAcetate,
#yiv6747332696 div.yiv6747332696MsoAcetate
{margin:0in;margin-bottom:.0001pt;font-size:8.0pt;font-family:"Tahoma",
sans-serif;}#yiv6747332696 span.yiv6747332696BalloonTextChar
{font-family:"Tahoma", sans-serif;}#yiv6747332696 p.yiv6747332696msoacetate,
#yiv6747332696 li.yiv6747332696msoacetate, #yiv6747332696
div.yiv6747332696msoacetate
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv6747332696 p.yiv6747332696msolistparagraph, #yiv6747332696
li.yiv6747332696msolistparagraph, #yiv6747332696
div.yiv6747332696msolistparagraph
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv6747332696 p.yiv6747332696msonormal, #yiv6747332696
li.yiv6747332696msonormal, #yiv6747332696 div.yiv6747332696msonormal
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv6747332696 p.yiv6747332696msochpdefault, #yiv6747332696
li.yiv6747332696msochpdefault, #yiv6747332696 div.yiv6747332696msochpdefault
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv6747332696 p.yiv6747332696msonormal1, #yiv6747332696
li.yiv6747332696msonormal1, #yiv6747332696 div.yiv6747332696msonormal1
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",
sans-serif;}#yiv6747332696 p.yiv6747332696msoacetate1, #yiv6747332696
li.yiv6747332696msoacetate1, #yiv6747332696 div.yiv6747332696msoacetate1
{margin:0in;margin-bottom:.0001pt;font-size:8.0pt;font-family:"Tahoma",
sans-serif;}#yiv6747332696 p.yiv6747332696msolistparagraph1, #yiv6747332696
li.yiv6747332696msolistparagraph1, #yiv6747332696
div.yiv6747332696msolistparagraph1
{margin-top:0in;margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",
sans-serif;}#yiv6747332696 p.yiv6747332696msochpdefault1, #yiv6747332696
li.yiv6747332696msochpdefault1, #yiv6747332696 div.yiv6747332696msochpdefault1
{margin-right:0in;margin-left:0in;font-size:10.0pt;font-family:"Times New
Roman", serif;}#yiv6747332696 span.yiv6747332696msohyperlink {}#yiv6747332696
span.yiv6747332696msohyperlinkfollowed {}#yiv6747332696
span.yiv6747332696balloontextchar {}#yiv6747332696
span.yiv6747332696emailstyle20 {}#yiv6747332696 span.yiv6747332696emailstyle21
{}#yiv6747332696 span.yiv6747332696emailstyle22 {}#yiv6747332696
span.yiv6747332696emailstyle23 {}#yiv6747332696 span.yiv6747332696emailstyle24
{}#yiv6747332696 span.yiv6747332696msohyperlink1
{color:#0563C1;text-decoration:underline;}#yiv6747332696
span.yiv6747332696msohyperlinkfollowed1
{color:#954F72;text-decoration:underline;}#yiv6747332696
span.yiv6747332696balloontextchar1 {font-family:"Tahoma",
sans-serif;}#yiv6747332696 span.yiv6747332696emailstyle201
{font-family:"Calibri", sans-serif;color:windowtext;}#yiv6747332696
span.yiv6747332696emailstyle211 {font-family:"Calibri",
sans-serif;color:windowtext;}#yiv6747332696 span.yiv6747332696emailstyle221
{font-family:"Calibri", sans-serif;color:#1F497D;}#yiv6747332696
span.yiv6747332696emailstyle231 {font-family:"Calibri",
sans-serif;color:windowtext;}#yiv6747332696 span.yiv6747332696emailstyle241
{font-family:"Calibri", sans-serif;color:#1F497D;}#yiv6747332696
span.yiv6747332696EmailStyle43 {font-family:"Calibri",
sans-serif;color:windowtext;}#yiv6747332696 span.yiv6747332696EmailStyle44
{font-family:"Calibri", sans-serif;color:#1F497D;}#yiv6747332696
.yiv6747332696MsoChpDefault {font-size:10.0pt;} _filtered #yiv6747332696
{margin:1.0in 1.0in 1.0in 1.0in;}#yiv6747332696 div.yiv6747332696WordSection1
{}-->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]]On Behalf Of Daniel Ratliff
Sent: Wednesday, February 10, 2016 12:38 PM
To: [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 TotalPatchesReportedas ( SELECT css.ResourceID,
COUNT(css.ResourceID)AS PatchCount FROM v_UpdateComplianceStatusAS css
Innerjoin v_UpdateInfo AS uiON ui.CI_ID = css.CI_ID INNERJOIN
v_CICategories_AllAS cat ON ui.CI_ID= cat.CI_ID AND ui.CI_UniqueIDNOT
LIKE 'Scope%' AND cat.CategoryTypeName= 'UpdateClassification'and
cat.CategoryInstanceID = '31' GROUPBY css.ResourceID ) select
TotalPatchesReported.ResourceID,sys.Netbios_Name0,sys.Operating_System_Name_and0,sys.Client_Version0,
TotalPatchesReported.PatchCount from TotalPatchesReportedinner join
v_R_System_ValidSYS on TotalPatchesReported.ResourceID= sys.ResourceID where
TotalPatchesReported.PatchCount< 60 orderby PatchCount Daniel Ratliff
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'
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]> 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]]On Behalf Of Daniel Ratliff
Sent: Wednesday, February 10, 2016 11:29 AM
To: [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]]On
Behalf Of Robert Spinelli
Sent: Wednesday, February 10, 2016 11:26 AM
To: [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]
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?) SELECTDISTINCT
css.ResourceID,sys.Netbios_Name0,sys.Client_Version0,sys.Operating_System_Name_and0,
agt.AgentTimeAS MPRegTime,site.SMS_Assigned_Sites0, ws.LastHWScan,
COUNT(css.ResourceID)AS PatchCount FROM
v_R_System_validASsysINNERJOIN
v_UpdateComplianceStatusAS cssONsys.ResourceID= css.ResourceIDINNERJOIN
v_AgentDiscoveriesAS agtONsys.ResourceID=
agt.ResourceIdINNERJOIN
v_RA_System_SMSAssignedSitesASsiteONsys.ResourceID=site.ResourceIDINNERJOIN
v_GS_WORKSTATION_STATUSAS wsONsys.ResourceID=
ws.ResourceIDLEFTOUTERJOIN v_UpdateInfoAS uiON
ui.CI_ID= css.CI_IDINNERJOIN v_CICategories_AllAS
catON ui.CI_ID= cat.CI_IDAND
ui.CI_UniqueIDNOTLIKE'Scope%'AND cat.CategoryTypeName='UpdateClassification'AND
cat.CategoryInstanceID='31'AND
agt.AgentName='MP_ClientRegistration' GROUPBY
css.ResourceID,sys.Netbios_Name0,sys.Client_Version0,
agt.AgentTime,site.SMS_Assigned_Sites0,
ws.LastHWScan,sys.Operating_System_Name_and0 ORDERBY Patchcount Daniel
Ratliff From:[email protected]
[mailto:[email protected]]On Behalf Of Robert Spinelli
Sent: Wednesday, February 10, 2016 8:29 AM
To: [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.
