Hi,

I got all the Network Devices and Windows Computers that currently monitoring in SCOM through the SQL Query from OperationsManager DB. but i am unable to find the Health State status of Windows Computers and Network Devices ..

Please guide which query/in which SQL Table i have to look for health state sta

use this SQL query

select healthstate=
case healthstate
when'1' then 'success'
else 'uninitialized'
end
, InMaintenanceMode=
case InMaintenanceMode
when '0' then 'false'
when '1' then 'true'
end
, displayname from managedentitygenericview;

For detail, pls. refer to

http://syscentercldman.blogspot.hk/2015/04/scom-db-insider-part-3.html

Roger

Hi Shailendra,

Following query will help you find the health state status of the windows computers. Hope, it should resolve your query.

----------------------------------------

select distinct a.[ActiveDirectorySite]
, a.NetworkName
, c.[IsAvailable]
, (SELECT  distinct ManagedTypeView.Name
                FROM       ManagedEntityGenericView
                INNER JOIN  ManagedTypeView ON ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.Id
                where ManagedTypeView.Name = 'microsoft.systemCenter.agent'
                and ManagedEntityGenericView.DisplayName = a.NetworkName)as IsAgentinstalled
FROM [MT_Computer] a
                inner JOIN [MT_Computer] b 
                                ON a.ActiveDirectorySite = b.ActiveDirectorySite 
                                AND a.NetbiosComputerName <> b.NetbiosComputerName
                inner JOIN ManagedEntityGenericView c 
                                ON a.[BaseManagedEntityId] = c.Id
WHERE 
a.ActiveDirectorySite <> 'NULL' 
and (a.[NetbiosComputerName] like '%%' or a.[NetbiosComputerName] like '%%') 
and b.ActiveDirectorySite <> 'NULL' and (b.[NetbiosComputerName] like '%%' 
or b.[NetbiosComputerName] like '%%')

------------------------------------------ Please mark the answer if you find it helpful.