Outages and Maintenance Report
This is a little report I put together on request.
This report will query the data warehouse, and show all the outages, and maintenance, for objects in specific groups.
The outages look at all the “Failed to Connect to Computer” alerts, and list the start and end time of the outage, based on the time the alert was created, to the time it was closed (assumes it is auto-closed by the agent coming back online)
The maintenance looks at the times that the Health Service Watcher objects are placed into maintenance mode.
There is a start and end time parameters for the report – and the report defaults to the last 30 days.
There is a group choice parameter – you should pick a group that contains Health Service Watcher objects.
It looks like this:
It is based on this query, which you can tune to meet your needs:
declare @startdate datetime
declare @enddate datetime
declare @computergroups varchar(50)
SET @startdate = '2009-06-01 00:00:00.000'
SET @enddate = getutcdate()
set @computergroups = 'Agent Managed Computer 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 = @computergroups)
)
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 = @computergroups)
)
ORDER BY DownDateTime
The report is attached below.
