SQL Query To Link rows from [AlertStatus] table to NodeID from [Nodes] table

AlexSoul

Hi Guys,

I would appreciate if anyone can help me with SQL to link records from [AlertStatus] table to NodeID. I guess few joints will be needed

Thanks a lot,

Alex

AlexSoul

yep, thanks, that's what I am doing ... the above will obviously pick nodes only... below is a full SQL if anyone interested:

SELECT  nn.NodeID,  alst.ActiveObject,  alst.[State],  nn.Caption,  alst.ObjectType,  alst.ObjectName,  CONVERT(NVARCHAR(50), DATEDIFF(MINUTE, alst.TriggerTimeStamp, getdate())) + ' min ago' AS 'TRIGGERED',  alst.AcknowledgedFROM AlertStatus alstLEFT JOIN Volumes v ON (v.VolumeID = alst.ActiveObject AND alst.ObjectType = 'Volume')LEFT JOIN Interfaces i ON (i.InterfaceID = alst.ActiveObject AND alst.ObjectType = 'Interface')LEFT JOIN APM_AlertsAndReportsData c ON (c.ComponentID = alst.ActiveObject AND alst.ObjectType = 'APM: Component')LEFT JOIN Nodes n ON (n.NodeID = alst.ActiveObject AND alst.ObjectType = 'Node')JOIN Nodes nn ON  ( nn.NodeID = v.NodeID OR    nn.NodeID = i.NodeID OR    nn.NodeID = c.NodeId OR    nn.NodeID = n.NodeID  )ORDER BY alst.TriggerTimeStamp desc