Historical Reports Database Tables
The following diagram shows the historical reports tables and their relationships Ivanti Voice automatically creates the first time Ivanti Voice connects to the Ivanti Voice database.
The AGENT_AUDIT table is part of the database structure, but is not linked to any of the other tables.
The following topics describe the contents of each database table.
REPORTS Table
The root table to which all other tables are linked either directly or indirectly. The configuration defines one or more historical reports, each of which is a collection of metric values, stored in the metric tables (CC_METRICS, AGENT_METRICS, etc.)
The default configuration defines only one report named Default. Ivanti, Inc. does not recommend defining more than one report.
|
Column |
Type |
Description |
|---|---|---|
| ID | int | The unique identifier of the report. |
| NAME |
nvarchars (64) |
The unique name of the report. The name is copied from the configuration. |
OBJECT_TYPES Table
A list that defines readable names for integer object type identifiers. This table reduces the database space needed for records in the OBJECTS table.
|
Column |
Type |
Description |
|---|---|---|
| ID | int |
The unique identifier of the object type. This ID points to object types in another table. |
| NAME |
nvarchar (64) |
The display name of the object type (either agent, agent group, contact center, or service). |
OBJECTS Table
A list of objects for which metric values are written in the database. One record is created in this table for each agent, agent group, service or contact center. The record defines a unique integer identifier of the object and gives the object a readable name that is shown in historical reports. The table also refers to the OBJECT_TYPES table so the display name of the object type may be shown in historical reports, built for the object.
|
Column |
Type |
Description |
|---|---|---|
| ID | int identity | The unique identifier of the object. |
| DISPLAY_NAME |
nvarchar (128) |
The display name of the object. |
| OBJECT_TYPE | int | The reference to the object type (OBJECT_TYPES.ID) |
AGENT_METRICS Table
Stores the recorded values of statistics for all agents. Each record in this table contains a reference to the object for which values are written, and the begin and end times for the interval for which Ivanti Voice collected the values. The table stores all time values in milliseconds.
|
Column |
Type |
Description |
|---|---|---|
| REPORT_ID | int | The unique identifier of the report. |
| OBJECT_ID | int | The unique identifier of the object. |
| BEGIN_TIME | date/ time | The beginning time of the interval. This value uses coordinated universal time (UTC). |
| END_TIME | date/ time | The ending time of the interval. This value uses coordinated universal time (UTC). |
| V_CALLS_ANSWERED | bigint | The values of the Number of calls answered statistic. |
| V_WRAPUP_TIME | bigint | The values of the Total after call work time statistic. |
| V_READY_TIME | bigint | The values of the Total ready time statistic. |
| V_NOT_READY_TIME | bigint | The values of the Total not ready time statistic. |
| V_WORK_TIME | bigint | The values of the Total working time statistic. |
| V_TOTAL_BUSY_TIME | bigint | The values of the Total busy time statistic. |
| V_HELD_TIME | bigint | The values of the Total held time statistic. |
| V_TOTAL_HANDLING_TIME | bigint | The values of the Total handling time statistic. |
| V_LOGON_TIME | bigint | The values of the Total logon time statistic. |
| V_INT_RETURNED | bigint | The values of the Number of returned interactions statistic. |
| V_TRANSFERS_ORIGINATED | bigint | The values of the Number of originated transfers statistic. |
| V_TRANSFERS_ACCEPTED | bigint | The values of the Number of accepted transfers statistic. |
| V_TRANSFERS_REJECTED | bigint | The values of the Number of rejected transfers statistic. |
| V_ANSWERED_RETURNS | bigint | The values of the Number of answered returned interactions statistic. |
| V_EI1_ANSWERED | bigint | The number of calls answered on escalation interval 1. |
| V_EI2_ANSWERED | bigint | The number of calls answered on escalation interval 2. |
| V_EI3_ANSWERED | bigint | The number of calls answered on escalation interval 3. |
| V_EI4_ANSWERED | bigint | The number of calls answered on escalation interval 4. |
| V_EI5_ANSWERED | bigint | The number of calls answered on escalation interval 5. |
| V_EIR_ANSWERED | bigint | The number of calls answered after all escalation intervals. |
| V_OUT_CALLS_MADE | bigint | The number of outbound calls made. |
| V_OUT_TOTAL | bigint | The values of the Total Outbound Time statistic. |
| V_OUT_HANDLING | bigint | The values of the Outbound Handling Time statistic. |
| V_OUT_WRAP_UP | bigint | The values of the Outbound Wrap-up Time statistic. |
| V_TIME_TO_ANSWER | bigint | The values of the Time to Answer statistic. |
| V_DELIVERY_ATTEMPTS | bigint | The values of the Delivery Attempts statistic. |
| V_IDLE_TIME | int | The values of the Idle Time statistic. |
| V_IDLE_STATE_COUNT | int | The values of the Idle State Count statistic. |
AGENT_GROUP_METRICS Table
Stores the recorded values of statistics for all agent groups. Each record in this table contains a reference to the object for which values are written, and the begin and end times for the interval for which Ivanti Voice collected the values. The table stores all time values in milliseconds.
|
Column |
Type |
Description |
|---|---|---|
|
REPORT_ID |
int |
The unique identifier of the report. |
|
OBJECT_ID |
int |
The unique identifier of the object. |
|
BEGIN_TIME |
date/ time |
The beginning time of the interval. This value uses coordinated universal time (UTC). |
|
END_TIME |
date/ time |
The ending time of the interval. This value uses coordinated universal time (UTC). |
|
V_CALLS_ANSWERED |
bigint |
The values of the Number of calls answered statistic. |
|
V_WRAPUP_TIME |
bigint |
The values of the Total after call work time statistic. |
|
V_READY_TIME |
bigint |
The values of the Total ready time statistic. |
|
V_NOT_READY_TIME |
bigint |
The values of the Total not ready time statistic. |
|
V_WORK_TIME |
bigint |
The values of the Total working time statistic. |
|
V_TOTAL_BUSY_TIME |
bigint |
The values of the Total busy time statistic. |
|
V_HELD_TIME |
bigint |
The values of the Total held time statistic. |
|
V_HANDLING_TIME |
bigint |
The values of the Total handling time statistic. |
|
V_LOGON_TIME |
bigint |
The values of the Total logon time statistic. |
|
V_INT_RETURNED |
bigint |
The values of the Number of returned interactions statistic. |
|
V_TRANSFERS_ORIGINATED |
bigint |
The values of the Number of originated transfers statistic. |
|
V_TRANSFERS_ACCEPTED |
bigint |
The values of the Number of accepted transfers statistic. |
|
V_TRANSFERS_REJECTED |
bigint |
The values of the Number of rejected transfers statistic. |
|
V_ANSWERED_RETURNS |
bigint |
The values of the Number of answered returned interactions statistic. |
|
V_MIN_LOGGED_IN_AGENTS |
bigint |
The maximum number of logged-in agents. |
|
V_MAX_LOGGED_IN_AGENTS |
bigint |
The minimum number of logged-in agents. |
|
V_MAX_QUEUED_TIME |
bigint |
The maximum time an interaction spent in the queue. |
|
V_EI1_ANSWERED |
bigint |
The number of calls answered on escalation interval 1. |
|
V_EI2_ANSWERED |
bigint |
The number of calls answered on escalation interval 2. |
|
V_EI3_ANSWERED |
bigint |
The number of calls answered on escalation interval 3. |
|
V_EI4_ANSWERED |
bigint |
The number of calls answered on escalation interval 4. |
|
V_EI5_ANSWERED |
bigint |
The number of calls answered on escalation interval 5. |
|
V_EIR_ANSWERED |
bigint |
The number of calls answered after all escalation intervals. |
|
V_OUT_CALLS_MADE |
bigint |
The number of outbound calls made. |
|
V_OUT_TOTAL |
bigint |
The values of the Total Outbound Time statistic. |
|
V_OUT_HANDLING |
bigint |
The values of the Outbound Handling Time statistic. |
|
V_OUT_WRAP_UP |
bigint |
The values of the Outbound Wrap-up Time statistic. |
|
V_TIME_TO_ANSWER |
bigint |
The values of the Time to Answer statistic. |
|
V_DELIVERY_ATTEMPTS |
bigint |
The values of the Delivery Attempts statistic. |
CC_METRICS Table
Stores the recorded values of statistics for the entire contact center. Each record in this table contains a reference to the object for which values are written, and the begin and end times for the interval for which Ivanti Voice collected the values. The table stores all time values in milliseconds.
|
Column |
Type |
Description |
|---|---|---|
|
REPORT_ID |
int |
The unique identifier of the report. |
|
OBJECT_ID |
int |
The unique identifier of the object. |
|
BEGIN_TIME |
date/ time |
The beginning time of the interval. This value uses coordinated universal time (UTC). |
|
END_TIME |
date/ time |
The ending time of the interval. This value uses coordinated universal time (UTC). |
|
V_CALLS_ANSWERED |
bigint |
The values of the Number of calls answered statistic. |
|
V_TOTAL_WRAPUP_TIME |
bigint |
The values of the Total after call work time statistic. |
|
V_READY_TIME |
bigint |
The values of the Total ready time statistic. |
|
V_NOT_READY_TIME |
bigint |
The values of the Total not ready time statistic. |
|
V_WORK_TIME |
bigint |
The values of the Total working time statistic. |
|
V_TOTAL_BUSY_TIME |
bigint |
The values of the Total busy time statistic. |
|
V_TOTAL_HANDLING_TIME |
bigint |
The values of the Total handling time statistic. |
|
V_LOGON_TIME |
bigint |
The values of the Total logon time statistic. |
|
V_INT_RETURNED |
bigint |
The values of the Number of returned interactions statistic. |
|
V_TRANSFERS_ORIGINATED |
bigint |
The values of the Number of originated transfers statistic. |
|
V_TRANSFERS_ACCEPTED |
bigint |
The values of the Number of accepted transfers statistic. |
|
V_TRANSFERS_REJECTED |
bigint |
The values of the Number of rejected transfers statistic. |
|
V_ANSWERED_RETURNS |
bigint |
The values of the Number of answered returned interactions statistic. |
|
V_SERVICE_FACTOR |
bigint |
The values of the Service factor statistic. |
|
V_CALLS_RECEIVED |
bigint |
The values of the Number of calls received statistic. |
|
V_CALLS_ABANDONED |
bigint |
The values of the Number of calls abandoned statistic. |
|
V_CALLS_SHORT_ABANDONED |
bigint |
The values of the Number of calls short-abandoned statistic. |
|
V_CALLS_QUEUED |
bigint |
The values of the Calls queued statistic. |
|
V_TOTAL_QUEUED_TIME |
bigint |
The values of the Total time in queue statistic. |
|
V_ABANDON_TIME |
bigint |
The values of the Total abandon time statistic. |
|
V_INT_IVR_DISCONNECTED |
bigint |
The values of the Number of disconnected in IVR interactions statistic. |
|
V_MIN_LOGGED_IN_AGENTS |
bigint |
The maximum number of logged-in agents. |
|
V_MAX_LOGGED_IN_AGENTS |
bigint |
The minimum number of logged-in agents. |
|
V_MAX_QUEUED_TIME |
bigint |
The maximum time an interaction spent in the queue. |
|
V_EI1_ANSWERED |
bigint |
The number of calls answered on escalation interval 1. |
|
V_EI2_ANSWERED |
bigint |
The number of calls answered on escalation interval 2. |
|
V_EI3_ANSWERED |
bigint |
The number of calls answered on escalation interval 3. |
|
V_EI4_ANSWERED |
bigint |
The number of calls answered on escalation interval 4. |
|
V_EI5_ANSWERED |
bigint |
The number of calls answered on escalation interval 5. |
|
V_EIR_ANSWERED |
bigint |
The number of calls answered after all escalation intervals. |
|
V_EI1_ABANDONED |
bigint |
The number of calls abandoned on escalation interval 1. |
|
V_EI2_ABANDONED |
bigint |
The number of calls abandoned on escalation interval 2. |
|
V_EI3_ABANDONED |
bigint |
The number of calls abandoned on escalation interval 3. |
|
V_EI4_ABANDONED |
bigint |
The number of calls abandoned on escalation interval 4. |
|
V_EI5_ABANDONED |
bigint |
The number of calls abandoned on escalation interval 5. |
|
V_EIR_ABANDONED |
bigint |
The number of calls abandoned after all escalation intervals. |
|
V_OUT_CALLS_MADE |
bigint |
The number of outbound calls made. |
|
V_OUT_TOTAL |
bigint |
The values of the Total Outbound Time statistic. |
|
V_OUT_HANDLING |
bigint |
The values of the Outbound Handling Time statistic. |
|
V_OUT_WRAP_UP |
bigint |
The values of the Outbound Wrap-up Time statistic. |
|
V_CALLS_UNQUEUED |
bigint |
Number of calls that had exited queues. |
|
V_MIN_QUEUED_TIME |
bigint |
The values of the Queued Time statistic. |
|
V_MIN_IVR_TIME |
bigint |
Minimum time in the voice application. |
|
V_MAX_IVR_TIME |
bigint |
Maximum time in the voice application. |
|
V_AVG_CALL_DURATION |
bigint |
Average call duration. |
| V_DIVERTED | bigint | Number of inbound calls diverted to destinations other than agents, without putting the calls in queue. |
| V_QUEUED_DIVERTED | bigint | Number of inbound calls diverted to destinations other than agents, after putting the calls in queue. |
| V_DIVERTED_FAIL | bigint |
Number of inbound calls diverted to destinations other than agents, but connected before being answered. |
| V_IN_TOTAL | bigint | The values of the Total Time statistic. |
| V_DELIVERY_ATTEMPTS | bigint | The values of the Delivery Attempts statistic. |
SERVICE_METRICS Table
Stores the recorded values of statistics for all services. Each record in this table contains a reference to the object for which values are written, and the begin and end times for the interval for which Ivanti Voice collected the values. The table stores all time values in milliseconds.
|
Column |
Type |
Description |
|---|---|---|
|
REPORT_ID |
int |
The unique identifier of the report. |
|
OBJECT_ID |
int |
The unique identifier of the object. |
|
BEGIN_TIME |
date/ time |
The beginning time of the interval. This value uses coordinated universal time (UTC). |
|
END_TIME |
date/ time |
The ending time of the interval. This value uses coordinated universal time (UTC). |
|
V_CALLS_ANSWERED |
bigint |
The values of the Number of calls answered statistic. |
|
V_TRANSFERS_ORIGINATED |
bigint |
The values of the Number of originated transfers statistic. |
|
V_TRANSFERS_ACCEPTED |
bigint |
The values of the Number of accepted transfers statistic. |
|
V_TRANSFERS_REJECTED |
bigint |
The values of the Number of rejected transfers statistic. |
|
V_ANSWERED_RETURNS |
bigint |
The values of the Number of answered returned interactions statistic. |
|
V_SERVICE_FACTOR |
bigint |
The values of the Service factor statistic. |
|
V_CALLS_ABANDONED |
bigint |
The values of the Number of calls abandoned statistic. |
|
V_CALLS_SHORT_ABANDONED |
bigint |
The values of the Number of calls short-abandoned statistic. |
|
V_TOTAL_QUEUED_TIME |
bigint |
The values of the Total time in queue statistic. |
|
V_CALLS_QUEUED |
bigint |
The values of the Calls queued statistic. |
|
V_MAX_QUEUED_TIME |
bigint |
The maximum time an interaction spent in the queue. |
|
V_EI1_ANSWERED |
bigint |
The number of calls answered on escalation interval 1. |
|
V_EI2_ANSWERED |
bigint |
The number of calls answered on escalation interval 2. |
|
V_EI3_ANSWERED |
bigint |
The number of calls answered on escalation interval 3. |
|
V_EI4_ANSWERED |
bigint |
The number of calls answered on escalation interval 4. |
|
V_EI5_ANSWERED |
bigint |
The number of calls answered on escalation interval 5. |
|
V_EIR_ANSWERED |
bigint |
The number of calls answered after all escalation intervals. |
|
V_EI1_ABANDONED |
bigint |
The number of calls abandoned on escalation interval 1. |
|
V_EI2_ABANDONED |
bigint |
The number of calls abandoned on escalation interval 2. |
|
V_EI3_ABANDONED |
bigint |
The number of calls abandoned on escalation interval 3. |
|
V_EI4_ABANDONED |
bigint |
The number of calls abandoned on escalation interval 4. |
|
V_EI5_ABANDONED |
bigint |
The number of calls abandoned on escalation interval 5. |
|
V_EIR_ABANDONED |
bigint |
The number of calls abandoned after all escalation intervals. |
|
V_OUT_CALLS_MADE |
bigint |
The number of outbound calls made. |
|
V_OUT_TOTAL |
bigint |
The values of the Total Outbound Time statistic. |
|
V_OUT_HANDLING |
bigint |
The values of the Outbound Handling Time statistic. |
|
V_OUT_WRAP_UP |
bigint |
The values of the Outbound Wrap-up Time statistic. |
|
V_CALLS_RECEIVED |
bigint |
The values of the Calls Received statistic. |
|
V_CALLS_UNQUEUED |
bigint |
Number of calls that had exited queues. |
|
V_TOTAL_BUSY_TIME |
bigint |
The values of the Busy Time statistic. |
|
V_SERVICE_FACTOR |
bigint |
The values of the Service Factor statistic. |
|
V_ANSWERED_RETURNS |
bigint |
The values of the Answered Returns statistic. |
|
V_MIN_QUEUED_TIME |
bigint |
The values of the Queued Time statistic. |
|
V_MIN_IVR_TIME |
bigint |
Minimum time in the voice application. |
|
V_MAX_IVR_TIME |
bigint |
Maximum time in the voice application. |
| V_TOTAL_HANDLING_TIME | bigint | The values of the Total Handling Time statistic. |
| V_DIVERTED | bigint | Number of inbound calls diverted to destinations other than agents, without putting the calls in queue. |
| V_QUEUED_DIVERTED | bigint | Number of inbound calls diverted to destinations after putting the calls in queue. |
| V_DIVERTED_FAIL | bigint | Number of inbound calls diverted to destinations other than agents, but disconnected before being answered. |
| V_DELIVERY_ATTEMPTS | bigint | The values of the Delivery Attempts statistic. |
| V_IN_TOTAL | bigint | The values of the Total Time statistic. |
AGENT_AUDIT Table
Records the state changes for agents with the auditing functionality enabled. Ivanti Voice adds a record to the table every time an agent changes state.
Enable auditing for an agent at the agent level (refer to ), at the agent group level (refer to ), or system-wide for all agents (refer to ).
The Contact Center server does not write audit records or create the tables until at least 500 agent state changes occur or you reboot Ivanti Voice.
The following table lists the columns each record in the database includes.
|
Column |
Type |
Description |
|---|---|---|
|
ID |
Int |
Primary Key, unique identifier for each row. |
|
AGENT |
nvarchar (32) |
The unique identifier of the agent. |
|
TIME_STAMP |
date/ time
|
The date and time the record was created. |
|
OLD_STATE |
int |
The previous state of the agent. Refer to the table above for the state that corresponds to each integer. |
|
NEW_STATE |
int |
The new state of the agent. Refer to the table above for the state that corresponds to each integer. |
|
LAST_NAME |
nvarchar (64) |
The agent’s last name. |
|
FIRST_NAME |
nvarchar (64) |
The agent’s first name. |
| REASON | nvarchar (64) | The Not Ready reason the agent gave for entering the Not Ready state. This value is relevant only if the agent enters the Not Ready state. |
|
DURATION |
bigint |
Time spent by agent in OLD_STATE before transitioning to the NEW_STATE. |
The following table shows the integers Ivanti Voice uses to represent each state.
|
Column |
Type |
Description |
|---|---|---|
|
0 |
Unknown |
The Contact Center server does not know the state of the agent. The Contact Center server reports this state as the old state in the first audit record for the agent. |
|
1 |
Logged-out |
The agent is not logged into the contact center. |
|
2 |
Not ready |
The agent is not ready to receive calls. The reason of the state may be reported in the REASON column. |
|
3 |
Ready |
The agent is ready to receive calls. |
|
4 |
Reserved |
The agent has been reserved for the delivery of a call. |
|
5 |
Busy |
The agent is on the phone with a caller. |
|
6 |
After Call Work |
The agent is done talking to the caller and is doing after call work. |
NOT_READY_REASONS Table
Stores a dictionary of all Not Ready reasons. Ivanti Voice populates the table with each Not Ready reason an agent reports.
|
Column |
Type |
Description |
|---|---|---|
|
ID |
int |
The unique identifier of the Not Ready reason. Values for this column are allocated automatically by the server. This column is the primary key of the table. The name of the primary key index is PK_NOT_READY_REASONS. |
|
NAME |
nvarchar |
The unique display name of the Not Ready reason. |
NOT_READY_TIME Table
A Not Ready reason data for all agents. Ivanti Voice populates the table with each Not Ready reason the agent reports, along with the following data:
•The time the agent entered the Not Ready state.
•The time the agent left the Not Ready state.
•The number of milliseconds the agent spent in the Not Ready state.
|
Column |
Type |
Description |
|---|---|---|
|
REPORT_ID |
int |
The unique identifier of the report record in the REPORTS table. |
|
OBJECT_ID |
int |
The unique identifier of the object for which the Not Ready time is stored in the record. |
|
BEGIN_TIME |
date/ time |
The beginning time of the interval for which time in the Not Ready state is stored in the record. |
|
END_TIME |
date/ time |
The ending time of the interval for which time in the Not Ready state is stored in the record. |
|
REASON_ID |
int |
The identifier of the Not Ready reason. |
|
REASON_TIME |
int |
The total number of milliseconds the agent spent in the Not Ready state. |