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.

Important

New tables in xivo_stats schema are available (prefixed with xc_). These tables are (so far) for internal use and can be changed without notice. Documentation will be available once this new statistic model will replace the existing one and fix the existing limitations of previous model described above.

Reporting Architecture

The reporting is composed of four packages: pack-reporting, xivo-full-stats, xivo-reporting-db and xivo-db replication.

These packages will feed the tables of the xivo_stats database:

• xivo-db-replication feeds the tables cel and queue_log in real time, and the configuration tables (dialaction, linefeatures, etc…) every 5 minutes

• xivo-full-stats feeds in real time the tables call_on_queue, call_data, stat_queue_periodic, stat_agent_periodic and agent_position

• xivo-reporting-db and pack-reporting work together to feed the tables stat_queue_specific, stat_agent_queue_specific and stat_agent_specific every 15 minutes

Queue statistics

It summarizes how we sort and name every call to a queue, reaching different states.

These states are based on asterisk queue logs event types, you should find more infos about them here : https://docs.asterisk.org/Operation/Logging/Queue-Logs/?h=queue+log

The CCM icon means the value is readable on the CC Manager (see Queue view).

Agents Statistics

A call may be submitted to an agent several times. Thus a call ‘not answered’ by an agent may still be answered by another. This may be more explicit through an example below.

An offered call can be submitted several times.

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.

• 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 limitations in call_data table:

  • 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.

  • Given one queue Q1, Two agents A1 and A2 and an external caller C.

    • C calls Q1 and A1 answers

    • A1 transfers to A2 through internal phone number

    • A1 completes the transfer

  Then the call between A2 and C is not computed at all in statistics.

Using SpagoBi

Important

All report samples (see Upload Statistics Reports) bundled with XiVOCC are aimed to work with french date format. By default dates are picked in french format (day/month/year). Either you need to type them explicitly in your locale format either you can set Locale to french thanks to Flag menu in Spago. See #1662

Statistics computed in real time are available in the ccmanager (see Queue view).

Warning

Because the SpagoBI technology is growing old and does not receive open-source updates, we won’t build our SpagoBI image after the one from Maia: 2024.05.latest. That image will still work just as before for further releases, starting with Naos.

Configure SpagoBI on http://XIVOCC_IP/SpagoBI (by default login: biadmin, password: biadmin). Replace XIVOCC_IP by the CC VM’s IP or the FQDN.

Update default language

1. Go to “⚙ Resources” > “Configuration management”

2. In the “Select Category” field, chose “LANGUAGE_SUPPORTED”

3. change value of the label “SPAGOBI.LANGUAGE_SUPPORTED.LANGUAGE.default” in your language : fr,FR , en,US , …

Upload Statistics Reports

Are you on a new installation ? Download the sample_reports from https://gitlab.com/xivocc/sample_reports/-/raw/master/spagobi/qsr_and_statusdb_from_luna.zip Did you upgrade to luna ? Get the new support reports from https://gitlab.com/xivocc/sample_reports/-/raw/master/spagobi/queue_support_report.zip https://gitlab.com/xivocc/sample_reports/-/raw/master/spagobi/agent_support.zip

Import zip file in SpagoBI:

1. Goto “Repository Management” -> “Import/Export”

2. Click on “Browse/Choose your file” and choose the previously downloaded file

3. Click on “Import” icon

4. Click next with default options until you are asked to override metadata, set Yes as shown in screen below

You can now browse the reports in Document->Rapports->Exemples>FA and Document->Rapports->Exemples>Agent..

Use the database status report to check if replication and reporting generation is working :

Scheduling reports

Due to a SpagoBi limitation, when scheduling reports using string parameters, you need to enter manualy the parameter using comma and quotes to separate values. Example for queues support, technical and sales, the parameters of the schedulers must be filled as follow:

‘support’,’technical’,’sales’

You may use Jaspersoft® Studio to design you own reports.

Warning

Due to a limitation in SpagoBi do not use single quotes in your reports name, otherwise you will not be able to schedule your report (see #213).

Database schema

Glossary

uniqueId :

• unqiueId are Ids generated by asterisk for each call leg.

• When A calls B we get a call leg for A and one for B, each one is a separate uniqueId.

• in cel asterisk table you can find all event linked to a call leg by looking for it in the column uniqueId.

linkedId :

• the column linkedId in cel Asterisk table contains a uniqueId which links the two legs of the call.

• The leg which initiates the call (A leg) will have the same value for both uniqueId and linkedId columns.

• The other leg (B leg) will have the uniqueId of B in uniqueId column and will have the uniqueId of A in linkedId one.

Overview

call_data

Each line in call_data, correspond to a unique call and contains only one uniqueId of one call leg. This table is aggregated in near real time with the data of cel Asterisk table.

Important

End of a call is considered when phone is hung up, we don’t consider the time of transfers that can be done besides initial call.

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 (phone is hung up)
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 : SIP/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

The reporting allows to attach as much 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

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

transfers

Table that follow transfers between calls

Column	Type	Description
id	INTEGER	id of the transfer
callidfrom	VARCHAR	initial call
callidto	VARCHAR	destination call

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)

