Le blog technique

Toutes les astuces #tech des collaborateurs de PI Services.

#openblogPI

Voir tous les articles

Retrouvez les articles à la une

SCOM – Requête SQL des agents gérés par une gateway spécifique

par | 27 Sep 2017 | , ,

La requête SQL ci-dessous affiche les agents gérés par une Gateway spécifique ainsi que le failover configuré ou non.

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]

SCOM – Requête SQL de toutes les instances d’objet pour une machine

par | 27 Sep 2017 |

La requête ci-dessous liste toute les instances d’objets qui se rapporte a une machine donnée, avec leur état ainsi que les infos sur un éventuel mode maintenance en cours sur l’objet.

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

 

SCOM – Requête des heartbeat failure pour les membres d’un groupe spécifique

par | 27 Sep 2017 |

La requête ci-dessous, a exécuter sur la base de reporting, liste les alertes “Health Service Heartbeat Failure” pour les membres d’un groupe donné (@computergroup1) sur une plage de temps donnée.

-- QUERY TO GET 'Health Service Heartbeat Failure' ALERTS FOR COMPUTERS THAT ARE MEMBERS OF A SPECIFIC GROUP (@computergroup1)

-- MODIFY @computergroup1 AND @startdate/@enddate

-- BEWARE THAT @computergroup1 NAME CHARACTERS NUMBER CAN BE HOLD BY VARCHAR(N) VARIABLE

 

Use OperationsManagerDW

 

DECLARE @startdate    datetime

DECLARE @enddate    datetime

DECLARE @computergroup1    VARCHAR(100)

 

SET @startdate = '2017-09-01 00:00:00'

SET @enddate = '2017-09-30 00:00:00'

SET @computergroup1 = 'MyGroup'

 

 

SELECT 

av.AlertGuid

 

,apv.ParameterValue as SystemName

,av.AlertName

,adv.TicketId

,(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.RaisedDateTime)) as DownDateTime 

,(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),arsv.StateSetDateTime)) as RestoredDateTime

,adv.CustomField2 as OutageType 

,adv.CustomField3 as RootCause 

,Alert_ResolutionState = CASE

    WHEN arsv.ResolutionState = '255' THEN 'Closed'

    END

,adv.DBLastModifiedByUserId as UserID

FROM Alert.vAlert av

JOIN Alert.vAlertDetail adv on av.AlertGuid = adv.AlertGuid

JOIN Alert.vAlertResolutionState arsv on av.AlertGuid = arsv.AlertGuid

JOIN Alert.vAlertParameter apv on av.AlertGuid = apv.AlertGuid

 

 

-- JOIN ON TEMPORARY TABLE THAT RETRIEVE MAX OF StateSetDateTime (restoretime)

JOIN    (

        select av.AlertGuid 

        ,(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.RaisedDateTime)) as DownDateTime 

        ,MAX(arsv.StateSetDateTime) as restoretime

        from Alert.vAlert av

        JOIN Alert.vAlertDetail adv on av.AlertGuid = adv.AlertGuid

        JOIN Alert.vAlertResolutionState arsv on av.AlertGuid = arsv.AlertGuid

        JOIN Alert.vAlertParameter apv on av.AlertGuid = apv.AlertGuid

        GROUP BY av.AlertGuid,av.RaisedDateTime

        ) temp on temp.AlertGuid = av.AlertGuid

 

 

 

WHERE AlertName = 'Health Service Heartbeat Failure'

AND arsv.ResolutionState = '255'

AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.RaisedDateTime)) between @startdate and @enddate

 

 

 

and apv.ParameterValue IN (

SELECT vManagedEntity.DisplayName

FROM  vManagedEntity 

INNER JOIN vRelationship ON vManagedEntity.ManagedEntityRowId = vRelationship.TargetManagedEntityRowId 

INNER JOIN vManagedEntity AS ManagedEntity_1 ON vRelationship.SourceManagedEntityRowId = ManagedEntity_1.ManagedEntityRowId

    WHERE (ManagedEntity_1.DisplayName = @computergroup1)

)

 

 

AND arsv.StateSetDateTime = temp.restoretime

AND adv.TicketId is not null

 

GROUP BY apv.ParameterValue,adv.TicketId,av.AlertGuid,av.AlertName,av.RaisedDateTime,arsv.StateSetDateTime,adv.CustomField2,adv.CustomField3,/*adv.CustomField4,*/arsv.ResolutionState,adv.DBLastModifiedByUserId

ORDER BY apv.ParameterValue

 

 

Derniers articles

Catégories

Archives

Auteurs/Autrices