cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

Accepted Solutions
Highlighted
Level 14

Re: Add IP address to "All Active Alerts"

Jump to solution

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
Highlighted
Level 14

Re: Add IP address to "All Active Alerts"

Jump to solution

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

Highlighted
Level 9

Re: Add IP address to "All Active Alerts"

Jump to solution

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
Highlighted
Level 14

Re: Add IP address to "All Active Alerts"

Jump to solution

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

Highlighted
Level 9

Re: Add IP address to "All Active Alerts"

Jump to solution

You are right, it is working fine now