1 Reply Latest reply on Oct 2, 2017 7:50 AM by radapompa

    Report for how many alerts that have been escalated

    radapompa

      Hi,

      I want to create a report where you can see how many alerts that have been escalated during a month. This to be able to evaluate how many of the alerts that have been escalated. But I can't seem to find a way to see if an alert has been escalated. Anyone that has an idea of how the report could look like to match the criteria?

       

      I might be able to match on alert time vs acknowledge time. Since the escalation happens after a specific time and if it hasn't been acknowledged. But might be an issue if you have different escalation times.

       

      I'm grateful for any ideas on how to solve this!

        • Re: Report for how many alerts that have been escalated
          radapompa

          I've been doing some progress now. It's not 100% percent of what I would like to have it, but it's something. Basically I've written an SQL that shows all alerts that has had an certain action during last month. This action is the one we are using for the escalation. I'm also comparing it with the amount of alerts that has happened last month during the time period the escalation is available.

          Last step would be to exclude alerts which dosen't have the action id, just so that we can compare with the actual alerts that can be escalated. I havent' found an solution so far. So if anyone has an suggestion on how to do it, let me know.

           

          Here is the SQL that I've came up with so far (not been able to convert it to SWQL):

           

          //Count the escalated alerts

          SELECT  COUNT(DISTINCT AlertActiveID) AS Escalated,

          (

              //Count the total alerts that has happened

              SELECT COUNT(DISTINCT AlertActiveID) AS a

              FROM [OrionNPM].[dbo].[AlertHistory]

              //Show only last month

              WHERE TimeStamp BETWEEN DATEADD(MONTH,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

              //Show only weekend and after 17:00 and before 08:00 on Monday to Friday

              AND (DATEPART(w,TimeStamp) IN (6) OR DATEPART(w,TimeStamp) IN (7) OR ((TimeStamp BETWEEN '17:00' AND '23:59'  OR TimeStamp BETWEEN '00:00' AND '08:00') AND DATEPART(w,TimeStamp) NOT IN (6,7)))

          ) AS Alarms

          FROM [OrionNPM].[dbo].[AlertHistory]

          //The action which is used for escalation

          WHERE ActionID = 210

          //Show only from last month

          AND TimeStamp BETWEEN DATEADD(MONTH,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))