SWQL Query On Node & Interface Down Alerts for Modern Dashboard

ImNishat

Hello Team,

I trust you're all doing well!

I'm reaching out for guidance to create a SWQL query for Alerts which can i use in modern dashboard. The requirement is to display all active alerts pertaining to node down and whose node ownership is "FT" in a separate view. Additionally, there should be a distinct view for All active alerts related to interface down with all ownership.

Could you advise on how I can achieve this and the most effective way to present it?

flackovic

This is what I'm currently using on one of my Modern Dashboards. I've added the 'FT' organization component, but you'll need to check and make sure the CustomProperty is updated to reflect what it is in your environment(s). Change both instances of '[Nodes].CustomProperties.Ownership' to whatever you need to.

SELECT

[Nodes].Caption AS [Node]
, [Nodes].DetailsURL AS [_LinkFor_Node]
, [Nodes].Status AS [Status]
, [Nodes].CustomProperties.Ownership AS [Owner]

, ToLocal([AlertObjects].AlertActive.TriggeredDateTime) AS [Down Time]

, CASE
WHEN TOSTRING(FLOOR(HOURDIFF([AlertObjects].AlertActive.TriggeredDateTime,GETUTCDATE())/24)) <> 0
THEN CONCAT(TOSTRING(FLOOR(HOURDIFF([AlertObjects].AlertActive.TriggeredDateTime,GETUTCDATE())/24)), 'd') ELSE ''
END + ' '

, CASE
WHEN TOSTRING(FLOOR(MINUTEDIFF([AlertObjects].AlertActive.TriggeredDateTime,GETUTCDATE())/60.0 % 24)) <> 0
THEN CONCAT(TOSTRING(FLOOR(MINUTEDIFF([AlertObjects].AlertActive.TriggeredDateTime,GETUTCDATE())/60.0 % 24)), 'h') ELSE ''
END + ' '

, CASE
WHEN TOSTRING(FLOOR(MINUTEDIFF([AlertObjects].AlertActive.TriggeredDateTime,GETUTCDATE())%60)) <> 0
THEN CONCAT(TOSTRING(FLOOR(MINUTEDIFF([AlertObjects].AlertActive.TriggeredDateTime,GETUTCDATE())%60)), 'm') ELSE ''
END AS [Outage Duration]

FROM Orion.AlertObjects [AlertObjects]
LEFT JOIN Orion.Nodes [Nodes] ON [Nodes].Uri = [AlertObjects].EntityUri

WHERE [AlertObjects].AlertConfigurations.Name = 'Node is down' -- Uses the OOTB "Node is down" alert that should trigger for each node
AND [Nodes].CustomProperties.Ownership = 'FT'
AND [Nodes].Status = '2'

-- Order widget results by time of outage and node caption 
ORDER BY [AlertObjects].AlertActive.TriggeredDateTime DESC
, [Nodes].Caption ASC

For the Interfaces, this should give you a start:

SELECT TOP 10

[Nodes].Caption AS [Node]
, [Nodes].DetailsURL AS [_LinkFor_Node]
, [Nodes].Status AS [Status]
, [Nodes].Interfaces.Alias AS [Interface]
, [Nodes].Interfaces.DetailsUrl AS [_LinkFor_Interface]

, CASE 
WHEN ToString([Nodes].Interfaces.AdminStatus) = '1' THEN 'UP'
WHEN ToString([Nodes].Interfaces.AdminStatus) = '4' THEN 'SHUTDOWN'
ELSE 'DOWN'
END AS [Administrative Status]

, CASE
WHEN ToString([Nodes].Interfaces.OperStatus) = '1' THEN 'UP'
ELSE 'DOWN'
END AS [Operational Status]

, ToLocal([Alerts].TriggeredDateTime) AS [Down Time]
, [Alerts].TriggeredMessage AS [RFO]

FROM Orion.Nodes [Nodes]
JOIN Orion.AlertObjects AS [Objects] ON [Objects].RelatedNodeId = [Nodes].NodeId AND [Objects].EntityUri = [Nodes].Interfaces.Uri
JOIN Orion.AlertActive AS [Alerts] ON [Alerts].AlertObjectId = [Objects].AlertObjectId

WHERE [Nodes].Interfaces.Status = 2
AND [Alerts].TriggeredMessage LIKE '%Interface%down%'

ORDER BY [Alerts].TriggeredDateTime DESC

Quick note: this is an "expensive" query and can be prone to timeouts, etc. if you have a lot of down interface alerts triggered. I would recommend filtering this further if possible using a specific alert instead of the OOTB "Interface down" alert or by other properties. I'd also cap the return using a SELECT TOP statement.

Unless your environment has a lot less down interfaces than mine... then you should be good.