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