I haven't posted the CI. mostly because it's boring. It's a detect only
script (no remediation); because remember I'm having machines drop out of the
collection and don't deserve it once they've run that version of the CI ever.
It contains a vbscript of 5 lines--sure, I could probably re-do it as a posh
1liner, but I've had this since CM07. We did it under cm07 the same way
(remember cm07? that old thing?)
On Error Resume Nextset newCCMUpdatesStore = CreateObject
("Microsoft.CCM.UpdatesStore")newCCMUpdatesStore.RefreshServerComplianceStatewscript.echo
"OK"wscript.quit(0)
you technically don't "need" lines 1, 4 or 5. lol. But I like to be tidy.
and I use the "OK" in the Detection logic of the CI for "what means success is
that the string of "OK" was returned ".
fyi, I hand typed the above... I didn't copy paste. So test that script before
you just use it. I might have typo'd something.
On Wednesday, February 10, 2016 3:58 PM, Robert Spinelli
<[email protected]> wrote:
<!--#yiv5531976351 _filtered #yiv5531976351 {font-family:Helvetica;panose-1:2
11 6 4 2 2 2 2 2 4;} _filtered #yiv5531976351 {font-family:"Cambria
Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv5531976351
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv5531976351
{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} _filtered #yiv5531976351
{font-family:Consolas;panose-1:2 11 6 9 2 2 4 3 2 4;}#yiv5531976351
#yiv5531976351 p.yiv5531976351MsoNormal, #yiv5531976351
li.yiv5531976351MsoNormal, #yiv5531976351 div.yiv5531976351MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 a:link, #yiv5531976351
span.yiv5531976351MsoHyperlink
{color:blue;text-decoration:underline;}#yiv5531976351 a:visited, #yiv5531976351
span.yiv5531976351MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv5531976351
p.yiv5531976351msoacetate, #yiv5531976351 li.yiv5531976351msoacetate,
#yiv5531976351 div.yiv5531976351msoacetate
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 p.yiv5531976351msolistparagraph, #yiv5531976351
li.yiv5531976351msolistparagraph, #yiv5531976351
div.yiv5531976351msolistparagraph
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 p.yiv5531976351msonormal, #yiv5531976351
li.yiv5531976351msonormal, #yiv5531976351 div.yiv5531976351msonormal
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 p.yiv5531976351msochpdefault, #yiv5531976351
li.yiv5531976351msochpdefault, #yiv5531976351 div.yiv5531976351msochpdefault
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 p.yiv5531976351msonormal1, #yiv5531976351
li.yiv5531976351msonormal1, #yiv5531976351 div.yiv5531976351msonormal1
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 p.yiv5531976351msoacetate1, #yiv5531976351
li.yiv5531976351msoacetate1, #yiv5531976351 div.yiv5531976351msoacetate1
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 p.yiv5531976351msolistparagraph1, #yiv5531976351
li.yiv5531976351msolistparagraph1, #yiv5531976351
div.yiv5531976351msolistparagraph1
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 p.yiv5531976351msochpdefault1, #yiv5531976351
li.yiv5531976351msochpdefault1, #yiv5531976351 div.yiv5531976351msochpdefault1
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 span.yiv5531976351msohyperlink {}#yiv5531976351
span.yiv5531976351msohyperlinkfollowed {}#yiv5531976351
span.yiv5531976351balloontextchar {}#yiv5531976351
span.yiv5531976351msohyperlink1 {}#yiv5531976351
span.yiv5531976351msohyperlinkfollowed1 {}#yiv5531976351
span.yiv5531976351balloontextchar1 {}#yiv5531976351
span.yiv5531976351emailstyle201 {}#yiv5531976351
span.yiv5531976351emailstyle211 {}#yiv5531976351
span.yiv5531976351emailstyle221 {}#yiv5531976351
span.yiv5531976351emailstyle231 {}#yiv5531976351
span.yiv5531976351emailstyle241 {}#yiv5531976351 span.yiv5531976351emailstyle43
{}#yiv5531976351 span.yiv5531976351emailstyle44 {}#yiv5531976351
p.yiv5531976351msonormal2, #yiv5531976351 li.yiv5531976351msonormal2,
#yiv5531976351 div.yiv5531976351msonormal2
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 span.yiv5531976351msohyperlink2
{color:blue;text-decoration:underline;}#yiv5531976351
span.yiv5531976351msohyperlinkfollowed2
{color:purple;text-decoration:underline;}#yiv5531976351
p.yiv5531976351msoacetate2, #yiv5531976351 li.yiv5531976351msoacetate2,
#yiv5531976351 div.yiv5531976351msoacetate2
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 span.yiv5531976351balloontextchar2
{font-family:"Tahoma", sans-serif;}#yiv5531976351
p.yiv5531976351msolistparagraph2, #yiv5531976351
li.yiv5531976351msolistparagraph2, #yiv5531976351
div.yiv5531976351msolistparagraph2
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 p.yiv5531976351msonormal3, #yiv5531976351
li.yiv5531976351msonormal3, #yiv5531976351 div.yiv5531976351msonormal3
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 p.yiv5531976351msochpdefault2, #yiv5531976351
li.yiv5531976351msochpdefault2, #yiv5531976351 div.yiv5531976351msochpdefault2
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 p.yiv5531976351msonormal11, #yiv5531976351
li.yiv5531976351msonormal11, #yiv5531976351 div.yiv5531976351msonormal11
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",
sans-serif;}#yiv5531976351 p.yiv5531976351msoacetate11, #yiv5531976351
li.yiv5531976351msoacetate11, #yiv5531976351 div.yiv5531976351msoacetate11
{margin:0in;margin-bottom:.0001pt;font-size:8.0pt;font-family:"Tahoma",
sans-serif;}#yiv5531976351 p.yiv5531976351msolistparagraph11, #yiv5531976351
li.yiv5531976351msolistparagraph11, #yiv5531976351
div.yiv5531976351msolistparagraph11
{margin-top:0in;margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",
sans-serif;}#yiv5531976351 p.yiv5531976351msochpdefault11, #yiv5531976351
li.yiv5531976351msochpdefault11, #yiv5531976351
div.yiv5531976351msochpdefault11
{margin-right:0in;margin-left:0in;font-size:10.0pt;font-family:"Times New
Roman", serif;}#yiv5531976351 span.yiv5531976351msohyperlink11
{color:#0563C1;text-decoration:underline;}#yiv5531976351
span.yiv5531976351msohyperlinkfollowed11
{color:#954F72;text-decoration:underline;}#yiv5531976351
span.yiv5531976351balloontextchar11 {font-family:"Tahoma",
sans-serif;}#yiv5531976351 span.yiv5531976351emailstyle2011
{font-family:"Calibri", sans-serif;color:windowtext;}#yiv5531976351
span.yiv5531976351emailstyle2111 {font-family:"Calibri",
sans-serif;color:windowtext;}#yiv5531976351 span.yiv5531976351emailstyle2211
{font-family:"Calibri", sans-serif;color:#1F497D;}#yiv5531976351
span.yiv5531976351emailstyle2311 {font-family:"Calibri",
sans-serif;color:windowtext;}#yiv5531976351 span.yiv5531976351emailstyle2411
{font-family:"Calibri", sans-serif;color:#1F497D;}#yiv5531976351
span.yiv5531976351emailstyle431 {font-family:"Calibri",
sans-serif;color:windowtext;}#yiv5531976351 span.yiv5531976351emailstyle441
{font-family:"Calibri", sans-serif;color:#1F497D;}#yiv5531976351
span.yiv5531976351EmailStyle60 {font-family:"Calibri",
sans-serif;color:#1F497D;}#yiv5531976351 .yiv5531976351MsoChpDefault
{font-size:10.0pt;} _filtered #yiv5531976351 {margin:1.0in 1.0in 1.0in
1.0in;}#yiv5531976351 div.yiv5531976351WordSection1 {}-->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]> 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]]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_UpdateInfoAS uiON ui.CI_ID= css.CI_ID INNERJOIN
v_CICategories_AllAS catON ui.CI_ID= cat.CI_ID AND ui.CI_UniqueIDNOTLIKE
'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 TotalPatchesReportedinnerjoin
v_R_System_ValidSYSon 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.
