AST_VDADstats.php long execution time / Database lockup

[DomeDan]

After a svn upgrade of our system strange things stared to happen.

A small hiccup when the system didn't respond and agents where complaining, the asterisk log showed this:
[Feb 13 11:09:17] WARNING[3332] chan_sip.c: Autodestruct on dialog '7ea53ead72a71b883ca7675c45100b09@<<Server IP>>:5060' with owner SIP/carrier_1-0000092f in place (Method: BYE). Rescheduling destruction for 10000 ms
aha, I've seen that before I though http://www.vicidial.org/VICIDIALforum/v ... =4&t=30297 but it was not exactly the same:
WARNING[2626]: chan_sip.c:4214 __sip_autodestruct: Autodestruct on dialog '0d42b9247c928cc90702775372e13c01@<<Server IP>>:5060' with owner SIP/ in place (Method: BYE). Rescheduling destruction for 10000 ms

the second time it happen it was a longer delay so I started investigate it more and found this in my /var/run/mysql/mysqld-slow.log (slow query logging):

Code: Select all

# Time: 140213 11:09:17
# User@Host: cron[cron] @  [123.123.123.123]
# Query_time: 11.792559  Lock_time: 0.000187 Rows_sent: 1  Rows_examined: 431
use asterisk;
SET timestamp=1392286157;
SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log,vicidial_list where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98');
# Time: 140213 11:09:31
# User@Host: cron[cron] @  [123.123.123.123]
# Query_time: 13.376370  Lock_time: 0.628954 Rows_sent: 1  Rows_examined: 433
SET timestamp=1392286171;
SELECT sum(pause_sec + wait_sec + talk_sec + dispo_sec) from vicidial_agent_log,vicidial_list where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and campaign_id IN('98')  and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000;
# Time: 140213 11:09:41
# User@Host: cron[cron] @  [123.123.123.123]
# Query_time: 10.593508  Lock_time: 0.142008 Rows_sent: 1  Rows_examined: 435
SET timestamp=1392286181;
select avg(wait_sec) from vicidial_agent_log,vicidial_list where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98');

(second time the problem occur the query took 231 seconds!!)

I knew I had run some report when it happen and the logs confirmed it:
123.123.123.2 - 6666 [13/Feb/2014:11:08:54 +0100] "GET /vicidial/AST_VDADstats.php HTTP/1.1" 200 24379 "http://123.123.123.123/vicidial/admin.php?ADD=999999" "Mozilla/5.0"


I tried that slow query and just removed ,vicidial_list
and voila:

Code: Select all

mysql> SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98');
+--------------------------------------+
| sum(wait_sec + talk_sec + dispo_sec) |
+--------------------------------------+
|                                 1084 |
+--------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log,vicidial_list where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98');
+--------------------------------------+
| sum(wait_sec + talk_sec + dispo_sec) |
+--------------------------------------+
|                            799945388 |
+--------------------------------------+
1 row in set (20.24 sec)


1012 times faster

and apparently this is how the query looked like before the change 2013-02-08 (140208-2033 - Added List select option)

now I noticed this text in the report page: "Lists: (optional, possibly slow)" no shit

if you look at the result from the query its very far off too!

So I did some tests:


build 140208-2033

list selection: --ALL--

Code: Select all

/vicidial/AST_VDADstats.php.orig1?agent_hours=&DB=1&outbound_rate=&costformat=&print_calls=&query_date=2014-02-13&end_date=2014-02-14&group%5B%5D=98&list_ids%5B%5D=--ALL--&include_rollover=NO&bottom_graph=NO&carrier_stats=NO&report_display_type=TEXT&shift=ALL&SUBMIT=SUBMIT

SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log,vicidial_list where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-14 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98') ;
Productivity Rating: 0

list selection: 997, 998

Code: Select all

/vicidial/AST_VDADstats.php.orig1?agent_hours=&DB=1&outbound_rate=&costformat=&print_calls=&query_date=2014-02-13&end_date=2014-02-14&group%5B%5D=98&list_ids%5B%5D=997&list_ids%5B%5D=998&include_rollover=NO&bottom_graph=NO&carrier_stats=NO&report_display_type=TEXT&shift=ALL&SUBMIT=SUBMIT

