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:


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


The report is attached below.

Outage and Maintenance Report.zip

Comments (25)

  1. Kevin Holman says:

    Each data set references my custom data source…. so you would need to edit your modified RDL and change each dataset section to reference your datasource name.

    Sorry about that…. I totally didnt think to make this work directly on import…. using the built in warehouse data source.  I will fix it if I get some time.

    1. Mohamed says:

      Can you help me to get the server count in Maintenance Mode older than 7 days in SCOM 2012 using powershell command.

  2. Anonymous says:

    How would i modify the query to return data for the last 24 hours so i can schedule this report? Many Thanks!

  3. Kevin Holman says:

    That is correct Ravi.  I wrote this originally for a customer request, and I was having them add data into Custom frield 2,3, and 4:

    adv.CustomField2 as OutageType,

    adv.CustomField3 as RootCause,

    adv.CustomField4 as Reason,

    This way – when they got a computer down alert that was unexpected – their business process was to fill the data into these custom fields on those alerts, then…. if the server once investigates was going to be down for an extended period – they would place the server into unplanned maintenance mode if desired.

  4. Anonymous says:

    Who can edit it for SCOM 2012? The Maitenance window table i think was changed. I do not get any maitenance window report.

  5. nomad says:

    Just ran it against some servers to test.  They were showing down for a total of around 3 hours over the last month and a half.

    When we ran the availablity report against the HSWatcher for the same servers they showed 100% availability until we drilled down into the monthly view and saw May @ 99.86% and June @ 99.93% available.

    I thought it was interesting.

  6. Nathan says:

    Thanks.  This report works like a charm and the bean-counters love it!

  7. Matthew Brown says:

    Hi… has any one had a problem running the report after import. I modified the data source to match our environment, but am getting this error when I run the report: "An error has occurred during report processing. (rsProcessingAborted)

    Query execution failed for data set ‘ComputerGroups’. (rsErrorExecutingCommand)

    For more information about this error navigate to the report server on the local server machine, or enable remote errors".

  8. Matthew Brown says:

    Kevin, Thank you so much for the rapid response. That was exacly my issue – thought I entered the correct data set and data source strings, but found a typo with the initial catalog portion of the string. Once I corrected the issue the report runs great. Your blog is great and your depth of knowledge of all things OpsMgr is amazing.

  9. StDenis says:

    Sorry, probably stupid question. How import this report to SCOM ?

  10. StDenis says:

    Question revoke. Nice report, thanks.

  11. ryan says:

    This report would be extremely helpful. Can you please tell me how to run this report? I dont mind doing the research I just need to have access to documentation. Point me in the direction and I will find it.

  12. Chris says:

    Great report!!  One question, how can I tailor it so I can exlcude weekends and only core hours??

    Thanks  

  13. Dan_IT says:

    The Custom Fields from The AlertDetail table are comming up as NULL, how can I set this up to show outageType, Reasaon, etc.?

  14. Daniel D'Amato says:

    Report works beautifully!

    Only issue i've found is that it only works for windows devices.

    I can't see the maintenance mode for SNMP devices.

    Can anyone think of a way to get it to work for SNMP devices aswell?

  15. Ravi says:

    Just to add that;

    The entries (we have in the report, in the snapshot above as well) where Outage,Root Cause and Reason is BLANK and UserID is 'System' are the entries which shows the grayed out period of that agent. This is not exactly a maintenance windows of the agent instaed it is the time when agent was not communicating to RMS/MS.

    Kevin: could u pl. confirm this?

    Regards,

    Ravi

  16. Ravi says:

    Sorry !!

    I didn't read the query completely. Blank rows are showing the outage only.

    Ravi

  17. Byty says:

    Hi,

    Can you help me adding the TiketID.

    If I add the line: adv.TicketId as TiketNr  I get an error meessage

    "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

    So.. I think i need to add this in the UNION part ( because without that.. the script is runing)

    Thank you.

  18. Althaf says:

    Sorry to ask.. Can some one please let me know how to run this query?

    I am new to this and in learning process.

  19. Dominique says:

    Hello,

    I was able to change the query to use the "Today's date " – 42 by using SET @startdate =(Select Dateadd(day, -42, Getdate())) /* Today minus 6 wekks = 42 days */

    but when running the report from the SCOM Console > reporting > Scheduled Reports > it still refers to a fix date in 2009???

    Anyway toc change this?

    Thanks,

    Dom

  20. Dynamic Group are not Visible in the Outage & Maintenance Report says:

    Hi Kevin . I would like to thank you for this wonderful work for getting the Outage & maintenance report , however it seem that the dynamic group created in the SCOM Console are not visible in this reports. do you have anything on top of your mind which can add that in this report.

  21. Karthick Kesavan says:

    Hi Kevin,

    i have some small query in SQL query Regarding this line (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.RaisedDateTime)) as DownDateTime,

    suppose consider raiseddate time is morning 6AM and i am adding 6 hours to the raiseddatetime means how it will be downtime since it may chnage the state @ 8 am means i am running the query @ 10 AM it will show downtime as 4 hours but issue resolved in 2 hours
    ?

  22. You actually make it seem so easy with your presentation however I find this matter to be really one thing which
    I believe I’d never understand. It kind of feels too complicated and extremely huge for me.
    I’m having a look ahead for your subsequent submit, I will attempt to get the grasp of it!

Skip to main content