Reporting and statistics

Introduction

Pack reporting is a part of the XivoCC, but can also be installed separately. It aims at computing historical statistics, which are stored in the xivo_stats database. Sample reports based on them are accessible in SpagoBI.

Standalone installation

Warning

Full installation of the pack reporting requires restarting XiVO services, so telephone communcations will be cut.

  1. Install docker by following installation instructions: http://docs.docker.com/installation/
  2. Execute the following commands:
wget https://gitlab.com/xivoxc/packaging/raw/master/install/install-docker-reporting.sh
bash install-docker-reporting.sh
docker-compose -f /etc/docker/compose/docker-reporting.yml up -d

During installation you will be asked for: * the XiVO IP address * the number of weeks to keep in history

At the end of the installation some configuration must be done on the XiVO:

  1. edit /var/lib/postgresql/9.1/main/postgresql.conf and set listen_addresses to *

  2. edit /var/lib/postgresql/9.1/main/pg_hba.conf and add the following line: host asterisk stats PACK_REPORTING_IP/32 md5

  3. add the following events to /etc/asterisk/cel.conf: HOLD,UNHOLD,BLINDTRANSFER,ATTENDEDTRANSFER

  4. execute the following command:

    sudo -u postgres psql asterisk << EOF
    CREATE USER stats WITH PASSWORD 'stats';
    GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO stats;
    EOF
    
  5. Finish the installation by a full restart of XiVO:

    xivo-service restart all
    

Checking the installation

Historical statistics

  • Docker containers compose_xivo_replic_1, compose_xivo_stats_1 and compose_pack_reporting_1 should be started
  • There should be no errors in /var/log/xivocc/xivo-db-replication/xivo-db-replication.log and /var/log/xivocc/xivo-full-stats/xivo-full-stats.log

Kibana / TOTEM

Known limitations

  • Queue members should only be agents. If users are members of a queue, their statistics will be incomplete.

  • Configuration modifications on the XiVO (such as an agent deletion) are replicated on the statistics server, and their previous value is not kept. However, statistics history is preserved.

  • POPC statistics are wrong.

  • If two agents are associated to the same call, they will have the same hold time for this call.

  • Transfer statistics limitation : given two queues Q1 and Q2, two agents A1 and A2, and an external caller C.

    • C calls Q1 and A1 answers
    • A1 transfers to Q2 and A2 answers
    • A2 transfers to the outside

    Then the second transfer is seen as a transfer to the outside.

Attached Data

The pack reporting allows to attach as mush data as wished to a given call, in order to find them in the reporting database for future use. This data must be in the form of a set of key-value pairs.

To attach data to a call, you must use the dialplan’s CELGenUserEvent application:

exten = s,n,CELGenUserEvent(ATTACHED_DATA,my_key=my_value)

This will insert the following tuple in the attached_data table:

key value
my_key my_value

Upgrade notes

These notes include upgrade procedures for old versions of the Pack reporting, before XivoCC starts and before it was packaged with Docker. In those cases, run the following command to find the installed version of the pack reporting:

dpkg -l|grep pack-reporting

From version < 1.6

  • data retention time will be lost during upgrade : save it and write it back in /etc/xivo-reporting-db.conf
  • the upgrade is likely to be long if there is a lot of data in queue_log. Purge old data out of this table if possible in order to accelerate the upgrade
  • at the end of the upgrade, run apt-get autoremove (deletion of xivo-stat, xivo-libdao and xivo-lib-python)

From version < 1.8

  • XiVO in version < 14.08 is not supported anymore
  • if it is required, the upgrade of the XiVO must be done before the upgrade of the pack reporting, and no call must be performed between the two upgrades

From a version using Debian packaging to a version using Docker

  • Beware: this will require a migration of the original PostgreSQL database to the Dockerised one. For this you need to have free disk space : the amount of free disk space must equal the size of /var/lib/postgresql. This check must be performed after docker images have been pulled.
  • Run the following commands:
apt-get update
apt-get install pack-reporting xivo-full-stats xivo-reporting-db xivo-db-replication db-utils
service xivo-db-replication stop
service xivo-full-stats stop
wget https://gitlab.com/xivoxc/packaging/raw/master/install/install-docker-reporting.sh
bash install-docker-reporting.sh
docker-compose -f /etc/docker/compose/docker-reporting.yml up -d pgxivocc
# Database migration. CHECK THE FREE DISK SPACE
sudo -u postgres pg_dump --format c xivo_stats | docker exec -i xivocc_pgxivocc_1 pg_restore -U postgres -d xivo_stats
docker-compose -f /etc/docker/compose/docker-reporting.yml up -d

From a dockerized version before callbacks

  • Run the following commands:
