PI Services

Le blog des collaborateurs de PI Services

SCOM – SQL – Requete d’info etendue sur les agents Scom

La requete suivante affiche des infos etendues sur les agents Scom, avec le détails des Primary et Failover Servers.

 

/****** 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'

Ajouter un commentaire

Loading