If you’ve ever had a dead a System Center Operations Manager 2007 SP1 Agent, you know that removing them from the console can be an issue. This can be a particular problem if you have agents that you cannot uninstall (the machines no longer exist), they self assigned themselves to the management group via Active Directory Integration (Domain Controllers that can read the site published in Active Directory), or permissions to the agent server for the Agent Action Account have been removed. Many times these agents will only show up under Monitoring, and won’t appear at all in the Administration console.
So how do you remove these agents from showing up in the console as “Not Monitored”?
Do NOT use this method to remove the agent from the DB, unless you have no other way of uninstalling the agent or deleting the agent inside the console. This method is only for removing agents that are permanently offline and you cannot uninstall the agent inside the OpsMgr 2007 console. These queries will update several tables. Use at your own risk.
(1) Get the FQDN of the dead agent you want to remove. Close all open OpsMgr 2007 SP1 consoles.
(2) Open the SQL Management Studio Console.
(3) In a new SQL query Use OperationsManager
(4) Select TopLevelHostEntityId from basemanagedentity where Name like 'servername.domainname.com'
(5) Take the returned data (It should look something like ‘BBF1DC38-3F1E-7CD9-532E-E4E34V558CDA’. Yours will be different.
(6) Now in a new query enter Update BaseManagedEntity Set Isdeleted=1 where TopLevelHostEntityId =‘BBF1DC38-3F1E-7CD9-532E-E4E34V558CDA’. This query will forcibly remove the agent from the DB.
(7) Re-open the OpsMgr 2007 SP1 Console – the dead agent servername.domainname.com will be gone.
even if it is a lot more painful and time consuming, it is much better to go thru the disabling of a number of discoveries for the objects that had been discovered on that agent, and then use remove-disabledmonitoringobject from powershell: http://blogs.technet.com/jonathanalmquist/archive/2008/09/14/remove-disabledmonitoringobject.aspx
making the change you suggest directly in the database can be extremely dangerous - potentially rendering the configuration invalid/incostintent and thus preventing the Config service from reading it. Which is a lot bigger issue than the one you were trying to solve...