cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Custom SQL for report - NODE NAME, IP, From Alert History View Where Severity is 0

Jump to solution

Can any one provide the correct SQL statement for the following:

Node Name

Node IP

Active Alert

Severity = Informational (0)

0 Kudos
1 Solution

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 a
JOIN Nodes n ON n.NodeID = a.RelatedNodeId
WHERE a.EventTypeWord = 'Triggered' --only triggering events, skip actions and resets
AND 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.

View solution in original post

6 Replies
Level 9

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

0 Kudos

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.

- David Smith

I need All Nodes or Applications that triggered 0 Severity Alarms along with that Objects IP if available

0 Kudos

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 a
JOIN Nodes n ON n.NodeID = a.RelatedNodeId
WHERE a.EventTypeWord = 'Triggered' --only triggering events, skip actions and resets
AND 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.

View solution in original post

this worked perfectly and also included nodes as well as app/URLs from SAM...thank you.

0 Kudos

No ignore my comment I had done a SELECT * and it was showing me both Tables fields, it was only when I checked the table index to double check that I noticed my mistake, In all honesty, I'm not a fan of SELECT * for that exact reason, I always like to list out the exact fields I want from the query, which is why SWQL is so great as there is no such thing as SELECT *

- David Smith