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:
<!--#yiv1785888839 _filtered #yiv1785888839 {font-family:"Cambria
Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv1785888839
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv1785888839
{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} _filtered #yiv1785888839
{font-family:Consolas;panose-1:2 11 6 9 2 2 4 3 2 4;}#yiv1785888839
#yiv1785888839 p.yiv1785888839MsoNormal, #yiv1785888839
li.yiv1785888839MsoNormal, #yiv1785888839 div.yiv1785888839MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",
sans-serif;}#yiv1785888839 a:link, #yiv1785888839
span.yiv1785888839MsoHyperlink
{color:#0563C1;text-decoration:underline;}#yiv1785888839 a:visited,
#yiv1785888839 span.yiv1785888839MsoHyperlinkFollowed
{color:#954F72;text-decoration:underline;}#yiv1785888839
p.yiv1785888839MsoAcetate, #yiv1785888839 li.yiv1785888839MsoAcetate,
#yiv1785888839 div.yiv1785888839MsoAcetate
{margin:0in;margin-bottom:.0001pt;font-size:8.0pt;font-family:"Tahoma",
sans-serif;}#yiv1785888839 p.yiv1785888839MsoListParagraph, #yiv1785888839
li.yiv1785888839MsoListParagraph, #yiv1785888839
div.yiv1785888839MsoListParagraph
{margin-top:0in;margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",
sans-serif;}#yiv1785888839 span.yiv1785888839BalloonTextChar
{font-family:"Tahoma", sans-serif;}#yiv1785888839
span.yiv1785888839EmailStyle20 {font-family:"Calibri",
sans-serif;color:windowtext;}#yiv1785888839 span.yiv1785888839EmailStyle21
{font-family:"Calibri", sans-serif;color:windowtext;}#yiv1785888839
span.yiv1785888839EmailStyle22 {font-family:"Calibri",
sans-serif;color:#1F497D;}#yiv1785888839 span.yiv1785888839EmailStyle23
{font-family:"Calibri", sans-serif;color:windowtext;}#yiv1785888839
span.yiv1785888839EmailStyle24 {font-family:"Calibri",
sans-serif;color:#1F497D;}#yiv1785888839 .yiv1785888839MsoChpDefault
{font-size:10.0pt;} _filtered #yiv1785888839 {margin:1.0in 1.0in 1.0in
1.0in;}#yiv1785888839 div.yiv1785888839WordSection1 {}-->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_validAS sys INNER JOIN
v_UpdateComplianceStatusAS css ON sys.ResourceID = css.ResourceIDINNER JOIN
v_AgentDiscoveriesAS agt ON sys.ResourceID =
agt.ResourceIdINNER JOIN
v_RA_System_SMSAssignedSitesAS site ON sys.ResourceID= site.ResourceIDINNER
JOIN v_GS_WORKSTATION_STATUSAS ws ON sys.ResourceID =
ws.ResourceIDLEFT OUTER JOIN v_UpdateInfoAS ui ON
ui.CI_ID= css.CI_ID INNER JOIN 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' 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.
