Query Alarm / Event Lists for 3rd Party Database
Data Direct has a function to Retrive the Alarm And Event Messages for a specific filtered list - The "IIT Alarms and Events" function on the Data Direct Toolbar. These events are read from the short term 800xA System Message Server
Is there any way to retrive the same information by using an SQL Query without using excell ?
The Information Management manual only describes using SQL to read messages from the IM Oracle database. We want to read alarm and event information from a system without using the IM. An OPC-AE client is not an option at this site.
Voted best answer
No, I've not heard about any Event Storage -> SQL interface or application.
For OPC DA/HDA we have the old OLE/DB methods available; but for AE... no I don't think so.
ABB Denmark has some eventlogger storing on plain text file; maybe you can ask them for a SQL based logger client and store the events in some database of your own choice?
The performance of today's Event Storage is not very good; any 3rd party "man in the middle agent" SQL-interface would probably be as slow as the regular event list, especially when applying attribute filters (the search/retrieval engine of Event Storage is pure string based (even for numeric attributes) without any indexing, etc. -> "full table scans" for just about any request using some filtering).
As you wrote, the IM message log stores AE in a couple of Oracle tables which are accessible through a range of Oracle standard access methods (Excel being just one of them). Straight SQL connection to the MSGxxx and MSGATTRSxxx tables is probably the fastest method, but you need an IM server in the configuration first...
I assume you will not be able to defend a purchase of a complete IM server for this purpose alone?
I've written my own in C#. It collects opc alarms and event and writes it to a SQL server database.
You do not the full description like in 800xA. But it is enough to do analysis.
It started as a simple app to improve my c# knowledge, but it runs now for two months without any problems.
I can send you the source code if you like.
Sogeti in Sweden have a package called Alarm and Event Archiver.
That brings an AE client that is able to connect to multiple AE servers and store received events and alarms in an SQL Server. It is possible to filter out vendor specific attributes that should not be stored in the database. You also get an GUI to manage the filtering of opc category and attributes and store as a config file and you also get a web gui for statistics and filtering task of alarm and events.
I also now another firm in sweden that have similar package for collection and analyse of alarm and events.
I can send you contact details if you want.
At power generation we have a own "Power Generation Information Manager" (PGIM) that we use instead of the 800xA IM. It is mainly for long time archives and has a lot of tools and programs. You may use the Event Management only for your purpose.
+ it is an ABB solution
- license cost
I add the Setup Manual, so you could get an idea how the program works.
Now we use History 2.0, but before that we used the 800xA basic history and added AGGSoft TCP/IP Data Logger to fetch events from 800xA and write them to a SQL database. The software is used to fetch the events from 800xA using a virtual TCP/IP printer and the Alarm Logger Service in 800xA.
The solution can be made redundant.
I could send you a detailed setup-guide if it has any interest.