Search
Sponsors
Manually Quering Opsmgr Database
In some situations it’s nice to be able to query the OperationsManager manually. After setting up my lab en be able to change a health status of a dummy database. I ran a trace and searched for a clue where this healthstatus information is stored. Turns out that Microsoft has created some nice, userfriendly views (dbo.ManagedEntityGenericView and dbo.ManagedTypeView) that can be queried. theres plenty more to find but those 2 were enough to get the health status the way I wanted it. Using the following query you can see which monitor classes are being monitored for a certain object and which health state is has got. 1 being healty, 2 is a warning and 3 is an error;
SELECT MEGv.[Displayname] AS Object, TMv.[Name] AS MonitoringClass, MEGv.[Healthstate] FROM dbo.ManagedEntityGenericView AS MEGv
INNER JOIN dbo.[ManagedTypeView] AS TMv
ON TMv.[id] = MEGv.[MonitoringClassId]
WHERE MEGv.[Displayname] = 'YOUR OBJECT'
YOUR OBJECT can be anything, form a database name to a server name. Just any object name that can be monitored.
If you’d like to a more selective result and only want one specific monitorclass of one specific object you can use:
SELECT MEGv.[Displayname] AS Object, TMv.[Name] AS MonitoringClass, MEGv.[Healthstate] FROM dbo.ManagedEntityGenericView AS MEGv
INNER JOIN dbo.[ManagedTypeView] AS TMv
ON TMv.[id] = MEGv.[MonitoringClassId]
WHERE MEGv.[Displayname] = 'YOUR OBJECT'
AND TMv.[Name] = ‘YOUR MONITORCLASS'
YOUR MONITORCLASS is something like Microsoft.SQLServer.Database
(Use the first query to list all the monitor classes for a certain object)