Project

General

Profile

Bug #4790

CC - Stats - Performance agents - Number of answered calls may be wrong

Added by Anonymous almost 5 years ago. Updated almost 5 years ago.

Status:
Resolved
Priority:
Normal
Assignee:
-
Category:
Statistics
Target version:
Security issue:
No
In versions:
13.12
Read documentation?:

Description

Preamble

  • I am opening this bug, being fully aware of #3938 ; and though it may ressemble #3938 this is NOT #3938
  • All examples refer to the database dropped in quebec-devepub/clients/4790-stats-agent-answeredcalls

The problem as it appears

In Services -> Statistics -> Call center -> Queues I select the 'rc_support_technique' configuration for the 9th of November
  • 235 answered calls
In Services -> Statistics -> Call center -> Performance agents I select the 'rc_support_technique' configuration for the 9th of November
  • if I add all answered calls of all agents, I have 241

Obviously, 241 != 235

Try to find from where comes the problem

I then tried to find what the raw data tells. I made this request to count all answered calls by agent (I use the CONNECT event) :
SELECT agentfeatures.firstname,agentfeatures.lastname,queue_log.agent,COUNT(*) 
FROM queue_log 
LEFT JOIN agentfeatures ON substring(queue_log.agent from '....$') = agentfeatures.number 
WHERE queue_log.time LIKE '2013-11-09%' 
AND queue_log.event = 'CONNECT' 
AND queue_log.agent LIKE 'Agent%' 
GROUP BY queue_log.agent,agentfeatures.firstname,agentfeatures.lastname 
ORDER BY agentfeatures.lastname;

Then I compared the output of this request and the output of Performance agents in the webi and found that Agent 1240 has :
  • 39 answered calls in the db (with my request, based on CONNECT event),
  • and 45 answered calls in the webi

You will have noticed that 241 - 235 = 6 = 45 - 39

Since I'm aware of #3938, I launched the following request with a condition on the queue (see line 7) :

SELECT agentfeatures.firstname,agentfeatures.lastname,queue_log.agent,COUNT(*) 
FROM queue_log 
LEFT JOIN agentfeatures ON substring(queue_log.agent from '....$') = agentfeatures.number 
WHERE queue_log.time LIKE '2013-11-09%' 
AND queue_log.event = 'CONNECT' 
AND queue_log.agent LIKE 'Agent%'
AND queue_log.queuename = 'rc_support_technique'
GROUP BY queue_log.agent,agentfeatures.firstname,agentfeatures.lastname 
ORDER BY agentfeatures.lastname;

I get the same values for all agents.

The questions

How is it that :
  1. queue rc_support_technique answered calls != SUM (agent answered calls in rc_support_technique queue)
  2. SUM (agent answered call in rc_support_technique queue) according to xivo-stat != SUM (CONNECT event for calls in rc_support_technique queue)
    1. in short why don't we use the CONNECT event to count the number of answered call for an agent (the algo in the SQL function fill_leaveempty_calls seems quite complicated) ?

Note that the error is not present every day.
In the supplied db it is OK for the 5th of November but NOK for the 8th of November.

P.S.

Please, try to keep away from this issue any person who would be prone to mess around the comments with nonsens regarding J.S.Bach's use of the pentatonic and diatonic scale in his early works.
Thanks.

History

#1 Updated by Anonymous almost 5 years ago

  • Subject changed from CC - Stats - Performance agents - Number of answered may be wrong to CC - Stats - Performance agents - Number of answered calls may be wrong
  • Description updated (diff)

#2 Updated by Anonymous almost 5 years ago

  • Description updated (diff)

#3 Updated by Pascal Cadotte-Michaud almost 5 years ago

  • Status changed from New to In progress
  • Target version set to 13.24
  • Assignee set to Pascal Cadotte-Michaud

#4 Updated by Pascal Cadotte-Michaud almost 5 years ago

I don't fully understand the problem yet but that statistics are right if I regenerate the stats.

I suspect that this problem happens when a call that is distributed to more than one queue in two overlapping hours.

asterisk=# select * from queue_log where callid = '1383900788.167039';
            time            |      callid       |      queuename       |   agent    |     event      | data1 |       data2       | data3 | data4 | data5 
----------------------------+-------------------+----------------------+------------+----------------+-------+-------------------+-------+-------+-------
 2013-11-08 09:53:09.948070 | 1383900788.167039 | rc_support_technique | NONE       | ENTERQUEUE     |       | ##########        | 1     |       | 
 2013-11-08 09:53:12.684547 | 1383900788.167039 | rc_support_technique | Agent/1240 | CONNECT        | 3     | 1383900789.167040 | 2     |       | 
 2013-11-08 10:02:30.219402 | 1383900788.167039 | rc_support_technique | Agent/1240 | COMPLETECALLER | 3     | 558               | 1     |       | 
 2013-11-08 10:02:30.569451 | 1383900788.167039 | rc_info_commande     | NONE       | ENTERQUEUE     |       | ##########        | 1     |       | 
 2013-11-08 10:02:30.686291 | 1383900788.167039 | rc_info_commande     | Agent/1255 | RINGNOANSWER   | 0     |                   |       |       | 
 2013-11-08 10:02:32.770339 | 1383900788.167039 | rc_info_commande     | Agent/1246 | CONNECT        | 2     | 1383901350.167967 | 2     |       | 
 2013-11-08 10:04:19.485883 | 1383900788.167039 | rc_info_commande     | Agent/1246 | COMPLETECALLER | 2     | 107               | 1     |       | 
(7 rows)

#5 Updated by Pascal Cadotte-Michaud almost 5 years ago

asterisk=# select * from stat_call_on_queue where callid = '1383900788.167039';
   id    |      callid       |            time            | ringtime | talktime | waittime | queue_id | agent_id |  status  
---------+-------------------+----------------------------+----------+----------+----------+----------+----------+----------
 4545039 | 1383900788.167039 | 2013-11-08 09:53:09.94807  |        0 |      558 |        3 |       91 |      273 | answered
 4545968 | 1383900788.167039 | 2013-11-08 09:53:09.94807  |        0 |      558 |        3 |       91 |      273 | answered
 4547780 | 1383900788.167039 | 2013-11-08 10:02:30.569451 |        0 |      107 |        2 |       90 |      357 | answered
 4547781 | 1383900788.167039 | 2013-11-08 09:53:09.94807  |        0 |      558 |        3 |       91 |      273 | answered
(4 rows)

#6 Updated by Pascal Cadotte-Michaud almost 5 years ago

  • Target version deleted (13.24)
  • Status changed from In progress to New

I think the fix to this problem is to remove all entries in stat_call_on_queue with a callid this is seen after the start time.

A "work around" to this bug is to do a xivo-stat clean_db, xivo-stat fill_db but this can be VERY long if there are many entries in queue_log.

#7 Updated by Pascal Cadotte-Michaud almost 5 years ago

  • Target version set to 13.25

#8 Updated by Anonymous almost 5 years ago

  • Status changed from New to In progress
  • Assignee changed from Pascal Cadotte-Michaud to Anonymous

#9 Updated by Anonymous almost 5 years ago

  • Status changed from In progress to Code review

#10 Updated by Anonymous almost 5 years ago

  • Status changed from Code review to Resolved

Also available in: Atom PDF