select count(*) from vicidial_closer_log where call_date >= '2014-02-13 00:00:00' and call_date <= '2014-02-14 23:59:59' and campaign_id IN('') and list_id IN('997','998') and status NOT IN('DROP','XDROP','HXFER','QVMAIL','HOLDTO','LIVE','QUEUE');
SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log,vicidial_list where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-14 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98') and vicidial_agent_log.lead_id=vicidial_list.lead_id and vicidial_list.list_id IN('997','998');
Productivity Rating: 1.9

no selected list selection

Code: Select all

/vicidial/AST_VDADstats.php.orig1?agent_hours=&DB=1&outbound_rate=&costformat=&print_calls=&query_date=2014-02-13&end_date=2014-02-14&group%5B%5D=98&include_rollover=NO&bottom_graph=NO&carrier_stats=NO&report_display_type=TEXT&shift=ALL&SUBMIT=SUBMIT

select count(*) from vicidial_closer_log where call_date >= '2014-02-13 00:00:00' and call_date <= '2014-02-14 23:59:59' and campaign_id IN('') and status NOT IN('DROP','XDROP','HXFER','QVMAIL','HOLDTO','LIVE','QUEUE');
SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-14 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98') ;
Productivity Rating: 0.11

build 140108-0730

no list selection because this was before that option

Code: Select all

/vicidial/AST_VDADstats_r2067.php?agent_hours=&DB=1&outbound_rate=&costformat=&print_calls=&query_date=2014-02-13&end_date=2014-02-14&group%5B%5D=98&include_rollover=NO&bottom_graph=NO&carrier_stats=NO&report_display_type=TEXT&shift=ALL&SUBMIT=SUBMIT

SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-14 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98');
Productivity Rating: 0.11

as you can see that the list selection queries is all wrong (look at the Productivity Rating value),
it will join with vicidial_list so only the number of leads will show and thus the productivity rating will be skyrocketing or the opposite when lists: --ALL-- is selected.
the query should left join instead to be able to grab all the data from vicidial_agent_log:

query without list selection:
SELECT * from vicidial_agent_log where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98');
-- 1084

bad query with list selection --ALL--
SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log,vicidial_list where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98');
-- 799945388

bad query with list selection 997, 998:
SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log,vicidial_list where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98') and vicidial_agent_log.lead_id=vicidial_list.lead_id and vicidial_list.list_id IN('997','998');
-- 63

good query with list selection:
SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log
left join vicidial_list on vicidial_list.lead_id=vicidial_agent_log.lead_id
and list_id IN('997','998')
where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000 and campaign_id IN('98');
-- 1084

I made a patch that changes the queries to left join and I posted it to the issue tracker: http://www.vicidial.org/VICIDIALmantis/view.php?id=741
With the patch the numbers are correct and the queries is fast, win win!

[jjohnson78]

Hello,

Thank you for your input. The first issue you cited with the vicidial_list table being included in the query was a bug; the table should not have been included in the query if the report was being run with no particular list IDs selected. We have corrected that.

As to the productivity level being skewed, this is an issue we've noticed when doing this report where specific list IDs are selected. There are many records in the vicidial_agent_log table where the agent is not in a call but will log wait times which contribute to the total time used to calculate the productivity level. The issue here is that these records do NOT have a lead ID associated with them and cannot be matched back to any selected list as a result, which means none of that time will be counted in calculating the productivity level. Below are two such examples from our test install:

Code: Select all

+---------------------+-------------+---------+----------+----------+-----------+
| event_time          | campaign_id | lead_id | wait_sec | talk_sec | dispo_sec |
+---------------------+-------------+---------+----------+----------+-----------+
| 2014-01-26 07:45:37 | TESTCAMP    |    NULL |       98 |        0 |         0 |
| 2014-01-26 08:16:37 | TESTCAMP    |    NULL |      343 |        0 |         0 |
+---------------------+-------------+---------+----------+----------+-----------+


What will happen here is that if you ran a report for 1/26/14 for the TESTCAMP campaign, and not specify any lists, these two records' times will be included in the calculations of the productivity level. If you specify a list or lists, these records' times will not be included because they don't tie back to a lead in the vicidial_list table.

Regarding your left join suggestion, in your example you have, the query without list IDs selected returns 1,084 seconds. Then the one where you specify certain list IDs within that campaign using a left join returns the same number of seconds; it should be returning fewer seconds because you are restricting the report to certain lists within the campaign. It should NOT return the same total wait/talk/dispo seconds as when you're doing it for the whole campaign. It should be lower because of both the above issue with the null lead IDs and also because you aren't reporting on every list within the campaign called within the specified time frame (running a right join seems to return vicidial_agent_log records only for the selected lists, which is what it should be doing).

