Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84613members
353912posts

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

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Solved
MikeForshock
Lieutenant JG
Lieutenant JG
0 Likes
12
4150

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

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


Accepted Solutions
BevanWeiss
Spock
Spock
11
4148

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

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.


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..

See Answer In Context

12 Replies 12
BevanWeiss
Spock
Spock
11
4149

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

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.


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
AdamWoodland
Commander Commander
Commander
3
4144

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

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
Spock
Spock
2
4142

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

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...


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
AdamWoodland
Commander Commander
Commander

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

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
Spock
Spock
0
4087

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

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.


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
MikeForshock
Lieutenant JG
Lieutenant JG
0 Likes
0
4049

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

Thanks for the response, I appreciate it.

MikeForshock
Lieutenant JG
Lieutenant JG
0 Likes
5
4015

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

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
Spock
Spock
4
4012

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

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.


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
AdamWoodland
Commander Commander
Commander
0 Likes
3
3992

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

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

 

See the details on logic execution at https://tprojects.schneider-electric.com/telemetry/display/CS/Logic+Execution, 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
Spock
Spock
0 Likes
2
3989

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

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?


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
AdamWoodland
Commander Commander
Commander
0 Likes
1
3985

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

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
Janeway Janeway
Janeway
0 Likes
0
3979

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

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.