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]