For right now, we're just changing the report to not calculate productivity levels if it's run for specific lists, since as mentioned above the null lead ID issue causes skewing.

Thanks for your suggestions!

[DomeDan]

Good you're looking into this! But you have misunderstood some things,
the "left join-query" shows the same number as the first query because list_id 997 and 998 are all the lists in that campaign.

I know its a lot of NULL lead_id in vicidial_agent_log, that is the reason I use left join.

And its not just productivity rating that was wrong when selecting list_id, every other values that was based on those queries with the bad join was affected. Here is an example (AGENT TIME CALLS/HOUR):

Code: Select all

build 140215-0704 and lists: --ALL--
---------- CALL STATUS STATS
+--------+----------------------+----------------------+------------+----------------------------------+----------+
|        |                      |                      |            |      CALL TIME                   |AGENT TIME|
| STATUS | DESCRIPTION          | CATEGORY             | CALLS      | TOTAL TIME | AVG TIME |CALLS/HOUR|CALLS/HOUR|
+--------+----------------------+----------------------+------------+------------+----------+----------+----------+
20912|1|SELECT sum(pause_sec + wait_sec + talk_sec + dispo_sec) from vicidial_agent_log where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and campaign_id IN('98')  and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000;
select count(*),status,sum(length_in_sec) from vicidial_log where call_date >= '2014-02-13 00:00:00' and call_date <= '2014-02-13 23:59:59'  and campaign_id IN('98')  group by status;
| A      | Telefonsvarare       | UNDEFINED            |          1 |       0:11 |     0:11 |   138.46 |     0.17 |
| CALLBK | Återuppringning     | UNDEFINED            |          1 |       0:00 |     0:00 |   138.46 |     0.17 |
| DROP   | Agent Not Available  | UNDEFINED            |          1 |       0:03 |     0:03 |   138.46 |     0.17 |
| N      | Inget svar           | UNDEFINED            |          1 |       0:12 |     0:12 |   138.46 |     0.17 |
+--------+----------------------+----------------------+------------+------------+----------+----------+----------+
| TOTAL:                                               |          4 |       0:26 |     0:07 |   553.85 |          |
+------------------------------------------------------+------------+------------+---------------------+----------+


build 140215-0704 and lists: 997,998
---------- CALL STATUS STATS
+--------+----------------------+----------------------+------------+----------------------------------+----------+
|        |                      |                      |            |      CALL TIME                   |AGENT TIME|
| STATUS | DESCRIPTION          | CATEGORY             | CALLS      | TOTAL TIME | AVG TIME |CALLS/HOUR|CALLS/HOUR|
+--------+----------------------+----------------------+------------+------------+----------+----------+----------+
137|1|SELECT sum(pause_sec + wait_sec + talk_sec + dispo_sec) from vicidial_agent_log,vicidial_list where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and campaign_id IN('98') and vicidial_agent_log.lead_id=vicidial_list.lead_id and vicidial_list.list_id IN('997','998') and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000;
select count(*),status,sum(length_in_sec) from vicidial_log where call_date >= '2014-02-13 00:00:00' and call_date <= '2014-02-13 23:59:59'  and campaign_id IN('98') and list_id IN('997','998') group by status;
| A      | Telefonsvarare       | UNDEFINED            |          1 |       0:11 |     0:11 |   138.46 |    26.28 |
| CALLBK | Återuppringning     | UNDEFINED            |          1 |       0:00 |     0:00 |   138.46 |    26.28 |
| DROP   | Agent Not Available  | UNDEFINED            |          1 |       0:03 |     0:03 |   138.46 |    26.28 |
| N      | Inget svar           | UNDEFINED            |          1 |       0:12 |     0:12 |   138.46 |    26.28 |
+--------+----------------------+----------------------+------------+------------+----------+----------+----------+
| TOTAL:                                               |          4 |       0:26 |     0:07 |   553.85 |          |
+------------------------------------------------------+------------+------------+---------------------+----------+

