Showing posts with label SQL queries. Show all posts
Showing posts with label SQL queries. Show all posts

Friday, March 2, 2012

Some Helpful SCOM Queries

The below is 2 SQL queries that I use to work around some limitations in SCOM 2007 R2.
To do these, changes are required on the SCOM OprsMgr Database hence sysadmin rights is required on the database.

Have you ever found that the agent is still displayed as grayed out in the computer’s view of the SCOM Console after the agent has been uninstalled and deleted from the list of agent managed servers?
To remove those, you may use the statement below

Remove state servers from computer view after removal of agents

UPDATE [OperationsManager].[dbo].[BaseManagedEntity] SET [IsDeleted] = 1   WHERE [DisplayName] LIKE 'Server FQDN Name’

Though manually SCOM agents to server is not a good practice, there are times that it has to be done this way for whatever operational reasons.
The manually installed agents comes with short comings such as
-       Not being able to be managed from the SCOM console
o    Able to change Management Server
o    Not automatically update to the latest CU whenever you perform an upgrade on the Management Servers.
The query below will enable the to be converted to be remotely manageable despite being installed manually.

Convert manually installed agent to remotely manageable

UPDATE MT_HealthService
SET IsManuallyInstalled=0
WHERE IsManuallyInstalled=1
AND BaseManagedEntityId IN
(select BaseManagedEntityID from BaseManagedEntity
where BaseManagedTypeId = 'AB4C891F-3359-3FB6-0704-075FBFE36710'
AND DisplayName =’Server FQDN Name’)