PI Services

Le blog des collaborateurs de PI Services

SCOM – SQL – Requete des Unreachable Computer pour un groupe specifique

La requete sql suivante affiche les evenement de type Unreachable Computer pour les machines membres d’un groupe spécifique, pour une période donnée, avec les infos de cause et de User associés.

 

/**** Unreachable Computer Event from Specific Group  ****/   Use OperationsManagerDW   DECLARE @startdate     datetime DECLARE @enddate      datetime DECLARE @computergroup1     VARCHAR(50)   SET @startdate = '2017-01-01 00:00:00' SET @enddate = '2017-01-31 00:00:00' SET @computergroup1 = 'My_Group'     SELECT apv.ParameterValue as SystemName ,(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, adv.CustomField4 as Reason, 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 WHERE AlertName = 'Failed to Connect to Computer' AND arsv.ResolutionState = '255' --AND adv.CustomField2 IS NOT NULL 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) )   UNION ALL   SELECT vme.displayname, (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vmm.StartDateTime)) as DownDateTime, (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vmm.EndDateTime)) as RestoredDateTime, 'OutageType' = CASE   vmm.PlannedMaintenanceInd   WHEN '1' THEN 'Scheduled'   WHEN '0' THEN 'Unscheduled' END, 'RootCause' = CASE   vmmh.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, vmmh.Comment as Reason, vmmh.userid as UserID FROM vMaintenanceMode vmm JOIN vManagedEntity vme on vmm.managedentityrowid = vme.managedentityrowid JOIN vMaintenanceModeHistory vmmh on vmm.maintenancemoderowid = vmmh.maintenancemoderowid WHERE vme.FullName LIKE '%HealthServiceWatcher%' AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vmm.StartDateTime)) between @startdate and @enddate AND vme.displayname 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) ) ORDER BY DownDateTime DESC

Ajouter un commentaire

Loading