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]] 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.