build 140208-2033 with my patch applied and lists: 997,998
---------- CALL STATUS STATS
+--------+----------------------+----------------------+------------+----------------------------------+----------+
|        |                      |                      |            |      CALL TIME                   |AGENT TIME|
| STATUS | DESCRIPTION          | CATEGORY             | CALLS      | TOTAL TIME | AVG TIME |CALLS/HOUR|CALLS/HOUR|
+--------+----------------------+----------------------+------------+------------+----------+----------+----------+
20912|1|SELECT sum(pause_sec + wait_sec + talk_sec + dispo_sec) from vicidial_agent_log left join vicidial_list on vicidial_list.lead_id=vicidial_agent_log.lead_id and vicidial_list.list_id IN('997','998') where event_time >= '2014-02-13 00:00:00' and event_time <= '2014-02-13 23:59:59' and campaign_id IN('98') and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000;
select count(*),status,sum(length_in_sec) from vicidial_log where call_date >= '2014-02-13 00:00:00' and call_date <= '2014-02-13 23:59:59'  and campaign_id IN('98') and list_id IN('997','998') group by status;
| A      | Telefonsvarare       | UNDEFINED            |          1 |       0:11 |     0:11 |   138.46 |     0.17 |
| CALLBK | Återuppringning      | UNDEFINED            |          1 |       0:00 |     0:00 |   138.46 |     0.17 |
| DROP   | Agent Not Available  | UNDEFINED            |          1 |       0:03 |     0:03 |   138.46 |     0.17 |
| N      | Inget svar           | UNDEFINED            |          1 |       0:12 |     0:12 |   138.46 |     0.17 |
+--------+----------------------+----------------------+------------+------------+----------+----------+----------+
| TOTAL:                                               |          4 |       0:26 |     0:07 |   553.85 |          |
+------------------------------------------------------+------------+------------+---------------------+----------+


Here is a little more info and tests:

SELECT * FROM `vicidial_agent_log` WHERE date(`event_time`) = '2014-02-13' and campaign_id = '98'
-- 42 rows

SELECT * FROM `vicidial_agent_log` WHERE date(`event_time`) = '2014-02-13' and campaign_id = '98' and lead_id is not NULL
-- 3 rows

# A regular join, its the same as doing "vicidial_agent_log,vicidial_list":
SELECT * FROM `vicidial_agent_log` a
join vicidial_list l
WHERE date(`event_time`) = '2014-02-13' and campaign_id = '98';
-- all rows in vicidial_list

# And if you add "on l.lead_id=a.lead_id":
SELECT * FROM `vicidial_agent_log` a
join vicidial_list l on l.lead_id=a.lead_id
WHERE date(`event_time`) = '2014-02-13' and campaign_id = '98'
-- 3 rows

# The "left join" is what we want in this situation:
SELECT * FROM `vicidial_agent_log` a
left join vicidial_list l on l.lead_id=a.lead_id
WHERE date(`event_time`) = '2014-02-13' and campaign_id = '98'
-- 42 rows

# A "right join" will be wrong too:
SELECT * FROM `vicidial_agent_log` a
right join vicidial_list l on l.lead_id=a.lead_id
WHERE date(`event_time`) = '2014-02-13' and campaign_id = '98'
-- 3 rows

just apply my patch to build 140208-2033 I made and compare the results from before the patching

[jjohnson78]

Hello,

The problem with you using the left join the way you are in the patch is that I don't think it is returning the right records.

It only seems to work for the particular situation in which you tested it; i.e. the user is selecting all the lists in the campaign.

Example:

Code: Select all

select campaign_id, count(*) From vicidial_agent_log where event_time>='2013-10-01' and event_time<'2013-11-01' group by campaign_id;
+-------------+----------+
| campaign_id | count(*) |
+-------------+----------+
| TESTCAMP    |       51 |
+-------------+----------+


A query of the vicidial_log table for the same campaign and time frame gives the list ID breakdown (this is assuming outbound-only dialing, which actually is the case here):

Code: Select all

select list_id, count(*) From vicidial_log where call_date>='2013-10-01' and call_date<'2013-11-01' and campaign_id='TESTCAMP' group by list_id;
+---------+----------+
| list_id | count(*) |
+---------+----------+
|     107 |        2 |
|     999 |       13 |
+---------+----------+


Now, if we ran the report for list IDs 107 and 999 using the syntax you suggested, this is the output:

Code: Select all

SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log left join vicidial_list on vicidial_list.lead_id=vicidial_agent_log.lead_id and vicidial_list.list_id IN('107', '999') where event_time >= '2013-10-01 00:00:00' and event_time <= '2013-10-31 23:59:59' and campaign_id IN('TESTCAMP') and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000;
+--------------------------------------+
| sum(wait_sec + talk_sec + dispo_sec) |
+--------------------------------------+
|                                  636 |
+--------------------------------------+