Important

Hold times are considered as conversation time and so are included.

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 (ACD), in seconds
ringing_time	INTEGER	Ringing time on incoming cals from a queue (ACD), 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 any kind of calls (ACD, internal and outgoing) 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

queuefeatures

Gather information about queue

Column	Type	Description
id	INTEGER	Queue id
name	VARCHAR	Technical name
displayname	VARCHAR	Display name
number	VARCHAR	Number to reach the queue

userfeatures

Gather information about user profile

Column	Type	Description
id	INTEGER	User id
firstname	VARCHAR	User first name
lastname	VARCHAR	User last name

linefeatures

Gather information about line used by a user

Column	Type	Description
id	INTEGER	Line id
name	VARCHAR	SIP interface name
number	VARCHAR	Phone number
context	VARCHAR	Line context
provisioningid	INTEGER	Number to provision a device

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

agent_states

Last state known for an agent

Column	Type	Description
agent	VARCHAR	Agent number
time	TIMESTAMP	Last state time
state	agent_state_type	logged_on, logged_off, paused, wrapup

agentgroup

Agent group ownership

Column	Type	Description
id	INTEGER	Agent group Id
groupid	INTEGER	Deprecated use id
name	VARCHAR	Name of the group
groups	VARCHAR	Deprecated
commented	INTEGER	know if group is disabled
deleted	INTEGER	know if group is deleted
description	TEXT	description set in XiVO

dialaction

Action to trigger when a number (extension) is matching some criteria (congestion, busy, no answer…)

Column	Type	Description
event	VARCHAR	Type of criteria (answer, noanswer, busy, congestion, chanunavail, qwaittime, qwaitratio)
category	VARCHAR	user, meetme, queue, incall, group, callfilter…
categoryval	VARCHAR	Id applying to the category (user id if category is user)
action	VARCHAR	What to trigger (can be custom or any existing category defined above)
actionarg1	VARCHAR	Value associated to action. e.g. if queue must be called, arg1 will contain the queue id
actionarg2	VARCHAR	Rarely used to define a second value if category chosen need it
linked	INTEGER	Deprecated

extensions

List of all numbers that you can call within XiVO

Column	Type	Description
id	INTEGER	Extension Id
commented	INTEGER	Define if extension is disabled
context	VARCHAR	Context of your extension (xivo-features are hardcoded ones)
exten	VARCHAR	Number to reach the extension
type	VARCHAR	user, meetme, queue, incall, group, extenfeatures…

user_line

Mapping table between a user, its line and associated extension

Column	Type	Description
id	INTEGER	User line association id
user_id	INTEGER	User id
line_id	INTEGER	Line id
extension_id	INTEGER	Extension id

labels

Defines the list of labels that can be set to users

Column	Type	Description
id	INTEGER	Label id
display_name	VARCHAR	Name displayed for the label
Description	TEXT	description set in XiVO

userlabels

Defines the association between labels and users

Column	Type	Description
id	INTEGER	id of the association
user_id	INTEGER	User Id
label_id	INTEGER	Label Id

cel & queue_log

These tables are generated by Asterisk and cloned in stats repository, for more information about it see https://wiki.asterisk.org/wiki/pages/viewpage.action?pageId=5242932

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

—

Tables agentgroups and agentfeatures can be linked together by doing a join on a column holding the agent group id reference. The columns are the following:

Table	Reference column
agentfeatures	numgroup
agentgroups	id

—

Tables transfers and call_data can be linked together by doing a join on a column holding the call uniqueid reference. The columns are the following:

Table	Reference column
transfers	callidfrom / callidto
call_data	uniqueid

Sample of statistic data from various call flow

This section gives an overview of what kind of data can be found in the different statistics tables when performing usual contact center call flow.

For the folowing diagrams, here the legend that would apply to understand interactions:

Single call put on hold

Let’s take the assumption that one Agent ( A1 with id 8000 and phone number 1000 ) calls an internal User ( U1 with phone number 1001 ).

This simple call flow generates in call_data 1 line (simplified table to keep only important fields):

id	uniqueId	dst_num	answer_time	end_time	src_agent
1	12345678.9	1001	2018-12-01 15:02:00	2018-12-01 15:22:00	8000

