ODBC/SQL to get Alarm List (Mimic Alarm Banner)

[MikeForshock]

Checking to see if there is a simpler method than multiple Table joins and subselects to get the list of alarms, similar to the alarm banner.

We are attempting to get a snapshot of the current/past alarms, cleared-unacked alarms, etc. from a ODBC/SQL query into our notification system (think Win911).

The only solution we seem to be able to come up with is processing each and every point table (Internal Analogs, Digitals; Modbus Analogs, Digitals; DNP3 Analogs, Digitals, etc.). This could become quite problematic as the need for other point types and Drivers may vary and would seem to be a waste of resources for unused tables.

Thanks for your help,

Mike

[BevanWeiss · Accepted solution]

I would recommend that you turn on Alarm Summary in the Server Configuration.

Then you can query the CAlarmSummary (maybe CDBAlarmSummary... I forget, but 'The Schema' has the answers 😉 )

That will provide you information on both current and historical alarms, for the duration that you have the Alarm Summary table configured.

The CAlarm table can also provide some information on current alarms, however it's not as useful as the Alarm Summary table.

[AdamWoodland]

Yup, its CDBAlarmSummary

CDBAlarmSummary = Current and history

CAlarm = Current

Querying CDBObject and descendants will be inaccurate as objects can have multiple alarm conditions active yet it reports the highest severity one

Edit: Fixed CAlarm table name

[BevanWeiss]

It would be super wonderful if the QP could add aliases to all the Cxxxxx tables so that they are also accessible as CDBxxxx

It's difficult to remember what arbitrary prefix each table was given 😛

Is it 'CAlarms' or 'CAlarm'?  I thought they were all singular...

[AdamWoodland]

Yes, the irony, it is CAlarm.

I think one of the problems is the table length is limited (a joy when writing DDKs) hence why some are C... and some are CDB...

[BevanWeiss]

That differs a bit from the previous 'story' I was told, where it was around whether the code-base for the table was from C (C..), or from C++ (CDB...).

Obviously from a user perspective it's a bit arbitrary anyway.

Having aliases without any such C / CDB prefix may be best.

After all, if I want to query for all Alarms... I shouldn't need to worry about CDBAlarm or CAlarm.

I should just be able to query for Alarm.

[MikeForshock]

Thanks for the response, I appreciate it.

[MikeForshock]

CDBAlarmSummary gets a little closer to the goal, but not quite there. I do appreciate your help.

The descriptions would required a lot of parsing strings to get a specific cause for a shortened message.

Example:

"State changed from Starting to Start Fail, value is 4 (Current data)" on the source  object "Forshock Test.Well Test.Status"

Using the ST Logic program we get:

"Well Test: Start Fail"

But this particular object could be a Digital, ModbusDigital, DNP3, etc. So must be further sorted to make sense to the outside software.

Perhaps diagnosing my root issue would be a better solution:

We have a simple ST Logic program that updates a few fields on a Linked SQL Table.

The logic works perfectly fine, until the system moves to the standby.

The logic then fails.

The Linked table is configured on both machines (Main and Standby).

If the standby is started as the main, the logic works.

If the logic is restarted after the main takeover, the logic works.

Only seems to be an issue on the takeover.

Is there a way to programmatically restart the logic?

Example of the SQL:

SAAUpdate AT %D(UPDATE SCADAspireAlertAlarms SET DescriptionTXT=?, DescriptionINT=?, IsAlarm=?, IsAcked=?, Priority=? WHERE UniqueID=?) WITH_PARAMS saadesc,saadescint,saaisalarm, saaisacked,saapriority,saauniqueid;

Variables:

saadesc = Object Name and CurrentStateDesc

saadescint = Object ID, CurrentState and AlarmState

saaisalarm = Boolean (Active Unacked, Active Acked)

saaisacked = Boolean (Acked)

saapriority = Priority

saauniqueid = System name, FullName, Object ID

This is likely not ideal as this obviously duplicating the fields and requires ClearSCADA to handle all of the processing.

Each Template Instance runs a similar logic program, so can become quite intensive as systems grow.

[BevanWeiss]

Hmm... 

What do you mean by 'when the logic is restarted after the main takeover'?

Do you mean that you go into Server Status, Modules and restart the Logic module?

Or do you just take that particular Logic Routine out of service, and then put it back in service?

Or do you just mean that the next execution of the logic works fine, but the execution during the failover doesn't? (if your logic runs for so long that this is a problem then it likely causes problems of its own.. like heartbeat failures).

It seems that perhaps you do indeed want CAlarm, if you only want the current alarms

To get 'Well Test: Start Fail', then the following should work

Perhaps try

SELECT

  ID->ParentGroupId->Name || ":" || ActiveSubCondition as "saadesc",

  ID || ":" || ActiveSubCondition || ":" StateDesc as "saadescint",

  InactiveTime IS NULL as "saaisalarm",

  Accepted as "saaisacked",

  Severity as "saapriority",

  'SystemName' || ":" || ID->FullName || ":" || ID

FROM CAlarm

That should get the values you want.  Although you'd have to handle resetting things if you're staging into a remote table.

[AdamWoodland]

Also be really really careful using linked tables in logic, it is not recommended.

See the details on logic execution at https://community.se.com/t5/Geo-SCADA-Knowledge-Base/OPC-Quality-Flags/ba-p/279050, basically reading and writing is done under a database lock and any delays in either actions on the linked table will impact logic execution and ultimately the whole database.

I once was using linked tables in logic to read and write to Oracle so there was a single source of truth for the data, and when the Oracle server wasn't accessible there was a "bug" in the Oracle client whereby it returned nothing the the API calls for 10 minutes.

During that 10 mins the system was unavailable. And this logic was in a template with multiple instances... basically the system was unusable until the Oracle was back online. No idea if that bug in Oracle was ever fixed, but I learnt and never used linked tables in logic again.

[BevanWeiss]

To stray a bit from the original topic.  I really don't like that Linked Tables actions are performed under DB Locks, and I can't understand why this might be the case still.

@AdamWoodland Is there a good reason for this?

Or is it just because it uses the QP, and the QP isn't particularly well optimised in regards to DB Lock performance?

[AdamWoodland]

Probably need a IANACSD (ClearSCADA developer) disclaimer here 🙂

Database consistency is king, but in my head linked tables may not fit under that strict consistency rule so maybe they could modify the logic engine to be outside of a lock. It probably adds a new dynamic here with asynchronous execution, you need to hold up the logic until query has returned and a database lock is a "simple" method to achieve that.

Would they allow other logic to run in the mean time? What happens when a backlog happens? Logic is designed to be quick, efficient and predictable and if they were to implement something like this is can see a knock-on effect that could be quite problematic for both developers and users.

18 odd years ago there was a registry setting to control how many threads there were for logic, but that got removed and I can only speculate it was to help with the whole database consistency, although back then testing to see if made a difference was a lot harder.

I couldn't see any requests for this in the issue system, so you may want to officially request via support.

[sbeadle]

Adam's right. Only use linked tables if you can 100% guarantee it will be available all of the time. I'd recommend exporting with the SQL Export driver, or write your own custom DDK driver to do it.