External Database Export
In order to extend the capabilities of the statistical treatment of alarms and calls, the application configuration and the alarm history, the call history (group and user calls) and the report log can be automatically exported to an external database.
To set this functionality operational, the database export option has to be authorized by the software license and the "Database export activation" option must be checked in the general option dialog box (see General options).
When the database export option is activated, some additional features are available:
- Enhanced history information (transmitted message)
- Capability of comment attachment to alarms
- Generation of report attached to alarms
- Real time and differed time call overview
The exported data is stored in a single database. By default, it is a Microsoft Access database located in the "Base" subdirectory of the application's data directory: "AlertDB.mdb".
The link to this database is defined by the data link definition files "AlertDB.udl". To modify the connection to the database, open this file with the data source editor (a double click on the file name is sufficient) and modify the desired parameters.
The tables in the database are all built automatically when the application is initialized.
Configuration tables
The configuration tables contain all the configuration data needed to use the logs. They are updated automatically after each modification of the corresponding configuration data.
The configuration tables are the following tables:
STATIONS: List of stations
| Field name | Data type | Description |
|---|---|---|
| StationId | Numeric | Station identifier |
| StationName | Text | Station name |
GROUPS: List of tag groups
| Field name | Data type | Description |
|---|---|---|
| GroupId | Numeric | Tag group identifier |
| GroupName | Text | Tag group name |
CALLGROUPS: List of call groups
| Field name | Data type | Description |
|---|---|---|
| CallGroupId | Numeric | Call group identifier |
| GroupName | Text | Call group name |
| StationId | Numeric | Identifier of the attached station |
TEAMS: List of on-call teams
| Field name | Data type | Description |
|---|---|---|
| TeamId | Numeric | Team identifier |
| TeamName | Text | Team name |
| CallGroupId | Numeric | Identifier of the attached call group |
TAGS: List of supervised tags
| Field name | Data type | Description |
|---|---|---|
| TagId | Numeric | Tag identifier |
| StationId | Numeric | Identifier of the attached station |
| Label | Text | Alphanumeric tag identifier |
| GroupId | Numeric | Identifier of the attached tag group |
| CallGroupId | Numeric | Identifier of the attached call group |
| Message | Memo | Associated text message |
| Priority | Numeric | Alarm priority |
| Pulse | Numeric | Transitory alarm |
| Reference | Text | Data path |
| Name | Text | Tag name |
USERPROFILES: List of user profiles
| Field name | Data type | Description |
|---|---|---|
| ProfileId | Numeric | Profile identifier |
| ProfileName | Text | Profile name |
| ProfileLevel | Numeric | Basic access level of the profile |
USERS: List of users
| Field name | Data type | Description |
|---|---|---|
| UserId | Numeric | User identifier |
| UserName | Text | User name |
| UserFirstName | Text | User first name (can be NULL) |
| StationId | Numeric | Identifier of the attached station |
| UserNumericId | Numeric | Telephonic identifier of the user |
| ProfileId | Numeric | Identifier of the attached profile |
| LanguageId | Numeric | Code of the user language |
| UserType | Numeric | User type (1=virtual, 2=console, ...) |
DRIVERS: List of installed communication drivers
| Field name | Data type | Description |
|---|---|---|
| DriverId | Numeric | Driver identifier |
| DriverName | Text | Driver name |
| DriverType | Numeric | Driver type (vocal, fax, SMS, ...) |
| CountryCode | Text | Country code (2 characters, NULL is not localized) |
CALLNUMBERS: List of user call numbers
| Field name | Data type | Description |
|---|---|---|
| NumberId | NumAuto | Call number identifier |
| UserId | Numeric | Identifier of the attached user |
| EntryNumber | Numeric | Entry number in the user directory |
| DriverId | Numeric | Identifier of the communication driver to use |
| Address | Text | Call number or address |
CALLGROUPS_USERS: Table of assignment of users to call groups
| Field name | Data type | Description |
|---|---|---|
| CallGroupUserId | Numeric | Link identifier |
| CallGroupId | Numeric | Identifier of the attached call group |
| UserId | Numeric | Identifier of the attached user |
| Relief | Boolean | Relief indicator for operator in the group |
TEAMS_USERS: Table of assignment of operators to on-call teams
| Field name | Data type | Description |
|---|---|---|
| TeamUserId | Numeric | Link identifier |
| TeamId | Numeric | Identifier of the attached on-call team |
| UserId | Numeric | Identifier of the attached user |
| UserIndex | Numeric | Position of the user in the team |
| Relief | Boolean | Relief indicator for operator in the team |
History tables
The history tables contain all dynamic data recorded by the software: alarms, calls, reports.
The history tables are the following tables:
ALARMLOG: Alarm history log
| Field name | Data type | Description |
|---|---|---|
| AlarmLogId | NumAuto | Identifier of the alarm event |
| TagId | Numeric | Identifier of the tag in alarm (cf. TAGS) |
| AlarmTime | Date/Time | Time-stamp of the alarm activation (GMT) |
| Message | Memo | Formatted alarm message |
| ResetTime | Date/Time | Time-stamp of the alarm reset (GMT) |
| UserId | Numeric | Identifier of the user who acknowledged the alarm (cf. USERS) |
| AckTime | Date/Time | Time-stamp of the alarm acknowledgment (GMT) |
| AlarmValue | Text | Tag value at the alarm time |
| CommentText | Memo | Comment associated with the alarm |
| AlarmLocalTime | Date/Time | Time-stamp of the alarm activation (Local) |
| ResetLocalTime | Date/Time | Time-stamp of the alarm reset (Local) |
| AckLocalTime | Date/Time | Time-stamp of the alarm acknowledgment (Local) |
| EventType | Numeric | 0 = Alarm, 1 = Event |
The alarm history table is updated in real time for every alarm event (activation, reset, acknowledgment).
CALLGROUPLOG: group call history log
| Field name | Data type | Description |
|---|---|---|
| CallGroupLogId | NumAuto | Identifier of the group call |
| CallGroupId | Numeric | Identifier of the call group (cf. CALLGROUPS) |
| BeginTime | Date/Time | Time-stamp of the begin of the call cycle (GMT) |
| EndTime | Date/Time | Time-stamp of the end of the call cycle (GMT) |
| Status | Numeric | 0 = No call, 1 = Call in progress, 2 = Call transmitted (wait for call acknowledgment), 3 = Group not warned (call fail), 4 = Group warned (calls acknowledged), 5 = Relief group called, 6 = Call delayed, 7 = Relief group not warned, 8 = Relief group warned, 9 = Call finished (no more alarm for the group), 10 = Call group active wait (Relief only), 11 = Call group passive wait (Relief only), 12 = Call canceled |
| BeginLocalTime | Date/Time | Time-stamp of the begin of the call cycle (Local) |
| EndLocalTime | Date/Time | Time-stamp of the end of the call cycle (Local) |
The call group table is updated at the beginning of the call cycle (EndTime and Status fields set to NULL) and at the end of the call cycle.
CALLLOG: user call history log
| Field name | Data type | Description |
|---|---|---|
| CallLogId | NumAuto | Identifier of the user call |
| UserId | Numeric | Identifier of the called user (cf. USERS) |
| DriverId | Numeric | Identifier of the used communication driver (cf. DRIVERS) |
| CallNumber | Memo | Called number |
| BeginTime | Date/Time | Time-stamp of the begin of the call (GMT) |
| EndTime | Date/Time | Time-stamp of the end of the call (GMT) |
| Status | Numeric | 0 = no call performed, 1 = call aborted, 2 = call suspended, 3 = call transmitted (wait for call acknowledgment), 4 = call failed, 5 = call acknowledged, 6 = call not acknowledged (failed), 7 = call cancelled, 8 = call acknowledged after one fail, 9 = call non-validating successed, 10 = call non-validating failed |
| FailReport | Text | Detailed error report |
| BeginLocalTime | Date/Time | Time-stamp of the begin of the call (Local) |
| EndLocalTime | Date/Time | Time-stamp of the end of the call (Local) |
The call table is updated at the beginning of the call (EndTime and Status fields set to NULL) and at the end of the call.
CALLLOGMESSAGE: Message call history log
| Field name | Data type | Description |
|---|---|---|
| CallLogMessageId | NumAuto | Identifier of the message call |
| CallLogId | Numeric | Identifier of the user call (cf. CALLLOG) |
| Message | Text | Message associated to user call |
The message call table can contains same user call identifier with multiple messages in case of multiple message are sent in one call (ex: Email).
REPORTLOG: report history log
| Field name | Data type | Description |
|---|---|---|
| ReportLogId | NumAuto | Identifier of the report |
| Report Date | Date/Time | Time-stamp of the first report edition (GMT) |
| UserId | Numeric | Identifier of the report author (cf. USERS) |
| TopicId | Numeric | Identifier of the report topic (cf. TOPICS) |
| ReportText | Memo | Report content |
| AudioFile | Text | Name of the attached audio file |
| AttachedFile | Memo | List of attached files |
| AlarmReport | Boolean | Indicator for alarm report |
| ReportLocalDate | Date/Time | Time-stamp of the first report edition (Local) |
The report log is updated after each report edition (creation or modification).
REPORTTOPIC: list of report topics
| Field name | Data type | Description |
|---|---|---|
| TopicId | NumAuto | Identifier |
| TopicTexte | Text | Topic label |
The following tables contain the list of crossed references between the alarms and the consecutive group calls (ALRMTOCALLGROUP), between the group calls and the associated user calls (CALLTOCALLGROUP), and the alarms and the associated reports (ALARMTOREPORT).
ALARMTOCALLGROUP: link alarms / group calls
| Field name | Data type | Description |
|---|---|---|
| AlarmToCallGroupId | NumAuto | Identifier |
| AlarmId | Numeric | Identifier of the alarm event (cf. ALARMLOG) |
| CallGroupId | Numeric | Identifier of the group call (cf. CALLGROUPLOG) |
CALLTOCALLGROUP: links user calls / group calls
| Field name | Data type | Description |
|---|---|---|
| CallToCallGroupId | NumAuto | Identifier |
| CallId | Numeric | Identifier the user call (cf. CALLOG) |
| CallGroupId | Numeric | Identifier of the group call (cf. CALLGROULOG) |
ALARMTOREPORT: links alarms / reports
| Field name | Data type | Description |
|---|---|---|
| AlarmToReportId | NumAuto | Identifier |
| AlarmId | Numeric | Identifier of the alarm event (cf. ALARMLOG) |
| ReportId | Numeric | Identifier of the report (cf. REPORTLOG) |
EVENTLOG: Event logs of Alert (required global option "DataBase.WriteEventLogs" set to "1")
| Field name | Data type | Description |
|---|---|---|
| EventLogId | NumAuto | Identifier |
| StationId | Numeric | Station identifier (cf. STATIONS) |
| DateTime | Date/Time | Time-stamp of the event (GMT) |
| LocalDateTime | Date/Time | Time-stamp of the event (Local) |
| EventType | Numeric | 0 = Alert Start, 1 = Alert Stop, 2 = Login, 3 = Logout, 4 = Manual outgoing call, 5 = Automatic outgoing call, 6 = Call success, 7 = Call not answered or not acknowledged, 8 = Communication failure, 9 = Alarm, 10 = Alarm acknowledgment, 11 = Alarm reset, 12 = Call acknowledged, 13 = Event, 14 = Incoming call, 15 = Team call success, 16 = Team call fail, 17 = Derogation start, 18 = Derogation end, 19 = System date changed, 20 = Remote login, 21 = Port disconnected, 22 = Port failed, 23 = Link failed, 24 = Information, 25 = Operator offduty, 26 = Operator replaced by, 27 = Alarm masked, 28 = Alarm unmasked, 29 = Port connection, 30 = Station disabled, 31 = Station enabled, 32 = Remote logout, 33 = Error, 34 = Operator onduty, 35 = Call rejected, 36 = Team relief, 37 = Relief fail, 38 = End relief, 39 = Operator status change, 40 = Call success but not acknowledged, 41 = Start/Stop polling, 42 = Outgoing call for transfer, 43 = License fail |
| Info | Text | Information of event |