Use OperationsManager

DECLARE @PrimaryServer VARCHAR(50)

SET @PrimaryServer = 'MyGateway'

;

WITH 

PrimaryRelation (SourceEntityId,agent,PrimaryServer,TargetEntityId)

AS

(

SELECT R.SourceEntityID,SourceBME.DisplayName as Agent,TargetBME.DisplayName as PrimaryServer, R.TargetEntityID

FROM Relationship R WITH (NOLOCK) 

JOIN BaseManagedEntity SourceBME 

ON R.SourceEntityID = SourceBME.BaseManagedEntityID 

JOIN BaseManagedEntity TargetBME 

ON R.TargetEntityID = TargetBME.BaseManagedEntityID 

WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication() 

AND TargetBME.IsDeleted <> '1'  -- AND THE PRIMARY SERVER IS NOT WAITING TO BE DELETED

AND SourceBME.IsDeleted <> '1' -- AND THE AGENT IS NOT WAITING TO BE DELETED

)

,

FailoverRelation (SourceEntityId,agent,FailoverServer,TargetEntityId)

AS

(

SELECT R.SourceEntityID,SourceBME.DisplayName as Agent,TargetBME.DisplayName as FailoverServer, R.TargetEntityID

FROM Relationship R WITH (NOLOCK) 

JOIN BaseManagedEntity SourceBME 

ON R.SourceEntityID = SourceBME.BaseManagedEntityID 

JOIN BaseManagedEntity TargetBME 

ON R.TargetEntityID = TargetBME.BaseManagedEntityID 

WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceSecondaryCommunication()

AND TargetBME.IsDeleted <> '1'  -- AND THE FAILOVER SERVER IS NOT WAITING TO BE DELETED

AND SourceBME.IsDeleted <> '1' -- AND THE AGENT IS NOT WAITING TO BE DELETED 

)

SELECT 

       MTV_HS.[DisplayName] as Agent

      ,PrimaryRelation.PrimaryServer

      ,FailoverServer =  -- FIELD RELATED TO 'LEFT OUTER JOIN' ON FailoverRelation TABLE

        CASE

        WHEN FailoverRelation.FailoverServer IS NULL THEN 'NO FAILOVER'

        ELSE FailoverRelation.FailoverServer

        END

      ,[Port]

      ,[InstallTime]

      ,[MaximumQueueSize]

      ,Patch = CASE 

        WHEN [PatchList] like '%UR4%' THEN 'RU4'

        WHEN [PatchList] like '%UR8%' THEN 'RU8'

        WHEN [PatchList] like '%UR11%' THEN 'RU11'

        WHEN [PatchList] = '' THEN '[NO_DATA]'

        END

      ,[IsManuallyInstalled]

      ,[Version]

      ,[ActionAccountIdentity]

      ,[ProxyingEnabled]

      ,[HeartbeatInterval]

      ,[NumberOfMissingHeartBeatsToMarkMachineDown_27AD2E30_EFE0_1A73_8C9D_F0A22B073227] as NumberOfMissingHeartBeatsToMarkMachineDown

      ,MTV_OS.DisplayName as OS

      ,MTV_OS.LogicalProcessors_5CAE4847_F75B_01D0_156E_1658D557B739 as Logic_CPU

      ,MTV_OS.CSDVersion_AFE62B62_74FC_2F06_D8A0_DEE31F14CD33 as ServicePack

  FROM [OperationsManager].[dbo].[MTV_HealthService] as MTV_HS

  INNER JOIN  [dbo].[MTV_Microsoft$Windows$OperatingSystem] as MTV_OS on MTV_HS.DisplayName = MTV_OS.PrincipalName

  INNER JOIN PrimaryRelation on PrimaryRelation.SourceEntityId = MTV_HS.BaseManagedEntityId

  -- THE LEFT OUTER JOIN CAN BE COMMENTED (AND RELATED FIELDS) TO DISPLAY ONLY THE PRIMARY SERVER)

  LEFT OUTER JOIN FailoverRelation on FailoverRelation.SourceEntityId = MTV_HS.BaseManagedEntityId

  WHERE IsAgent = '1'

  AND PrimaryServer like '%'+@PrimaryServer+'%'

  order by MTV_HS.[DisplayName]