Reporting and statistics¶
Introduction¶
Pack reporting is a part of the XivoCC. It aims at computing historical statistics, which are stored in the xivo_stats database. Sample reports based on them are accessible in SpagoBI.
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.
Calls longer than 4 hours are considered as unterminated calls and therefore communication time is set to 0 for these calls.
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
service xivo-db-replication stop
service xivo-full-stats stop
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
- Install docker, docker-compose and xivocc-installer
- Open docker-xivocc.yml and remove sections recording_rsync, config_mgt, recording_server, xuc, xucmgt
- Run xivocc-dcomp pull
- CHECK THE FREE DISK SPACE. The next command will migrate the database. This may take several hours.
sudo -u postgres pg_dump --format c xivo_stats | docker exec -i xivocc_pgxivocc_1 pg_restore -U postgres -d xivo_stats
- Start by xivocc-dcomp 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 outside, 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 | Answer 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, outside 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 because 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 | Wrap-up 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 | Technical 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 wrap-up 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 internal 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 internal 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 | Technical 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 | Wrap-up time, in seconds |
agentfeatures¶
Gather information about agent profile
| Column | Type | Description |
|---|---|---|
| id | INTEGER | |
| numgroup | INTEGER | Agent group number |
| number | VARCHAR | Agent number (≠ line number) |
| firstname | VARCHAR | Agent first name |
| lastname | VARCHAR | Agent last name |
agent_position¶
Line number used by agents
| Column | Type | Description |
|---|---|---|
| agent_num | VARCHAR | Agent number |
| line_num | VARCHAR | Line number of the device used |
| start_time | TIMESTAMP | Begin date of line use |
| end_time | TIMESTAMP | End date of line use |
| sda | VARCHAR | Line direct inward dial |
Sample SQL statistic queries¶
This section describes some SQL query achievements done based on Database schema.
List all received agent calls¶
This query get the phone set number on which the agent took the call. It lists all calls answered by agent with line number on which he was logged in. The query here is limiting to all calls answered the first day of August, but it can be easily customized to your needs.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT cq.answer_time,
cq.hangup_time,
COALESCE(af.firstname, '') || ' ' || COALESCE(af.lastname, '') AS agent_name,
cd.src_num AS caller,
ap.line_number AS line_number
FROM call_on_queue cq
LEFT JOIN call_data cd ON cq.callid = cd.uniqueid
INNER JOIN agentfeatures af ON cq.agent_num = af.number
INNER JOIN agent_position ap ON cq.agent_num = ap.agent_num AND cq.answer_time between ap.start_time and ap.end_time
AND to_char(cq.answer_time,'YYYY') = '2016'
AND to_char(cq.answer_time,'MM') = '08'
AND to_char(cq.answer_time,'DD') = '01'
AND cq.agent_num IS NOT NULL;
|
This query will result to something like:
| Answer time | Hangup time | Agent name | Caller number | Line number used |
|---|---|---|---|---|
| 2016-08-01 09:01:36.803 | 2016-08-01 09:02:38.916 | Agent A | xxxxxxxxxx | 101 |
| 2016-08-01 09:08:52.8 | 2016-08-01 09:09:31.97 | Agent B | xxxxxxxxxx | 102 |
| 2016-08-01 09:03:43.797 | 2016-08-01 09:07:18.452 | Agent A | xxxxxxxxxx | 101 |
| 2016-08-01 09:09:06.895 | 2016-08-01 09:09:56.549 | Agent C | xxxxxxxxxx | 103 |
Distribution of received call by month and DID¶
This query aggregates all received call by month and by direct inward dial number.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SELECT extract(year from cq.queue_time) as Year,
to_char(cq.queue_time,'Mon') as Month,
dst_num AS DID,
COUNT(CASE WHEN cq.status IN ('answered', 'abandoned', 'leaveempty', 'timeout', 'exit_with_key') OR cq.status IS NULL THEN 1 END) AS Presented,
COUNT(CASE WHEN cq.answer_time IS NOT NULL THEN 1 END) as Answered,
to_char(AVG(CASE WHEN cq.answer_time IS NOT NULL THEN cq.hangup_time - cq.answer_time END), 'HH24:MI:SS') as ACT,
COUNT(CASE WHEN cq.status = 'timeout' THEN 1 END) as Dissuaded,
COUNT(CASE WHEN cq.status = 'abandoned' THEN 1 END) as Hungup,
COUNT(CASE WHEN cq.status = 'closed' THEN 1 END) as Refused,
COUNT(CASE WHEN cq.status = 'abandoned' AND (cd.end_time - cq.queue_time) < '15 seconds'::interval THEN 1 END) as Abandoned_T1,
to_char(SUM(CASE WHEN cq.status = 'answered' THEN
EXTRACT(epoch FROM (cq.answer_time - cq.queue_time)) ELSE 0 END) /
NULLIF(COUNT(CASE WHEN cq.status IN ('answered', 'abandoned', 'leaveempty', 'timeout', 'exit_with_key')
OR cq.status IS NULL THEN 1 END),0) * INTERVAL '1 second', 'HH24:MI:SS') as AWT,
SUM(CASE WHEN cd.transfered THEN 1 ELSE 0 END) AS Transfered,
ROUND(COUNT(CASE WHEN cq.answer_time IS NOT NULL THEN 1 END)::numeric /
NULLIF(COUNT(CASE WHEN cq.status IN ('answered', 'abandoned', 'leaveempty', 'timeout', 'exit_with_key')
OR cq.status IS NULL THEN 1 END),0)::numeric * 100,2) as Accepted_ratio
FROM call_on_queue cq
LEFT JOIN call_data cd ON cq.callid = cd.uniqueid
GROUP BY 1,2,3;
|
This query will result to something like:
| Year | Month | DID | Presented | Answered | Average Call Time | Dissuaded | Hangup | Refused | Abandonned | Average Waiting Time | Transfered | Answered Rate |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2016 | Aug | 1101 | 2 | 2 | 00:04:49 | 0 | 0 | 0 | 0 | 00:00:03 | 0 | 100 |
| 2016 | Aug | 1105 | 1 | 1 | 00:03:53 | 0 | 0 | 0 | 0 | 00:00:06 | 0 | 100 |
| 2016 | Aug | 1106 | 331 | 306 | 00:03:11 | 10 | 15 | 38 | 3 | 00:00:17 | 5 | 92.45 |
| 2016 | Aug | 1107 | 8 | 8 | 00:01:55 | 0 | 0 | 12 | 0 | 00:00:18 | 0 | 100 |
| 2016 | Aug | 1114 | 1 | 1 | 00:04:20 | 0 | 0 | 0 | 0 | 00:00:06 | 0 | 100 |
| 2016 | Aug | 1115 | 2 | 2 | 00:01:30 | 0 | 0 | 0 | 0 | 00:00:09 | 0 | 100 |
| 2016 | Aug | 1118 | 53 | 49 | 00:01:20 | 1 | 3 | 2 | 3 | 00:00:17 | 1 | 92.45 |
| 2016 | Aug | 1119 | 3 | 0 | 2 | 1 | 0 | 0 | 00:00:00 | 0 | 0 | |
| 2016 | Aug | 1120 | 1 | 1 | 00:00:51 | 0 | 0 | 0 | 0 | 00:00:42 | 0 | 100 |
Tables join¶
Tables call_data, call_on_queue and 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 |
On the other hand, tables attached_data and call_element contains foreign key referencing the id column of call_data.
—
Tables call_on_queue, agentfeatures and agent_position can be linked together by doing a join on a column holding the agent number reference. The columns are the following:
| Table | Reference column |
|---|---|
| call_on_queue | agent_num |
| agentfeatures | number |
| agent_position | agent_num |
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:
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
