Alarm reports using Information Manager
I'm trying to build a daily and weekly alarm report using the data stored on an Information Manager server; using Excel and DataDirect i'm able to fetch only about 30,000 rows very slowly, and its not enough for the weekly report. Using SQL i've found the audit and system events using the flag EVENT_TYPE=1 or 2, but no success for finding the process alarms.
Any suggestions or different methods to do a process alarm report?
Voted best answer
Please read the IM's Data Access and Reports User's Guide!
With an EXCEL_DA license seat you can perform an "AID" login to the IM and get access to "stunningly fast" SQL-statements towards the Message Logs in the IM Oracle instance using the ABBSql()-function.
W/o this license I believe your stuck with the less fast ABBAERetrieval() function that only can read from 800xA's A/E services (but, really this is not a fair comparison since it is made available for applications w/o IM)
The UG explains how the message logs are built up (using a number of different MSGxxx tables to store the events and their attributes), how joined queries can merge them together and how to filter only certain process alarms categories, e.g. "Process Condition Event" (emitted from AC800M).
Advanced queries (eg filtering on individual event attributes, etc) may require additional indexes to avoid so called "full table scanning" which is Oracle's last resort in lack of an useable index. Full table scans significantly increases a SQL-query's response time. The predefined indexes permit sorting & filtering on object name and event time only I believe.
If you are using latest 800xA 5.1 you have Alarm Analysis which will give you most of the information.You need to have separate licence for this.
Alarm Analysis will give you 20 most frequent alarms, alarm rate,time to acknowledge etc..But it won't be in excel format.
Even I tried to put all these information in excel form and generate a report weekly/monthly automatically. But couldn't get success.