docker exec -ti compose_pgxivocc_1 psql -U postgres -c 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"' xivo_stats
docker exec -ti compose_pgxivocc_1 psql -U postgres -c 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"' xuc_rights

Database schema

call_data

Calls list

Column Type Description
id INTEGER  
uniqueid VARCHAR Call unique reference, generated by Asterisk
dst_num VARCHAR Called number
start_time TIMESTAMP Call start time
answer_time TIMESTAMP Call answer time
end_time TIMESTAMP Call end time
status status_type Call status. Beware: only answered is properly filled.
ring_duration_on_answer INTEGER Ring time of the endpoint answering the call, in seconds
transfered BOOLEAN True if the call has been transfered
call_direction call_direction_type Call direction (‘’incoming’’ : call from the outisde, received by XiVO ; ‘’outgoing’’ : call to the outside, originated by an endpoint associated to XiVO ; ‘’internal’’ : call taking place entirely inside the XiVO)
src_num VARCHAR Calling number
transfer_direction call_direction_type Indicates the transfer direction, if relevant
src_agent VARCHAR Agent originating the call
dst_agent VARCHAR Agent receiving the call, if it is a direct call on an agent. Not filled when the call is destined to a queue
src_interface VARCHAR Interface originating the call (in the Asterisk sense, ex : SCCP/01234)

attached_data

Data attached to the call (cf. Attached Data)

Column Type Description
id INTEGER  
id_call_data INTEGER Id of the associated tuple in call_data
key VARCHAR Name of the attached data
value VARCHAR Value of the attached data

call_element

Part of a call matching the reaching of an endpoint

Column Type Description
id INTEGER  
call_data_id INTEGER Id of the associated tuple in call_data
start_time TIMESTAMP Time at which the endpoint was called
answer_time TIMESTAMP Asnwer time for the endpoint
end_time TIMESTAMP End time of this call part
interface VARCHAR Endpoint interface

call_on_queue

Calls on a queue

Column Type Description
id INTEGER  
callid VARCHAR Call unique reference, generated by Asterisk
queue_time TIMESTAMP Time of entrance in the queue
total_ring_seconds INTEGER Total ring time, in seconds (includes ringing of non-answered calls)
answer_time TIMESTAMP Answer time
hangup_time TIMESTAMP Hangup time
status call_exit_type Call status (full: full queue; closed: closed queue; joinempty: call arrived on empty queue; leaveempty : exit when queue becomes empty; divert_ca_ratio : call redirected because the ratio waiting calls/agents was exceeded ; divert_waittime: call redirected because estimated waiting time was exceeded; answered: call answered ; abandoned: call abandoned; timeout : maximum waiting time exceeded)
queue_ref VARCHAR Technical queue name
agent_num VARCHAR Number of the agent taking the call, if relevant

hold_periods

Hold periods

Column Type Description
id INTEGER  
linkedid VARCHAR Call unique reference, generated by Asterisk
start TIMESTAMP Hold start time
end TIMESTAMP Hold end time

stat_queue_periodic

Statistics aggregated by queue and time interval (15 minutes)

Column Type Description
id INTEGER  
time TIMESTAMP Start time of the considered interval
queue VARCHAR Queue technical name
answered INTEGER Number of answered calls
abandoned INTEGER Number of abandoned calls
total INTEGER Total number of calls received on the queue (which excludes the calls dissuaded before entering the queue)
full INTEGER Number of calls arrived on a full queue (diversion before entering the queue)
closed INTEGER Number of calls arrived on a closed queue, outsided of the configured schedules (diversion before entering the queue)
joinempty INTEGER Number of calls arrived on an empty queue (diversion before entering the queue)
leaveempty INTEGER Number of calls redirected becouse of a queue becoming empty
divert_ca_ratio INTEGER Number of calls arrived when the calls / available agents ratio is exceeded (diversion before entering the queue)
divert_waittime INTEGER Number of calls arrived when the estimated waiting time is exceeded (diversion before entering the queue)
timeout INTEGER Nombre of calls redirecting because maximum waiting time is exceeded

stat_agent_periodic

Statistics aggregated by agent and time interval (15 minutes)

Column Type Description
id INTEGER  
time TIMESTAMP Start time of the considered interval
agent VARCHAR Agent number
login_time INTERVAL Login time
pause_time INTERVAL Pause time
wrapup_time INTERVAL Wrapup time

stat_queue_specific

Statistics aggregated by queue, called number and time interval (15 minutes)

