Report on Alerts to include Downtime

ngsd 5 months ago

Question:

How can I get a report of "Node is down" alerts, which includes a field for either Downtime or Reset Time?

Background:

I created a basic, working "Node is down" alert. The only trigger action is send an email. The only reset action is send an email. The reset action message, contains: "Object was down for ${N=Alerting;M=Downtime} minutes." I get an email when a node goes down. I get another email when the node comes up, which includes the down time in minutes. It works perfectly!

I want to create a report for these alerts. I created a "Custom Table" report and found fields for NodeName and TriggerTime. I cannot find any field indicating the time the alert reset. I must be overlooking it. Where is the downtime field from the "Reset Action" event?

Parents

0 bobmarley 5 months ago

Try this, I commented out a custom property

SELECT 
    NodeName AS [Node Name],
        '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],
        DetailsUrl AS [_LinkFor_Node Name]--, Nodes.CustomProperties._Group as [Group],
        
,MAX(tolocal(DOWNEVENT.EVENTTIME)) AS [DOWN TIME]
FROM ORION.NODES NODES
INNER JOIN ORION.EVENTS DOWNEVENT
ON NODES.NODEID = DOWNEVENT.NETWORKNODE
WHERE STATUS = 2 
GROUP BY NodeName, StatusIcon, DetailsUrl--, Nodes.CustomProperties._Group
--ORDER BY NodeName
ORDER BY [DOWN TIME] DESC

we used called group. If you uncomment that to put in your own you will need to remove the extra comma in the Max line.

0 bobmarley 5 months ago in reply to bobmarley

This was my replacement to the out of the box Node Down resource as I needed the time in it that the node went down, and my exclusion list was getting too long on that resource and it is way easier to work on within the SWQL query. I also included our SNow assignment group so our NOC could see when they went down and what team the node belongs too. The query mesverrum provided below is also an excellent view.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 bobmarley 5 months ago in reply to bobmarley

This was my replacement to the out of the box Node Down resource as I needed the time in it that the node went down, and my exclusion list was getting too long on that resource and it is way easier to work on within the SWQL query. I also included our SNow assignment group so our NOC could see when they went down and what team the node belongs too. The query mesverrum provided below is also an excellent view.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data