/****** Scom Agent Extended Infos ******/
 
Use OperationsManager
 
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() 
)
,
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() 
)
 
 
SELECT 
                   MTV_HS.[DisplayName]
                  ,PrimaryRelation.PrimaryServer
                  ,FailoverServer
                  ,Failover_State = 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.CSDVersion_AFE62B62_74FC_2F06_D8A0_DEE31F14CD33 as ServicePack
                  ,MTV_OS.LogicalProcessors_5CAE4847_F75B_01D0_156E_1658D557B739 as Logic_CPU
                      
  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
  LEFT OUTER JOIN FailoverRelation on FailoverRelation.SourceEntityId = MTV_HS.BaseManagedEntityId
    
  WHERE IsAgent = '1'