Also, this is what it outputs if we do not sum it and include the lead ID and list ID from vicidial_list (i.e. show each individual record from vicidial_agent_log combined with the list ID for each record that has a lead ID tying back to vicidial_list)

Code: Select all

SELECT wait_sec + talk_sec + dispo_sec, vicidial_list.lead_id, vicidial_list.list_id from vicidial_agent_log left join vicidial_list on vicidial_list.lead_id=vicidial_agent_log.lead_id and vicidial_list.list_id IN('107', '999') where event_time >= '2013-10-01 00:00:00' and event_time <= '2013-10-31 23:59:59' and campaign_id IN('TESTCAMP') and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000;
+---------------------------------+---------+---------+
| wait_sec + talk_sec + dispo_sec | lead_id | list_id |
+---------------------------------+---------+---------+
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               3 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              55 |  402558 |     999 |
|                               0 |    NULL |    NULL |
|                               2 |    NULL |    NULL |
|                              18 |  273846 |     999 |
|                               0 |    NULL |    NULL |
|                               2 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              28 |  815910 |     107 |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              68 |  273846 |     999 |
|                               0 |    NULL |    NULL |
|                               7 |    NULL |    NULL |
|                              43 |  834387 |     107 |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              64 |  273846 |     999 |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               3 |    NULL |    NULL |
|                               1 |    NULL |    NULL |
|                              46 |  273846 |     999 |
|                               0 |    NULL |    NULL |
|                              21 |  273846 |     999 |
|                               0 |    NULL |    NULL |
|                              28 |  273846 |     999 |
|                               0 |    NULL |    NULL |
|                              15 |  273846 |     999 |
|                               0 |    NULL |    NULL |
|                              21 |  426097 |     999 |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              63 |  426097 |     999 |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               5 |    NULL |    NULL |
|                              66 |  426097 |     999 |
|                               0 |    NULL |    NULL |
|                              29 |    NULL |    NULL |
|                               2 |    NULL |    NULL |
|                              34 |  273846 |     999 |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              12 |  273846 |     999 |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
+---------------------------------+---------+---------+


However, if we run it for only list ID 107, a problem arises because it returns the same total time and the same number of records. It is not filtering out list ID 999's records, which is what the original modification to this report is supposed to do:

Code: Select all

SELECT sum(wait_sec + talk_sec + dispo_sec) from vicidial_agent_log left join vicidial_list on vicidial_list.lead_id=vicidial_agent_log.lead_id and vicidial_list.list_id IN('107') where event_time >= '2013-10-01 00:00:00' and event_time <= '2013-10-31 23:59:59' and campaign_id IN('TESTCAMP') and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000;
+--------------------------------------+
| sum(wait_sec + talk_sec + dispo_sec) |
+--------------------------------------+
|                                  636 |
+--------------------------------------+


Running the report without summing and including the lead ID and list ID, as above, shows that it's still returning 51 records, it's just leaving the ones that were matched to list ID 999 as null for the lead and list ID:

Code: Select all

+---------------------------------+---------+---------+
| wait_sec + talk_sec + dispo_sec | lead_id | list_id |
+---------------------------------+---------+---------+
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               3 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              55 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               2 |    NULL |    NULL |
|                              18 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               2 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              28 |  815910 |     107 |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              68 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               7 |    NULL |    NULL |
|                              43 |  834387 |     107 |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              64 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               3 |    NULL |    NULL |
|                               1 |    NULL |    NULL |
|                              46 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              21 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              28 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              15 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              21 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              63 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               5 |    NULL |    NULL |
|                              66 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              29 |    NULL |    NULL |
|                               2 |    NULL |    NULL |
|                              34 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                              12 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
|                               0 |    NULL |    NULL |
+---------------------------------+---------+---------+


Did you test your patch by running this report for some, not all, of the lists for a selected campaign?

[DomeDan]

Oh I see!
I did test with a single list and saw the numbers in the report change so I thought it was working as expected.

It seams like the current structure of the database-table makes it impossible to determine wait_sec and pause_sec per list_id because they often don't have a lead_id

but then "---------- CALL STATUS STATS" needs to be checked too because those numbers are calculated with a vicidial_list join