I was recently researching this very issue and found this very useful blog post originally made by Kevin Holman. To add to this, I would like to mention that the roadmap plans for OpsMgr have changed and there is no longer an SP2 scheduled to be released but the entire product will be released as an R2.
The original post may be found here:
http://blogs.technet.com/kevinholman/archive/2008/06/24/how-do-i-know-which-hotfixes-have-been-applied-to-which-agents.aspx
Here are the details:
How do I know which hotfixes have been applied to which agents?
As more hot-fixes are applied to our OpsMgr 2007 SP1 environments.... how can we know which hot-fixes have been applied to our agents? How can we detect an agent that needs patching but got missed?
In MOM 2005... this was rather simple... in the Admin console, under Agent-managed Computers, there was a column called "version" which incremented the agent version number in most cases.
In OpsMgr... we do not update this field in the Administration tab. See graphic: The version here shows the major version number... like RTM 6.0.6500, SP1 6.0.6278.... etc....
So.... how do we examine this now for minor updates?
Create a new State view. Call it "Custom - Agent Patch List" or something you like. Target "Health Service" for "Show Data Related To". You can filter it further to the "Agent Managed Computer Group".
Then - personalize this view, and show the columns for "Name" and "Patch List" See graphic:
Now.... the "Patch List" column isn't super user friendly - because of the amount of text in the single column.... but it will let you see what has been installed. For instance - here is an example of KB950853 installed:
To make this a bit easier.... I wrote the following SQL query which does essentially the same thing.... you can create a web based SQL report from this and the data will be much easier to manage in Excel:
select bme.path AS 'Agent Name', hs.patchlist AS 'Patch List' from MT_HealthService hs
inner join BaseManagedEntity bme on hs.BaseManagedEntityId = bme.BaseManagedEntityId
order by path
If you want to query for all agents missing a specific hot-fix... you could run a query like this.... just change the KB number below (thanks to Brad Turner for providing the idea):
select bme.path AS 'Agent Name', hs.patchlist AS 'Patch List' from MT_HealthService hs
inner join BaseManagedEntity bme on hs.BaseManagedEntityId = bme.BaseManagedEntityId
where hs.patchlist not like '%951380%'
order by path
I have noticed, however, that this field, "Patch List" is limited to 255 characters in the database.... which I imagine will run out of space fairly soon. I will also be interested to see how we handle this table column, once SP2 comes out.... as any pre-SP2 applied hotfixes will no longer apply.
The Patch List information is discovered and updated once per day across all agents in the management group.
For a report which shows you the same information, but lets you query for all agent missing a specific hotfix - check out my more recent post with the report download:
http://blogs.technet.com/kevinholman/archive/2008/06/27/a-report-to-show-all-agents-missing-a-specific-hotfix.aspx
For great, in-depth, technical training on System Center, check out www.infrontconsulting.com/events.htm.