This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Report for how many alerts that have been escalated

radapompa over 6 years ago

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!

0 radapompa over 6 years ago

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))
Cancel
Vote Up 0 Vote Down

Cancel