and another thing... the reason I pulled the =31 was because in my 1511 lab
(with 3 whole clients), there were zero results. and I didn't bother to check
why/why not at the time. but on my production cm12 (sadly, still cm12--we'll
upgrade soonish) it worked with the =31
In my installed-without-upgrading 1511 lab, it was never cm12; the
categoryinstanceid of 31 doesn't exist. It's a completely different number
which means "software updates". so if we want this to work for everyone, don't
hard code that number. try this... make sure it still works in cm12 and 1511
(or higher). Of course... I'm in American English. It's possible that other
languages in that view it's not called "Security Updates". so this probably
still isn't standard everywhere. But it might be closer...
with TotalPatchesReported as (SELECT css.ResourceID, COUNT(css.ResourceID) AS
PatchCountFROM 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 =
(Select top 1 CategoryInstanceID from v_CICategoryInfo_All where
CategoryInstanceName = 'Security Updates')GROUP BY css.ResourceID)select
TotalPatchesReported.ResourceID, sys.Netbios_Name0,
sys.Operating_System_Name_and0, sys.Client_Version0,
TotalPatchesReported.PatchCountfrom TotalPatchesReported inner join
v_R_System_Valid SYS on TotalPatchesReported.ResourceID = sys.ResourceIDwhere
TotalPatchesReported.PatchCount < 60order by PatchCount
On Wednesday, February 10, 2016 4:29 PM, Sherry Kissinger
<slkissin...@sbcglobal.net> wrote:
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
<rspinell...@outlook.com> wrote:
#yiv2359918132 #yiv2359918132 -- filtered {font-family:Helvetica;panose-1:2
11 6 4 2 2 2 2 2 4;}#yiv2359918132 filtered {panose-1:2 4 5 3 5 4 6 3 2
4;}#yiv2359918132 filtered {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2
4;}#yiv2359918132 filtered {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2
4;}#yiv2359918132 filtered {font-family:Consolas;panose-1:2 11 6 9 2 2 4 3 2
4;}#yiv2359918132 p.yiv2359918132MsoNormal, #yiv2359918132
li.yiv2359918132MsoNormal, #yiv2359918132 div.yiv2359918132MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv2359918132 a:link,
#yiv2359918132 span.yiv2359918132MsoHyperlink
{color:blue;text-decoration:underline;}#yiv2359918132 a:visited, #yiv2359918132
span.yiv2359918132MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv2359918132
p.yiv2359918132msoacetate, #yiv2359918132 li.yiv2359918132msoacetate,
#yiv2359918132 div.yiv2359918132msoacetate
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv2359918132
p.yiv2359918132msolistparagraph, #yiv2359918132
li.yiv2359918132msolistparagraph, #yiv2359918132
div.yiv2359918132msolistparagraph
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv2359918132
p.yiv2359918132msonormal, #yiv2359918132 li.yiv2359918132msonormal,
#yiv2359918132 div.yiv2359918132msonormal
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv2359918132
p.yiv2359918132msochpdefault, #yiv2359918132 li.yiv2359918132msochpdefault,
#yiv2359918132 div.yiv2359918132msochpdefault
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv2359918132
p.yiv2359918132msonormal1, #yiv2359918132 li.yiv2359918132msonormal1,
#yiv2359918132 div.yiv2359918132msonormal1
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv2359918132
p.yiv2359918132msoacetate1, #yiv2359918132 li.yiv2359918132msoacetate1,
#yiv2359918132 div.yiv2359918132msoacetate1
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv2359918132
p.yiv2359918132msolistparagraph1, #yiv2359918132
li.yiv2359918132msolistparagraph1, #yiv2359918132
div.yiv2359918132msolistparagraph1
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv2359918132
p.yiv2359918132msochpdefault1, #yiv2359918132 li.yiv2359918132msochpdefault1,
#yiv2359918132 div.yiv2359918132msochpdefault1
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv2359918132
span.yiv2359918132msohyperlink {}#yiv2359918132
span.yiv2359918132msohyperlinkfollowed {}#yiv2359918132
span.yiv2359918132balloontextchar {}#yiv2359918132
span.yiv2359918132msohyperlink1 {}#yiv2359918132
span.yiv2359918132msohyperlinkfollowed1 {}#yiv2359918132
span.yiv2359918132balloontextchar1 {}#yiv2359918132
span.yiv2359918132emailstyle201 {}#yiv2359918132
span.yiv2359918132emailstyle211 {}#yiv2359918132
span.yiv2359918132emailstyle221 {}#yiv2359918132
span.yiv2359918132emailstyle231 {}#yiv2359918132
span.yiv2359918132emailstyle241 {}#yiv2359918132 span.yiv2359918132emailstyle43
{}#yiv2359918132 span.yiv2359918132emailstyle44 {}#yiv2359918132
p.yiv2359918132msonormal2, #yiv2359918132 li.yiv2359918132msonormal2,
#yiv2359918132 div.yiv2359918132msonormal2
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv2359918132
span.yiv2359918132msohyperlink2
{color:blue;text-decoration:underline;}#yiv2359918132
span.yiv2359918132msohyperlinkfollowed2
{color:purple;text-decoration:underline;}#yiv2359918132
p.yiv2359918132msoacetate2, #yiv2359918132 li.yiv2359918132msoacetate2,
#yiv2359918132 div.yiv2359918132msoacetate2
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv2359918132
span.yiv2359918132balloontextchar2 {}#yiv2359918132
p.yiv2359918132msolistparagraph2, #yiv2359918132
li.yiv2359918132msolistparagraph2, #yiv2359918132
div.yiv2359918132msolistparagraph2
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv2359918132
p.yiv2359918132msonormal3, #yiv2359918132 li.yiv2359918132msonormal3,
#yiv2359918132 div.yiv2359918132msonormal3
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv2359918132
p.yiv2359918132msochpdefault2, #yiv2359918132 li.yiv2359918132msochpdefault2,
#yiv2359918132 div.yiv2359918132msochpdefault2
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv2359918132
p.yiv2359918132msonormal11, #yiv2359918132 li.yiv2359918132msonormal11,
#yiv2359918132 div.yiv2359918132msonormal11
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;}#yiv2359918132
p.yiv2359918132msoacetate11, #yiv2359918132 li.yiv2359918132msoacetate11,
#yiv2359918132 div.yiv2359918132msoacetate11
{margin:0in;margin-bottom:.0001pt;font-size:8.0pt;}#yiv2359918132
p.yiv2359918132msolistparagraph11, #yiv2359918132
li.yiv2359918132msolistparagraph11, #yiv2359918132
div.yiv2359918132msolistparagraph11
{margin-top:0in;margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt;font-size:11.0pt;}#yiv2359918132
p.yiv2359918132msochpdefault11, #yiv2359918132
li.yiv2359918132msochpdefault11, #yiv2359918132
div.yiv2359918132msochpdefault11
{margin-right:0in;margin-left:0in;font-size:10.0pt;}#yiv2359918132
span.yiv2359918132msohyperlink11
{color:#0563C1;text-decoration:underline;}#yiv2359918132
span.yiv2359918132msohyperlinkfollowed11
{color:#954F72;text-decoration:underline;}#yiv2359918132
span.yiv2359918132balloontextchar11 {}#yiv2359918132
span.yiv2359918132emailstyle2011 {color:windowtext;}#yiv2359918132
span.yiv2359918132emailstyle2111 {color:windowtext;}#yiv2359918132
span.yiv2359918132emailstyle2211 {color:#1F497D;}#yiv2359918132
span.yiv2359918132emailstyle2311 {color:windowtext;}#yiv2359918132
span.yiv2359918132emailstyle2411 {color:#1F497D;}#yiv2359918132
span.yiv2359918132emailstyle431 {color:windowtext;}#yiv2359918132
span.yiv2359918132emailstyle441 {color:#1F497D;}#yiv2359918132
span.yiv2359918132EmailStyle60 {color:#1F497D;}#yiv2359918132
.yiv2359918132MsoChpDefault {font-size:10.0pt;}#yiv2359918132 filtered
{margin:1.0in 1.0in 1.0in 1.0in;}#yiv2359918132 div.yiv2359918132WordSection1
{}#yiv2359918132 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: listsadmin@lists.myitforum.com
[mailto:listsadmin@lists.myitforum.com]On Behalf Of Sherry Kissinger
Sent: Wednesday, February 10, 2016 3:38 PM
To: ms...@lists.myitforum.com
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
<rspinell...@outlook.com> 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:listsadmin@lists.myitforum.com
[mailto:listsadmin@lists.myitforum.com]On Behalf Of Daniel Ratliff
Sent: Wednesday, February 10, 2016 12:38 PM
To: ms...@lists.myitforum.com
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:listsadmin@lists.myitforum.com [mailto:listsadmin@lists.myitforum.com]On
Behalf Of Sherry Kissinger
Sent: Wednesday, February 10, 2016 12:20 PM
To: ms...@lists.myitforum.com
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 <rspinell...@outlook.com> 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:listsadmin@lists.myitforum.com
[mailto:listsadmin@lists.myitforum.com]On Behalf Of Daniel Ratliff
Sent: Wednesday, February 10, 2016 11:29 AM
To: ms...@lists.myitforum.com
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:listsadmin@lists.myitforum.com [mailto:listsadmin@lists.myitforum.com]On
Behalf Of Robert Spinelli
Sent: Wednesday, February 10, 2016 11:26 AM
To: ms...@lists.myitforum.com
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:listsadmin@lists.myitforum.com
[mailto:listsadmin@lists.myitforum.com]On Behalf Of Daniel Ratliff
Sent: Wednesday, February 10, 2016 9:10 AM
To: ms...@lists.myitforum.com
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:listsadmin@lists.myitforum.com
[mailto:listsadmin@lists.myitforum.com]On Behalf Of Robert Spinelli
Sent: Wednesday, February 10, 2016 8:29 AM
To: ms...@lists.myitforum.com
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.
