Can any one provide the correct SQL statement for the following:
Node Name
Node IP
Active Alert
Severity = Informational (0)
maybe this is more up to speed for you?
SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), a.TimeStamp ) 'TRIGGER TIME' --local SQL time,a.EntityCaption 'TRIGGER OBJECT' --what caused the alert,a.RelatedNodeCaption 'RELATED NODE' --parentNode (will be the same as trigger object for node alerts),n.IP_Address 'IP ADDRESS' --parentNode IP Address,a.Name 'ALERT NAME',a.Message 'ALERT MESSAGE',a.ObjectType 'ALERT OBJECT TYPE'FROM AlertHistoryView aJOIN Nodes n ON n.NodeID = a.RelatedNodeIdWHERE a.EventTypeWord = 'Triggered' --only triggering events, skip actions and resetsAND a.Severity = 0 --AND a.ObjectType IN ( 'APM: Application', 'APM: Component', 'Node' ) --if you want to limit to only app/node alerts, uncomment this line
I'm running a slightly older version of Orion, so I am not sure if the latest version has the node IP in the dbo.AlertHistoryView view as dgsmith80 mentions above, if it is; then you can drop line 10 and change line 5 to match the column name in the view.
This is what I have that fails in the content editor when building the report:
SELECT a.*, n.IP_Address
FROM dbo.AlertHistoryView a
JOIN Nodes n ON n.NodeID = a.RelatedNodeID
WHERE Severity = 0
What information are you actually trying to get? You have a.* which assumes your trying to get all fields from AlertHistoryView, but then you've done JOIN for the Node IP which is already in the AlertHistoryView.
I need All Nodes or Applications that triggered 0 Severity Alarms along with that Objects IP if available
