Use OperationsManager
DECLARE @TargetComputer VARCHAR(50)
set @TargetComputer = 'MyServer'
SELECT
MTV.DisplayName as ClassName
,MEGV.Path as Instance_Path
,MEGV.[DisplayName] as 'Entity_DisplayName'
,MEGV.[Name] as 'Entity_Name'
,MEGV.[FullName] as Entity_FullName
,[IsManaged]
,[IsDeleted]
,HealthState =
CASE WHEN InMaintenanceMode = '0'
THEN
CASE [HealthState]
WHEN '0' THEN 'Not Monitored'
WHEN '1' THEN 'OK'
WHEN '2' THEN 'Warning'
WHEN '3' THEN 'Critical'
END
WHEN InMaintenanceMode = '1'
THEN
CASE [HealthState]
WHEN '0' THEN 'In Maintenance Mode'
WHEN '1' THEN 'OK'
WHEN '2' THEN 'Warning'
WHEN '3' THEN 'Critical'
END
END
,Is_Available =
CASE [IsAvailable]
WHEN '1' THEN 'YES'
WHEN '2' THEN 'NO'
END
,In_MaintenanceMode =
CASE [InMaintenanceMode]
WHEN '0' THEN 'NO'
WHEN '1' THEN 'YES'
END
,Start_Of_Maintenance =
CASE WHEN InMaintenanceMode = '0'
THEN null
ELSE MMV.StartTime
END
,End_Of_Maintenance =
CASE WHEN InMaintenanceMode = '0'
THEN null
ELSE MMV.ScheduledEndTime
END
,Maintenance_RootCause =
CASE WHEN InMaintenanceMode = '0'
THEN null
ELSE
CASE MMV.ReasonCode
WHEN '0' THEN 'Other (Planned)'
WHEN '1' THEN 'Other (Unplanned)'
WHEN '2' THEN 'Hardware: Maintenance (Planned)'
WHEN '3' THEN 'Hardware: Maintenance (Unplanned)'
WHEN '4' THEN 'Hardware: Installation (Planned)'
WHEN '5' THEN 'Hardware: Installation (Unplanned)'
WHEN '6' THEN 'Operating System: Reconfiguration (Planned)'
WHEN '7' THEN 'Operating System: Reconfiguration (Unplanned)'
WHEN '8' THEN 'Application: Maintenance (Planned)'
WHEN '9' THEN 'Application: Maintenance (Unplanned)'
WHEN '10' THEN 'Application: Installation (Planned)'
WHEN '11' THEN 'Application: Unresponsive'
WHEN '12' THEN 'Application: Unstable'
WHEN '13' THEN 'Security Issue'
WHEN '14' THEN 'Loss of network connectivity (Unplanned)'
END
END
,Maintenance_Reason =
CASE WHEN InMaintenanceMode = '0'
THEN null
ELSE MMV.Comments
END
FROM [OperationsManager].[dbo].[ManagedEntityGenericView] MEGV
INNER JOIN [dbo].[ManagedTypeView] MTV on MEGV.MonitoringClassId = MTV.Id
INNER JOIN [OperationsManager].[dbo].[MaintenanceModeView] MMV on MEGV.id = MMV.BaseManagedEntityId
WHERE (MEGV.Name like '%'+@TargetComputer+'%' OR MEGV.DisplayName like '%'+@TargetComputer+'%' OR MEGV.Path like '%'+@TargetComputer+'%')
and MTV.LanguageCode = 'ENU'
and MEGV.HealthState is not null
and MEGV.IsDeleted <> '1'
ORDER BY MTV.DisplayName