also get in hold_period 1 line:

id	linkedId	start	end
1	12345678.9	2018-12-01 15:13:00	2018-12-01 15:17:00

Statistics (done by pack-reporting for spago reports) generates then stat_agent_specific:

time	agent_num	nb_emitted_internal_calls	conversation_time_emitted_internal_calls	hold_time
2018-11-29 15:15:00	8000	1	720	120
2018-11-29 15:30:00	8000	1	480	120

Single call, direct transfer then new call

Let’s take the assumption that one Agent (A1 with id 8000 and phone number 1000) calls an internal User ( U1 with phone number 1001 ). This call is transfered to Agent A2 (with id 8002 ) blindly. Then A1 calls internal another User ( U2 with phone number 1002 )

This call flow generates in call_data 2 lines (simplified table to keep only important fields) one for each call ( U1 and U2 ):

id	uniqueId	dst_num	answer_time	end_time	src_agent	dst_agent	transfered	transfer_direction
1	12345678.9	1001	2018-12-01 15:02:00	2018-12-01 15:12:00	8000	8002	true	internal
2	12345679.0	1002	2018-12-01 15:13:00	2018-12-01 15:23:00	8000

and get in transfers 1 line (where callidto id the uniqueId of the leg of Agent A2 with user U1):

id	callidfrom	callidto
1	12345678.9	98765432.1

Statistics (done by pack-reporting for spago reports) generates then stat_agent_specific:

time	agent_num	nb_emitted_internal_calls	conversation_time_emitted_internal_calls	hold_time
2018-11-29 15:15:00	8000	1	720	0
2018-11-29 15:30:00	8000	1	480	0

Important

We don’t consider the time of the transfer besides initial call and only time of established calls.

Single call, attented transfer then new call

Let’s take the assumption that one Agent (A1 with id 8000 and phone number 1000) calls an internal User ( U1 with phone number 1001 ). This call is transfered to Agent A2 (with id 8002 ) after talking with him. Finally A1 calls internal another User ( U2 with phone number 1002 )

This call flow generates in call_data 3 lines (simplified table to keep only important fields) one for each call between U1 and U2 and one for A2:

id	uniqueId	dst_num	answer_time	end_time	src_agent	dst_agent	transfered	transfer_direction
1	12345678.9	1001	2018-12-01 15:02:00	2018-12-01 15:12:00	8000	8002	true	internal
2	12345679.0	1002	2018-12-01 15:13:00	2018-12-01 15:23:00	8000

and get in transfers 1 line (where callidto id the uniqueId of the leg of Agent A2 with user U1):

id	callidfrom	callidto
1	12345678.9	98765432.1

also get in hold_period 1 line:

id	linkedId	start	end
1	12345678.9	2018-12-01 15:07:00	2018-12-01 15:12:00

Statistics (done by pack-reporting for spago reports) generates then stat_agent_specific:

time	agent_num	nb_emitted_internal_calls	conversation_time_emitted_internal_calls	hold_time
2018-11-29 15:15:00	8000	1	1020	300
2018-11-29 15:30:00	8000	1	480	0

Important

Conversation time can be over 900s (15mn) as hold time is included in this specific call flow.

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.

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 for a queue

This query aggregates all received call by month and by queue number.

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

Number of direct calls to user through its DID only

This query aggregates all received call through direct inward dial number. We are not counting here the internal calls of a user.

SELECT
              CASE
                      WHEN call_direction='outgoing'
                              THEN (select ext_a.typeval::integer from extensions ext_a where ext_a.type = 'user' and ext_a.exten=src_num)
                      WHEN call_direction='incoming'
                              THEN (select dl_a.actionarg1::integer from dialaction dl_a,extensions ext_b where  dl_a.category = 'incall'
                                      AND dl_a.action = 'user' and dl_a.categoryval = ext_b.typeval AND ext_b.type = 'incall' AND dst_num IN (ext_b.exten, '+' || ext_b.exten) )
                      ELSE 0
              END as user_id,
        sum(CASE WHEN call_direction= 'incoming' THEN 1 ELSE 0 END) AS nb_incoming_offered,
        sum(CASE WHEN call_direction= 'incoming' and status = 'answer' THEN 1 ELSE 0 END) AS nb_incoming_answered,
        sum(CASE WHEN call_direction= 'incoming' THEN 1 ELSE 0 END)-sum(CASE WHEN call_direction= 'incoming' and status = 'answer' THEN 1 ELSE 0 END) as nb_incoming_missed
FROM
        call_data
GROUP BY user_id

This query will result to something like:

user_id	nb_incoming_offered	nb_incoming_answered	nb_incoming_missed
1	24	18	6