ConfigMgr Client Hotfix Queries

Client hotfixes in ConfigMgr has become an often discussed topic as of late mainly because of the pre-R3 hotfix (977384) required on all managed systems. Microsoft recently published a good KB on this titled System Center Configuration Manager 2007 Hotfix Installation Guidance. Near the bottom of this KB they added this small query to help build collections to target the client hotfixes:

select * from SMS_R_System
inner join SMS_G_System_SMS_ADVANCED_CLIENT_STATE 
on SMS_G_System_SMS_ADVANCED_CLIENT_STATE.ResourceID = SMS_R_System.ResourceId
where SMS_R_System.ClientType = 1 
and SMS_G_System_SMS_ADVANCED_CLIENT_STATE.Name = "CcmFramework" 
and (SMS_G_System_SMS_ADVANCED_CLIENT_STATE.Version < "4.00.6487.2012"

Unfortunately, this query is not specific to any particular hotfix and the hotfix KBs themselves do not list which component that they update – although they do list version numbers if you know what to look for.

So my task was clear, figure out what components get updated version numbers. First, I used this little query to pull back all of the component versions in the environment I am currently working in:

SELECT
    AC.Name0, AC.Version0
FROM
    dbo.v_GS_SMS_ADVANCED_CLIENT_STAT AC
GROUP BY AC.Name0, AC.Version0
ORDER BY AC.Name0

And from there I was able to put together this little table for the following three client hotfixes (there aren’t a whole lot of client hotfixes so the table is short, but worth documenting and thus this blog post):

KB	Component	Version
977384	SmsPowerManagement	4.0.6487.2157
978754	SmsOSDeployment	4.0.6487.2115
2444668	SmsInventory	4.0.6487.2161

If you review the list of files affected in each of the above KBs, you will find the version numbers listed there although they are associated with files and not components in the KB.
Note that SmsPowerManagement does actually exist on the clients even if you haven’t installed 977384. This is because R3 functionality was actually shipped in SP2 and installing R3 merely enables it.
Clients with 977384 also have two other components bumped up in version:

Component	Version
CcmFramework	4.0.6487.2155
SmsSoftwareDistribution	4.0.6487.2154

I can’t find the above version numbers publically documented anywhere though.
Using this info and the query you (and I) can now target or report on client hotfix deployment.