Need to fetch node id in the existing query

Dears ,

Below is the query to fetch alert info triggered date , details etc , attached the screenshot . Need to fetch NODE ID along with this query , can anyone help me to customize to include Node ID please .

Query

=====

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 AS AlertObjects
INNER JOIN Orion.AlertActive AS AlertActive ON AlertObjects.AlertObjectID = AlertActive.AlertObjectID
INNER JOIN Orion.AlertConfigurations AS AlertConfigurations ON AlertConfigurations.AlertID = AlertObjects.AlertID
ORDER BY AlertConfigurations.Name
     , AlertObjects.EntityCaption

Find more posts tagged with

Alert

query

alarm

Accepted answers

All comments

basilfernandaz

KMSigma.SWI

You really should start leveraging the Navigation Properties instead of using all these JOINs. Makes the queries easier to parse.

SELECT DISTINCT [AlertObjects].AlertActive.AlertActiveID
     , [AlertObjects].AlertObjectID
     , [AlertObjects].AlertConfigurations.Name
     , [AlertObjects].AlertConfigurations.Severity
     , [AlertObjects].AlertConfigurations.ObjectType
     , [AlertObjects].EntityUri
     , [AlertObjects].EntityType
     , [AlertObjects].EntityCaption
     , ToLocal([AlertObjects].AlertActive.TriggeredDateTime) AS [TriggeredDateTime]
     , [AlertObjects].LastTriggeredDateTime
     , [AlertObjects].AlertActive.TriggeredMessage AS Message
     , [AlertObjects].AlertActive.AcknowledgedDateTime
     , [AlertObjects].AlertActive.Acknowledged AS Acknowledged
     , [AlertObjects].AlertActive.AcknowledgedBy
     , [AlertObjects].AlertActive.AcknowledgedNote
     , CASE 
          WHEN Floor((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 86400) > 0
               THEN ToString(ToString(Floor((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 86400)) + 'd ' + ToString(Floor(((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) - 86400 * (Floor((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 86400))) + 0.0) / 3600)) + 'h ' + ToString(Floor(((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) - 3600 * (Floor((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 3600))) + 0.0) / 60)) + 'm ')
          WHEN Floor(((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) - 86400 * (Floor((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 86400))) + 0.0) / 3600) > 0
               THEN ToString(ToString(Floor(((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) - 86400 * (Floor((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 86400))) + 0.0) / 3600)) + 'h ' + ToString(Floor(((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) - 3600 * (Floor((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 3600))) + 0.0) / 60)) + 'm ')
          WHEN Floor(((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) - 3600 * (Floor((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 3600))) + 0.0) / 60) > 0
               THEN ToString(ToString(Floor(((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) - 3600 * (Floor((SecondDiff([AlertObjects].AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 3600))) + 0.0) / 60)) + 'm ')
          ELSE ''
          END AS ActiveTime
     , [AlertObjects].Node.NodeID
FROM Orion.AlertObjects AS [AlertObjects]
ORDER BY [AlertObjects].AlertConfigurations.Name
     , [AlertObjects].EntityCaption

bobmarley

In the Orion.AlertObjects table it appears you could use either EntityNetObjectID or RelatedNodeID as your node ID for your query.

basilfernandaz

can u pls help with the query pls wher to add this entitynetobjectid in the above query ?

basilfernandaz

i am new to this platform , can u help me with the complete query pls

KMSigma.SWI

Already did. The above query added NodeID at the end of the list.

As @bobmarley suggested, you could also use the EntityNetObjectID but that would return the NetObjectID for anything - not just nodes. For this screenshot below I added in the EntityNetObjectID so you can see the difference.

The first element shows a NodeID of 1, but an EntityNetObjectID of AM:2. Because this is an alert on an Application Component, it uses the AM prefix followed by the ID number for that Application Component.

Are you trying to build an Alert dashboard or something? Because you can just import one that our members have shared from the Modern Dashboards Content Exchange.

KMSigma.SWI

The EntityNetObjectID in the Orion.AlertObjects entity isn't always a Node. Sometimes it's a Volume, an Interface, an Application, or something else. It all depends on the 'target' of the specific alert. You are better off using the Navigational Properties and just connecting to the Orion.Nodes entity and retrieving the NodeID from that.

bobmarley

Thanks!