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

e.jenidy
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
jrouviere
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.

e.jenidy
Level 9

Re: Add IP address to "All Active Alerts"

Jump to solution

You are right, it is working fine now