The rostrvm database contains a number of tables that hold historical data as follows:
Session details for each station, line, and routing number device. |
|
A historic record of agent state changes for each station. |
|
A historic record of all the calls and contacts handled by rostrvm devices. |
|
A historic record of all the connections involved in each call and contact. |
|
An audit trail of the activity codes and their associated values. |
|
Record of all dialler accounts on which a callback has been set by the dialler. |
|
Record of all dialler accounts which have been imported through OutBound. |
|
Record of all email, chat, SMS text, voice and screen recordings with associated custom data. |
|
A list of all archived emails, chats or voice and screen recordings. |
|
The voice and screen recordings of each leg of the calls. |
|
Additional real-time user information recorded alongside user configuration details. |
|
A record of when a list is imported, purged, reset or split from an existing one. |
Table Relationships
Relationships between the tables (for the purpose of SQL joins) can be defined using a number of different fields. The following diagrams illustrate some of the more common fields that can be used for such joins. In each case, the field listed in each destination table is referenced from the origin table.
The tables in RED ITALICS are configuration tables which contain some fields that are used by the rostrvm Manager displays.
Contents
One session records for all points in time for each rostrvm station, line and routing number.
Row Added
| • | On start-up or following a re-configuration when a new device is added. |
| • | When the session type has changed (i.e. the agent has logged on or logged off or the user has connected or disconnected). |
Record Recovery
On start-up if the END_TIME is null for this device.
Record Definition
Column Name |
Description |
Type |
SESSION_ID |
Unique Id to identify the session. |
Int |
START_TIME |
Logon time for user or agent. For lines and routing numbers it is the time of the last configuration change. |
D/t |
END_TIME |
Agent/user log off time. For lines and routing numbers this is NULL or the time that the particular configuration was valid for the device. |
D/t |
TYPE |
Station, Line, HCDN, User, Agent, Archived User & Agent imply device is a station. If a user connects (or an agent logs on) to any other device type it is ignored (i.e. this column is not updated). HCDN is an historic term for a CallDirector routing number. Archived is a record that is accessed from a voice or screen recording. |
Enum |
DEVICE DEVICE_DESCRIPTION |
The device details - rostrvm device id and description (as per configuration). |
Str |
USER_NAME FULL_NAME USER_GROUP |
The user details - rostrvm user Id, full name (as per configuration), and the user group to which they belong. Applicable to user and agent sessions only. |
Str |
AGENT_GROUP |
The rostrvm group to which they have logged on (applicable to agent sessions only). |
Str |
LOGOFF_REASON |
The logoff reason code (applicable to agent sessions only). |
Str |
NAIL_UP |
The telephone number or SIP URL used to connect the agent's turret to the rostrvm system (applicable to agent sessions only). |
Str |
TIME_TO_ |
Seconds before this agent logs on again. This is set for the previous record when the agent logs on again (i.e. it is NULL in the last agent session). |
Int |
Contents
Agent state times (and connection reference if applicable) for when the agent last entered the Not Ready state.
Row Added
| • | When an agent changes state to or from anything other than Busy, and the total state times are greater than 1 second. Records of 1 second or less are carried forward to the next record (this is to avoid a 0 or 1 second Ready entry for every dialler preview/progressive call attempt). |
Record Recovery
Never. However, if the record already exists (i.e. following a SESSIONS entry recovery for this device on recorder re-start) then it will be overwritten rather than a new one created.
Record Definition
Column Name |
Description |
Type |
SESSION_ID |
The agent session to which the record is associated. |
Int |
START_TIME |
Time of agent logon or the time that the agent entered not ready. |
D/t |
CALL_REFERENCE |
The call that is associated with the agent state timings, or null if not applicable. Note for multiple legs (consultation calls) the Wrap-up time is associated with the last leg whereas the Ready and Not Ready times are associated with the first leg. Busy time spans all legs. |
Int |
READY_TIME |
Number of seconds the agent spent in the ready state. |
Int |
BUSY_TIME |
Number of seconds the agent spent in the busy state. |
Int |
WRAPUP_TIME |
Number of seconds the agent spent in the wrap-up state. |
Int |
NOT_READY_TIME |
Number of seconds the agent spent in the not ready state. |
Int |
NOT_READY_REASON |
Cause code for the this not ready state change. |
Str |
Contents
The details for each rostrvm call or contact. A single entry exists regardless of how many times the call was routed, transferred or requeued to different devices.
Row Added
| • | When a new call (or contact) is started. |
Record Recovery
When the recorder is re-started whilst a call is in progress.
Record Definition
Column Name |
Description |
Type |
CALL_REFERENCE |
The reference which uniquely identifies the call. |
Int |
START_TIME |
Time of the first event associated with the call. |
D/t |
END_TIME |
Time of last recorded event on the call. |
D/t |
CLASS |
Last class associated with the call (Campaign for Outbound). |
Str |
ACCOUNT_ID |
For OutBound generated calls the account id associated with the call that was made. |
Str |
LIST |
For OutBound generated calls the list from which the call was generated. |
Str |
SKILL |
For inbound skilled based routing calls the skill associated with the call. |
Str |
DIALLED_DIGITS |
Original number dialled to originate the call. |
Str |
ORIGIN |
Originating party (either a remote DN or a rostrvm device id). |
Str |
REMOTE_PARTY |
The remote party involved in this call (with any configured prefixes removed). |
Str |
ORIGIN_TYPE |
Inbound, Outbound, Predictive, Preview, Progressive, Manual Outbound, Dialler Manual, Inbound Email, Outbound Email, Dialler Email, Inbound Chat, Outbound Chat, Outbound SMS, Dialler SMS, Preview Cancelled, Inbound SMS, ScoreCard The type of the contact from the originator's point of view. Note Outbound is only set for outbound calls initiated from a line. |
Enum |
REASON_FOR |
'Inbound' if a remote originated the call; '<callback type>' for dialler callbacks; 'OutBound' if the original destination is a remote (non-callback); 'Internal' otherwise. |
Str |
BILL_TIME |
Number of milliseconds that the agent has been connected (including held) to a remote device for outbound calls only. This includes outbound consultation calls. If the agent makes an outbound primary and an outbound consultation to 2 remotes then this time will be double the elapsed time. |
Int |
LINKED |
Call reference of a linked call (used for ScoreCard calls). |
Int |
Contents
The details for each rostrvm connection to a call or contact. Connection records are only maintained for rostrvm configured Stations, Lines, and routing numbers. They are not added for remote devices. ACD queues are recorded as a field within the routing number or Line entry whenever the call is queued.
Row Added
| • | When a new connection is created. This is as a result of a new party being added to the contact notification (connection state is not null). |
| • | When the call class changes during a call. |
Record Recovery
We attempt to recover station silent monitor authentication legs. To do this a check is made before creating a new entry and if a record with the same call reference already exist for that devices session, and the entry is less than 4 seconds old, then it is recovered.
Record Definition
Column Name |
Description |
Type |
CONNECTION_ID |
Unique identifier for the record. |
Int |
CALL_REFERENCE |
The call reference to which this connection belongs. |
Int |
START_TIME |
Time that the connection started. |
D/t |
END_TIME |
Time that the connection ended. |
D/t |
SESSION_ID |
The session Id for the rostrvm device for this connection. |
Int |
TYPE |
Outbound, Inbound, Failed, Preview, Conference, Transfer In, Outbound Consultation, Monitoring, Recovered, IVR, Queued, Routing, Inbound Consultation, Hunting, Queueback, Archived, ScoreCard The type of call. All but IVR, Queued and Routing are set for Station connections only. Queued can be for either a Line or Routing Number session. Hunting is a consultation call from a line. Archived is a record that is accessed from a voice or screen recording. |
Enum |
CLASS |
Call class that was assigned to the call when this connection ended. |
Str |
QUEUE |
Name of the queue that the call is in during this connection when the type is Queue. |
Str |
REMOTE_PARTY |
Other party (may be Remote DN or a rostrvm device id) with any configured prefixes removed. |
Str |
SWITCH_REASON |
The final switch generated reason for the termination of the call. |
Str |
ALERT_TIME |
The number of seconds the call was alerting. |
Int |
HOLD_OR_Q_TIME |
The number of seconds the connection was in the held or queued state. |
Int |
CONNECT_TIME |
The number of seconds the call was connected (i.e. talking state). |
Int |
TRANSFERRED_TO |
The device or remote party this connection was transferred to if it was requeued, transferred, diverted, or routed. |
Str |
MEDIA_TYPE |
Call, Email, Chat, Unread Voice Mail, Voice Mail, Deleted Voice Mail, IVR, SMS This is the type of the media recorded in the MEDIA_RECORDING table for this connection (NULL if there is not media). |
Enum |
SCREEN |
The connection ID of the screen recording associated with this connection (may be different to the connection id in the case of multiple contacts at a station). This is NULL if there is no screen recording. This refers to the CONNECTION_ID in the MEDIA_RECORDING table (or the archived equivalent) with an encoding type of RFB. |
Int |
ARCHIVE_ID |
Specifies the archive in which the media is stored (if it is no longer in the main database). The name of the archive may be found by using this reference against the MEDIA_ARCHIVES table. The actual media is stored in the MEDIA_RECORDING table within the archive. |
Int |
Contents
One record for each activity code entered against a call.
Row Added
| • | When a new activity code is received for the call, or a new value is received for an already set activity code. |
Record Recovery
Never.
Record Definition
Column Name |
Description |
Type |
CONNECTION_ID |
The connection identity against which this activity was set. |
Int |
CODE |
The activity code. |
Int |
VALUE |
Value associated with the activity. |
Str |
SET_TIME |
The time that the code was first set. |
D/t |
Contents
One record for each callback that is set through the rostrvm OutBound product.
Row Added
| • | Whenever a callback is set by the dialler. |
Record Recovery
Never.
Record Definition
Column Name |
Description |
Type |
CONNECTION_ID |
Connection upon which the callback was set (if set by a call). |
Int |
ACCOUNT_ID |
Unique identifier for the outbound record. |
Str |
TYPE |
Stale, Agent, Busy, Fail, No Answer, Answer Machine, Cancelled, Nuisance, Rejected, Undefined, Hungup After Answer, Activity Code, System Fail The type of callback. Note that these values reflect the types recorded in DLR_ACCOUNTS. |
Enum |
SET_TIME |
Date and time the callback was set. |
D/t |
CALL_BACK_TIME |
Date and time that the callback is scheduled. |
D/t |
LATEST_TIME |
Latest time of day to perform the callback. |
Time |
USER_NAME |
Name of the user assigned to the callback (NULL if it does not matter). |
Str |
Contents
One record for each entry in DLR_ACCOUNTS.
Row Added
| • | Whenever an equivalent field in DLR_ACCOUNTS is updated. |
Record Recovery
Never.
Record Definition
Column Name |
Description |
Type |
ACCOUNT_ID |
Account Identifier. |
Str |
ACCOUNT_REF |
Account reference. |
Str |
CLASS |
Last campaign to which this account was assigned. |
Str |
NAME |
The name associated with this account. |
Str |
LIST |
List with which the account is associated. |
Str |
STATUS_CODE |
FilteredOut, Terminated, Callback, Active, Priority Current status of the account (blank means Unused). |
Enum |
TERM_CODE |
Max Calls to Busy, Max Calls to Unavailable, Max Calls to No Answer, Contacted, Excluded, Max Nuisance Calls, Max Calls to Answer Machine, Max Call Cancellations, Max Calls Rejected, Max Calls Failed, Agent Cancelled Callback, Max Activity Outcome, Max Overall Call Attempts, Max Hungup After Answer This account termination code. |
Enum |
DOWN_LOAD_DATE |
Time the account was put into the system. |
D/t |
EXPIRY_DATE |
Time from which the account is no longer valid for dialling. |
D/t |
CALL_BACK_TIME |
The latest callback date and time (this may or may not have been dialled). |
D/t |
LAST_UPDATED |
Time of the last change to any field within the account. |
D/t |
SKILL_NAME |
The required skill for this account. |
Str |
TEL_NUM_1 to 4 |
The telephone numbers associated with this account. |
Str |
SMS_NUMBER |
The SMS number associated with the account. |
Str |
EMAIL_ADDR |
The email address associated with the account |
Str |
Contents
Contains a variable number of entries (possibly no entries) corresponding to each email, chat, SMS text, voice recording.
Row Added
| • | Whenever a new contact of the appropriate type or voice recording is archived by the rostrvm server components. |
Record Recovery
Never.
Record Definition
Column Name |
Description |
Type |
CONNECTION_ID |
The identity of the CONNECTIONS record associated with this entry. |
Int |
ITEM_NAME |
The name of the data store column that has the 'Keep History' check box ticked, that is associated with the contact and contains information. |
Str |
VALUE |
Values set for the data store on this contact (only non-blank values are stored). |
Str |
Contents
This table contains an entry for each email/chat/voice archive file found in the archive location. The entries identify the file name and the time the archive was last modified.
Row Added
| • | Each time a new email, chat or voice archive is placed in, or found in, the archive location. |
Record Recovery
| • | When an archive file is no longer present in the archive area. |
Record Definition
Column Name |
Description |
Type |
ARCHIVE_ID |
A unique identity of the archive, this is the key used by the MEDIA_INDEX table. |
Int |
PATH |
Full path name of the archive file. |
Str |
LAST_MODIFIED |
Modification date of the archive file the last time it was indexed. This is used to detect changes to archive files and archive them as appropriate. |
D/t |
Contents
This table contains active email, chat, voice and screen recordings and recordings that are waiting to be archived. An hourly archive process runs to move completed recordings into archive files.
Row Added
| • | When an email, chat or call is recorded |
Record Recovery
| • | When an email, chat or call is moved into an archive file. |
Record Definition
Column Name |
Description |
Type |
CONNECTION_ID |
The reference to the CONNECTIONS entry with which the recording is associated. |
Int |
TIME_OFFSET |
As the recordings are made they are broken into segments. This field provides an time offset in milliseconds from the start of the recording and is used to re-assemble the segments. |
Int |
ENCODING |
GSM, Text, Header, Binary, RFB The type of data recorded: GSM for sound data, Text for chat interactions, Header, Text and Binary for emails, and RFB (Remote Frame Buffer) for screen recordings. |
Enum |
MEDIA |
The actual sound data or contact text. |
Blob |
Contents
This is an existing configuration table which is being extended to effectively hold real-time information about the users.
Users are only recorded if they are configured with USER_DETAILS.ACCESS_TYPE = 0 (i.e. non-controlling users are ignored).
Row Added
| • | Through Manager when a new user is added, |
Record Recovery
Never.
Record Definition (real-time MIS columns only)
Column Name |
Description |
Type |
USER_NAME |
The identity of the user (note this is created by Manager when a new record is added). |
Str |
SESSION_ID |
This references the last session record associated with the user (this is updated when the user and/or agent logs on). If the user has never logged on, or is currently not logged on, then it is set to logged off (if an agent Id is configured) or is null. |
Int |
CONNECTION_ID |
Currently connected call. |
Int |
AGENT_STATE |
Logged Off, Ready, Not Ready, Wrap Up, Preview, Alerting, Remote Alerting, Hold, Connected, Conference Current agent state (if user is an agent). If the agent state is busy then a call state is set. |
Enum |
STATE_START_TIME |
Date/time of last update to the state. |
D/t |
MOVING_TO |
If a request to move an agent from their current agent group to another agent group is outstanding (i.e. agent is not yet in a Ready state), then this is the new target agent group. |
Str |
STATUS |
Either the not ready reason or logged off reason code string. |
Str |
LAST_LOGON |
Last time the user logged on to (or off from) rostrvm. |
D/t |
Contents
Defines the operations performed on each dialling list.
Row Added
| • | Whenever a list is imported (either as an Overwrite, Append, Update or Singular Import operation); |
| • | Whenever a list is Purged or Reset; |
| • | Whenever a new list is created from an existing list or split from one. |
Record Recovery
None.
Record Definition
Column Name |
Description |
Type |
LIST |
The name of list upon which is being operated or is being imported. |
Str |
TYPE |
created, overwrite, append, purge, single, delete, newlist, splitlist, reset, update, addition, removal, cbsreset, cbswindow, cbstime, cbsmove, cbsexpreset The type of list operation that is being logged. |
Enum |
TIMESTAMP |
The time at which the list operation was performed. |
D/t |
DATESTAMP |
The date at which the list operation was performed. |
Date |
RECORD_COUNT |
The number of accounts in the list after the specified operation has completed. |
Int |
NUM_IMPORTED_OK |
The number of accounts successfully imported from a supplied data source. |
Int |
NUM_EXCLUDED_DUP |
The number of accounts left un-imported from a supplied data source due to the de-duplication function. |
Int |
NUM_EXCLUDED_BLANK |
The number of accounts left un-imported from a supplied data source due to having blank telephone numbers. |
Int |
NUM_EXCLUDED_TPS |
The number of accounts left un-imported from a supplied data source due to their telephone numbers being present in the TPS list. |
Int |
NUM_EXCLUDED_SML |
The number of accounts left un-imported from a supplied data source due to their telephone numbers being too short. The minimum length of acceptable telephone numbers is defined in the rostrvm registry by the OlmMinTelNumLen key. If not defined, the default value is 11. |
Int |
NUM_EXCLUDED_ALP |
The number of accounts left un-imported from a supplied data source due to their telephone numbers containing unwanted alpha characters. This feature of rejecting accounts due to the presence of alpha characters is disabled by default. It can be enabled by setting the OlmRejectAlphaNums rostrvm registry key to 1. |
Int |
NUM_EXCLUDED_FAIL |
The number of accounts left un-imported from a supplied data source due to reasons other than those specified above. |
Int |
NUM_PURGED |
The number of accounts removed from a list as a result of the specified operation type. |
Int |
DAILY_STATUS |
Enumerated flag field indicating whether a given operation was the latest to be performed on a specific list on any given day or not. Values are Latest or Historic - i.e. the last operation to be performed on a list on any day will have a DAILY_STATUS value of Latest and any preceding entries for that list on that same day will have a value of Historic. |
Enum |
ACCOUNT_ID |
This is the account id when a single account is added or removed from a list. |
Str |