Menu Close

SCOM Outages and Maintenance Report

Download:  https://kevinholman.com/files/OutageAndMaintenanceReport.zip

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:

 

image

 

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

8 Comments

  1. Scott

    Will this work in 2012 R2? Was there an .rpt attached to this that got lost when you had to move your content? I could sure use this, even better would be a configurable AlertName.

  2. Ronald

    For those asking if this works on 2016, yes and also on 2019. But after uploading the rdl file you need to change the datasource to your shared datawarehouse datasource. (report, manage, Data sources, Connect to a shared data source, choose data warehouse main)

Leave a Reply

Your email address will not be published.