I am trying to build a query and eventually a dashboard for our Remote locations only
I cannot seem to get the Node statuses
I tried to create an alert to key off of but i do not get the proper node statuses
Historical Event Description for the last 30 days
Nodes where an Interface has Custom Field: Wan_Interface =True
Would like to see the following Status events
1 Node Down
2 Node Warning
5 Node Up
8 Node Removed
9 Node Added
14 Node Rebooted
40 Unmanage Node
41 Manage Node
100 Node Changed
Example
AlertHistoryID EventType EventTypeName Message Timestamp AlertACtiveID AlertObjectID ActionID,NodeCaption NodeIPAdress
386015 1 Node Down Wan Interfaces was triggered. 2025-01-19 17:12:12.78 366015 116438 568 ChicagoRouter 10.10.10.1
386016 5 Node Up Wan Interfaces was triggered. 2025-01-19 17:12:12.79 366016 116439 568 ChicagoRouter 10.10.10.1
386017 40 Unmanage Node Wan Interfaces was triggered. 2025-01-19 17:12:12.80 366017 116440 568 LARouter 10.10.10.2
386018 41 Manage Node Wan Interfaces was triggered. 2025-01-19 17:12:12.81 366018 116441 568 LARouter 10.10.10.2
here is the query i have to date
SELECT TOP 100
ah.AlertHistoryID,
ah.EventType,
et.Name AS EventTypeName,
ah.Message,
ah.TimeStamp,
ah.AlertActiveID,
ah.AlertObjectID,
ah.ActionID,
a.Title AS ActionTitle,
n.NodeID,
n.Caption AS NodeCaption,
n.IPAddress AS NodeIPAddress,
ncp.EBS_Location_ID
FROM Orion.AlertHistory ah
LEFT JOIN Orion.EventTypes et ON ah.EventType = et.EventType
INNER JOIN Orion.AlertObjects ao ON ah.AlertObjectID = ao.AlertObjectID
INNER JOIN Orion.Nodes n ON ao.RelatedNodeID = n.NodeID
LEFT JOIN Orion.Actions a ON ah.ActionID = a.ActionID
LEFT JOIN Orion.NodesCustomProperties ncp ON n.NodeID = ncp.NodeID
WHERE ah.ActionID > 1
AND ao.EntityType = 'Orion.Nodes'
AND n.NodeID IN (
SELECT DISTINCT i.NodeID
FROM Orion.NPM.Interfaces i
INNER JOIN Orion.NPM.InterfacesCustomProperties icp
ON i.InterfaceID = icp.InterfaceID
WHERE icp.WAN_Interface = 'True'
)
