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 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 TotalPatchesReported.ResourceID, sys.Netbios_Name0,
sys.Operating_System_Name_and0, sys.Client_Version0,
TotalPatchesReported.PatchCount
from TotalPatchesReported inner join
v_R_System_Valid SYS on TotalPatchesReported.ResourceID = sys.ResourceID
where TotalPatchesReported.PatchCount < 60
order by 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]<mailto:[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]>
[mailto:[email protected]] On Behalf Of Daniel Ratliff
Sent: Wednesday, February 10, 2016 11:29 AM
To: [email protected]<mailto:[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]>
[mailto:[email protected]] On Behalf Of Robert Spinelli
Sent: Wednesday, February 10, 2016 11:26 AM
To: [email protected]<mailto:[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]<mailto:[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?)
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]>
[mailto:[email protected]] On Behalf Of Robert Spinelli
Sent: Wednesday, February 10, 2016 8:29 AM
To: [email protected]<mailto:[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.
