Get NodeId for the Alert on Alert status

Question

I am trying to get node details related to the alert. In forum questions, they do inner join of AlertStatus.ActiveObject and Nodes.NodeID. The question is 1) what is the significance of ActiveObject in AlertStatus table, 2) Can I join AlertStatus and Nodes using ActiveObject and NodeId to get Node details? (3) How to get all the required datas related to the given alert?

The following query is used in forums. Please explain this.

SELECT AlertDefID,ActiveObject,ObjectType,ObjectName, State,AlertMessage, TriggerTimeStamp,AlertObjectID FROM Orion.AlertStatus INNER JOIN Orion.Nodes ON AlertStatus.ActiveObject = Nodes.NodeID

adam.beedell · Answer

Download SWQL studio if you havnt already, it makes answering these questions easier

1) So in the AlertStatus table ActiveObject is the ID for whatever the alert is going off for. Alerts can go off for most typed of object so there's a bunch of IDs in there with numbers that could overlap. The ObjectType column defines the prefix for nodes, volumes, etc. If you know what table your object will be in there's no overlap and the ID will match, so:

2)Yup

3) Add whatever columns you're after to that query like so:

SELECT as.AlertDefID,as.ActiveObject,as.ObjectType,as.ObjectName, as.State,as.AlertMessage, as.TriggerTimeStamp,as.AlertObjectID, n.caption

FROM Orion.AlertStatus as

INNER JOIN Orion.Nodes n ON as.ActiveObject = n.NodeID

WHERE n.NodeID like 'whatever you're looking for, maybe a macro or something'

That'll work in a few places but not all of em, depends what exactly you're trying to do

The bolded stuff is table aliases, less errors from SWQL that way over collumn name confusion

xaturbatu · Answer

You will only get alerts for node objects there. You should use a different column in order to be able to properly link them.

Just go and use Orion.AlertObjects table and you already have it linked with the Orion.Nodes table. Ex: ao.Node.Caption will get you the caption where ao is your alias for Orion.AlertObjects table.