Joining Custom Poller Table

Hey y'all

So, my company uses an RTU which collects binary inputs from multiple devices connected to it and uses that to signify when the connected systems are in alarm. When their binary state changes it triggers a preset alarm. The following is the custom Poller table on the node which contains the alarms and which physical I/O ports they're related to.

_____________________________________________________________________________

So basically, if contact point 1.2.1 is high then the alarm triggers. (A state of 2 is high 1 is low, idk why it's not 1 and 0)

My issue is, I'm trying to get the alert names into a modern dashboard, but when the alerts are triggered, the alert description is handled by some SQL code so when I try and pull through the alert name it pulls through the SQL code which is useless. However, I can get the contact point through so I'm trying to figure out how to join the table to access the alarm name of rows where the contact point matches. In other words, I'm trying to find the alarm which is related to the specific contact point in the custom poller table.

At the moment, to pull through the contact point I'm using the following code:

SELECT

[AlertObjects].EntityCaption

FROM Orion.AlertObjects AS [AlertObjects]

WHERE [AlertObjects].AlertActive.AlertActiveID > 0 AND [AlertObjects].Node.NodeName LIKE '--'

I've tried a LOT of different ways to try and pull the alarm name through, so I'm fairly sure I can't with just using navigation properties. So I thought joining the poller table and the high contact points I could retrieve the alarm name. I just can't figure out how to do this.

Thanks !!

Find more posts tagged with

RTU

SWQL

Modern Dashboards

custom poller

Alerts

Accepted answers

HelpMeTHWACK

UPDATE:

Got it working like this check it out

SELECT TOP 100 
    CustomPollerAssignmentID
    ,DateTime
    ,Status
    ,RowID 
    ,AO.Description
    ,AO.EntityCaption

FROM Orion.NPM.CustomPollerStatistics CPS

JOIN Orion.AlertObjects AO ON (CPS.RowID = Replace(AO.EntityCaption, 'Contact Point ', ''))

WHERE RowID LIKE '%.2.%' 
    AND SECONDDIFF(DateTime, GETUTCDATE()) < 60
    AND AO.AlertActive.AlertActiveID > 0

All comments

KMSigma.SWI

When you say SQL code do you mean something you've all written or something that's be put into the platform somewhere?

If it's your own code, any chance you can share it. Might prove handy.

HelpMeTHWACK

Here is the code I can pull through; I didn't write it. It gets pulled through when I query

Orion.AlertObjects.AlertConfiguration.AlertMessage

${N=SwisEntity;M=CustomPollerAssignment.Node.Caption}:: [${N=SwisEntity;M=CustomPollerAssignment.Node.CustomProperties.ws_node_short_description}] ${SQL: 
SELECT 
CASE 
    WHEN (name.value LIKE 'CRITICAL%')  THEN right(name.value, len(name.value) - 9)
    WHEN (name.value LIKE 'UA%')  THEN right(name.value, len(name.value) - 3)
    WHEN (name.value LIKE 'NUA%') THEN right(name.value, len(name.value) - 4)
END AS value
FROM [dbo].[CustomPollerStatusTable] name
where 
name.nodeid like '${NodeID}' and
name.rowid like '${N=SwisEntity;M=RowID}' and
len(name.value) > 2
} has triggered

If there's any more information I can provide, please let me know. Thanks!

planglois

Out of curiosity, what's the vendor and model of RTU?

HelpMeTHWACK

Asentria SiteBoss 530 pretty sure

adam.beedell

Maybe add a unique thing after "triggered" in the alert so you can find it (or use the trigger ID)
then case-statement out the SQL

You've got the object ID already so that should be dynamic enough right?

HelpMeTHWACK

Sorry, this is for a modern dashboard, I didn't write that SQL code. I'm trying to access the above table to display the alert name in my dashboard (using SWQL).

HelpMeTHWACK

UPDATE:

Got it working like this check it out

SELECT TOP 100 
    CustomPollerAssignmentID
    ,DateTime
    ,Status
    ,RowID 
    ,AO.Description
    ,AO.EntityCaption

FROM Orion.NPM.CustomPollerStatistics CPS

JOIN Orion.AlertObjects AO ON (CPS.RowID = Replace(AO.EntityCaption, 'Contact Point ', ''))

WHERE RowID LIKE '%.2.%' 
    AND SECONDDIFF(DateTime, GETUTCDATE()) < 60
    AND AO.AlertActive.AlertActiveID > 0