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

Add IP address to "All Active Alerts"

Jump to solution

The default report "All Active Alerts" is pretty good but I need to show the IP address for the nodes as well but I can't get it to work.

Here is the SWQL:

SELECT DISTINCT

AlertActive.AlertActiveID, AlertObjects.AlertObjectID, AlertConfigurations.Name, AlertConfigurations.Severity, AlertConfigurations.ObjectType,

AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption,

ToLocal(AlertActive.TriggeredDateTime) AS TriggeredDateTime, AlertObjects.LastTriggeredDateTime, AlertActive.TriggeredMessage AS Message,

AlertActive.AcknowledgedDateTime, AlertActive.Acknowledged AS Acknowledged, AlertActive.AcknowledgedBy, AlertActive.AcknowledgedNote,

Case

When Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400)>0 Then

ToString(ToString(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) +0.0)/86400))+'d '+

ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+

ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)>0 Then

ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+

ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)>0 Then

ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

Else ''

End AS ActiveTime

FROM Orion.AlertObjects (nolock=true) AlertObjects

INNER JOIN Orion.AlertActive (nolock=true) AlertActive ON AlertObjects.AlertObjectID=AlertActive.AlertObjectID

INNER JOIN Orion.AlertConfigurations (nolock=true) AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID

Order By AlertConfigurations.Name, AlertObjects.EntityCaption

0 Kudos
1 Solution
Level 14

Not trying to over simplify it, but I was able to add AlertObjects.Node.IPAddress, to the select query as there's already a relationship between the tables, are you running into an issue where that doesn't work for you? Here's the entire third line with it added at the end:

<snip line 2>

AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption, AlertObjects.Node.IPAddress,

<snip line 4>

View solution in original post

4 Replies
Level 14

Not trying to over simplify it, but I was able to add AlertObjects.Node.IPAddress, to the select query as there's already a relationship between the tables, are you running into an issue where that doesn't work for you? Here's the entire third line with it added at the end:

<snip line 2>

AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption, AlertObjects.Node.IPAddress,

<snip line 4>

View solution in original post

Can you advise how to join ( Interfaces table ) I need to add interface status to this report but it gave me an error.

SELECT DISTINCT

AlertActive.AlertActiveID, AlertObjects.AlertObjectID, AlertConfigurations.Name, AlertConfigurations.Severity, AlertConfigurations.ObjectType, AlertObjects.relatednodecaption,

AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption,AlertObjects.Node.IPAddress,

ToLocal(AlertActive.TriggeredDateTime) AS TriggeredDateTime, AlertObjects.LastTriggeredDateTime, AlertActive.TriggeredMessage AS Message,

AddHour(+3, AlertActive.AcknowledgedDateTime) as ACK_Time, AlertActive.Acknowledged AS Acknowledged, AlertActive.AcknowledgedBy, AlertActive.AcknowledgedNote,

Case

When Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400)>0 Then

ToString(ToString(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) +0.0)/86400))+'d '+

ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+

ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)>0 Then

ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+

ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)>0 Then

ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')

Else ''

End AS ActiveTime

FROM Orion.AlertObjects (nolock=true) AlertObjects

INNER JOIN Orion.AlertActive (nolock=true) AlertActive ON AlertObjects.AlertObjectID=AlertActive.AlertObjectID

INNER JOIN Orion.AlertConfigurations (nolock=true) AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID

INNER JOIN  Orion.Nodes (nolock=true)  Nodes ON AlertObjects.RelatedNodeId = Nodes.NodeID

INNER JOIN  Orion.Interfaces (nolock=true)  Interfaces ON AlertObjects.EntityCaption = Interfaces.Caption

                        

                       

Order By AlertConfigurations.Name, AlertObjects.EntityCaption

@ jrouviere

0 Kudos

Orion.Interfaces doesn't exist, it's actually Orion.NPM.Interfaces, so making that change appears to make the query return results for me.

You are right, it is working fine now