Column Type Description
time TIMESTAMP Start time of the considered interval
queue_ref VARCHAR Technicxal name of the queue
dst_num VARCHAR Called number
nb_offered INTEGER Number of presented calls
nb_abandoned INTEGER Number of abandoned calls
sum_resp_delay INTEGER Wait time, in seconds
answer_less_t1 INTEGER Number of calls answered in less than t1 seconds
abandoned_btw_t1_t2 INTEGER Number of calls abandoned between t1 and t2 seconds
answer_btw_t1_t2 INTEGER Number of calls answered between t1 and t2 seconds
abandoned_more_t2 INTEGER Number of calls answered in more than t2 seconds
communication_time INTEGER Total communication time in seconds
hold_time INTEGER Total hold time in seconds
wrapup_time INTEGER Total wrapup time in seconds

The thresholds t1 and t2 are configurable:

  • in the table queue_specific_time_period for the default values in seconds. Installation values are t1=15 seconds and t2=20 seconds. Data is saved in the form of (name, seconds) pairs, for example : (‘t1’, 15).
  • in the table queue_threshold_time for values specific to a queue. Data is saved in the form of a tuple (queue name, t1, t2).

stat_agent_specific

Statistics aggregated by agent and time interval (15 minutes)

Column Type Description
time TIMESTAMP Start time of the considered interval
agent_num VARCHAR Agent number
nb_offered INTEGER Number of calls presented from a queue
nb_answered INTEGER Number of calls answered from a queue
conversation_time INTEGER Conversation time on incoming calls from a queue, in seconds
ringing_time INTEGER Ringing time on incoming cals from a queue, in seconds
nb_outgoing_calls INTEGER Number of calls emitted to the outside
conversation_time_outgoing_calls INTEGER Conversation time in calls emitted to the outside, in seconds
hold_time INTEGER Hold time for calls from a queue, in seconds
nb_received_internal_calls INTEGER Number of received internal calls
conversation_time_received_internal_calls INTEGER Conversation time on received interbal calls, in seconds
nb_transfered_intern INTEGER Number of calls coming from a queue and transfered to an internal destination
nb_transfered_extern INTEGER Number of calls coming from a queue and transfered to an external destination
nb_emitted_internal_calls INTEGER Number of emitted interbal calls
conversation_time_emitted_internal_calls INTEGER Conversation time on emitted internal calls, in seconds
nb_incoming_calls INTEGER Number of received incoming calls
conversation_time_incoming_calls INTEGER Conversation time on received incoming calls, in seconds

stat_agent_queue_specific

Statistics aggregated by queue, called number, agent and time interval (15 minutes)

Column Type Description
time TIMESTAMP Start time of the considered interval
agent_num VARCHAR Agent number
queue_ref VARCHAR Technicxal name of the queue
dst_num VARCHAR Called number
nb_answered_calls INTEGER Number of answered calls
communication_time INTEGER Communication time, in seconds
hold_time INTEGER Hold time, in seconds
wrapup_time INTEGER Wrapup time, in seconds

Tables call_data, call_on_queue et hold_periods can be linked together by doing a join on a column holding the call reference. The columns are the following:

Table Reference column
call_data uniqueid
call_on_queue callid
hold_periods linkedid

D’autre part, les tables attached_data et call_element contiennent une clef étrangère référençant la colonne id de call_data.

Using Kibana

Kibana is a web tool used to compute statistics based on Elasticsearch content. The reports packaged with the Pack reporting give you an outline of your recent call center activity. Here is a Kibana sample panel:

Kibana sample panel

Graphs are based on the queue_log table, enriched with agent names and agent groups, and inserted into an Elasticsearch index. It contains avents about calls placed on queues, and events about agent presences.

For each entry in the queue_log index, the following attributes are available:

  • queudisplayname : Queue display name
  • data1: basic queue_log data, with a different meaning according to the event
  • callid : Call unique identifier, generated by Asterisk
  • event : Call or agent status event - please see below
  • agentnumber: Agent number
  • queuename : Technical queue name
  • groupname : Agent group name
  • queuetime: Time of the event
  • agentname : Name of the agent, if available

The event can be one of the following (for a detailed explanation, please refer to https://wiki.asterisk.org/wiki/display/AST/Queue+Logs):

  • Call events:
    • FULL
    • CONNECT
    • EXITEMPTY
    • CLOSED
    • EXITWITHTIMEOUT
    • JOINEMPTY
    • ABANDON
    • ENTERQUEUE
    • TRANSFER
    • COMPLETEAGENT
    • COMPLETECALLER
    • RINGNOANSWER
  • Agent or queue event:
    • ADDMEMBER
    • PAUSEALL
    • PAUSE
    • WRAPUPSTART
    • UNPAUSE
    • UNPAUSEALL
    • PENALTY
    • CONFIGRELOAD
    • AGENTCALLBACKLOGIN
    • AGENTCALLBACKLOGOFF
    • REMOVEMEMBER
    • PRESENCE
    • QUEUESTART