Active Node alerts with associated IP address?

leonejames1

Does anyone know how I can get a sql query to report on triggered alerts + the alert objects associated IP

It's a paint o constantly have to open each object or 'hover' to see the addresses when we have a mass outage.

And I am not sure which table to join for the correct information

Thank you!

ecmel

I can list the tables related to your requirement below:

AlertActive: list of active alerts

AlertObjects: list of alert objects (containing related node information)

AlertConfigurations: list of configured alert definitions

Nodes: list of nodes

This is an example SQL query to pull the data using join statement:

SELECT aa.AlertActiveID
, aa.TriggeredDateTime 
, ac.Name AS AlertName
, N.Caption as NodeName
, N.IP_Address as NodeIP
FROM AlertObjects (nolock) AO
INNER JOIN AlertActive (nolock) AA ON AO.AlertObjectID=AA.AlertObjectID
INNER JOIN AlertConfigurations (nolock) AC ON AC.AlertID=AO.AlertID
INNER JOIN Nodes N (nolock) ON AO.RelatedNodeID=N.NodeID
ORDER BY AA.TriggeredDateTime desc