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