Export Call Report with carrier info

[covarrubiasgg]

Hello.

I am trying to attach the carrier to the Export Call Report, i am wondering if someone already have done this before.

Until now my best approach is to JOIN vicidial_log and call_log and get the carrier from the channel string i can wotk with this, but there are many many Call Attempts that has length_in_sec 0 and doesnt have any record under call_log, i am matching via uniqueid.

MariaDB [asterisk]> select * from vicidial_log AS a LEFT JOIN call_log as b ON a.uniqueid = b.uniqueid WHERE DATE_FORMAT(call_date,'%Y-%m-%d') BETWEEN '2014-12-01' and '2014-12-01' AND b.uniqueid IS NOT NULL LIMIT 2;
+-------------------+---------+------------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+----------+-----------+------------+-------------+----------+--------------+-------------------+---------------------+--------------------+-------+---------------+-------------+---------------+----------------------+---------------------+-------------+---------------------+------------+---------------+---------------+
| uniqueid | lead_id | list_id | campaign_id | call_date | start_epoch | end_epoch | length_in_sec | status | phone_code | phone_number | user | comments | processed | user_group | term_reason | alt_dial | called_count | uniqueid | channel | channel_group | type | server_ip | extension | number_dialed | caller_code | start_time | start_epoch | end_time | end_epoch | length_in_sec | length_in_min |
+-------------------+---------+------------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+----------+-----------+------------+-------------+----------+--------------+-------------------+---------------------+--------------------+-------+---------------+-------------+---------------+----------------------+---------------------+-------------+---------------------+------------+---------------+---------------+
| 1417455696.256899 | 51285 | 2014100202 | CAMPAIGN | 2014-12-01 09:41:49 | 1417455709 | 1417455758 | 49 | DEC | 1 | 619XXXXXXX | 308 | MANUAL | N | AGENTS | AGENT | MAIN | 4 | 1417455696.256899 | SIP/CARRIER2-0000d6c9 | Local Channel Line | Local | 192.168.4.147 | 7619XXXXXXX | 7619XXXXXXX | M2010941350000051285 | 2014-12-01 09:41:36 | 1417455696 | 2014-12-01 09:42:38 | 1417455758 | 62 | 1.03 |
| 1417455924.256929 | 41860 | 2014100202 | CAMPAIGN | 2014-12-01 09:45:39 | 1417455939 | 1417455967 | 28 | DEC | 1 | 619XXXXXXX | 308 | 619XXXXXXXMANUAL | N | AGENTS | AGENT | MAIN | 5 | 1417455924.256929 | SIP/CARRIER1-0000d6cf | Local Channel Line | Local | 192.168.4.147 | 7619XXXXXXX | 7619XXXXXXX | M2010945240000041860 | 2014-12-01 09:45:24 | 1417455924 | 2014-12-01 09:46:07 | 1417455967 | 43 | 0.72 |
+-------------------+---------+------------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+----------+-----------+------------+-------------+----------+--------------+-------------------+---------------------+--------------------+-------+---------------+-------------+---------------+----------------------+---------------------+-------------+---------------------+------------+---------------+---------------+

First i get all the rows only restricted by date

Code: Select all

MariaDB [asterisk]> select count(*) from vicidial_log AS a LEFT JOIN call_log AS b ON a.uniqueid = b.uniqueid WHERE DATE_FORMAT(call_date,'%Y-%m-%d') BETWEEN '2014-12-01' and '2014-12-31';
+----------+
| count(*) |
+----------+
|   244064 |
+----------+
1 row in set (0.25 sec)


But there are A LOT of missing entries on call_log

Code: Select all

MariaDB [asterisk]> select count(*) from vicidial_log AS a LEFT JOIN call_log AS b ON a.uniqueid = b.uniqueid WHERE DATE_FORMAT(call_date,'%Y-%m-%d') BETWEEN '2014-12-01' and '2014-12-31' AND b.uniqueid IS NULL;
+----------+
| count(*) |
+----------+
|   106107 |
+----------+
1 row in set (3.59 sec)

Can you point me to a better approach for this?

Also i realize that there are more records on vicidial_log then what i get if a run the Export Call Report to all the campaigns, why could be happening this difference?

For example:

Code: Select all

MariaDB [asterisk]> select count(*) from vicidial_log WHERE DATE_FORMAT(call_date,'%Y-%m-%d') BETWEEN '2014-12-03' and '2014-12-03';+----------+
| count(*) |
+----------+
|     6351 |
+----------+
1 row in set (0.22 sec)

But if i run the Export Call Report selecting all the campaigns it only gives me 6040 records, i would like to know why those 311 of difference.

Hope anyone can help with this, thank you in advance!

[mflorell]

Different processes write to different log tables depending on the call type and how it was initiated. All sorts of things can go wrong depending on the type of call and the carrier you are sending it through so the differences are almost always there.

Since this report, and several others, pull data from multiple tables, there is a chance that one of them will miss data from some calls.

We actually started working on a report last year that would attempt to combine all of them for a client, but after we created the beta version for them we never heard back. I'll have to look at where we are with that report and see if we could possibly add that to svn/trunk.

[covarrubiasgg]

This is driving me crazy

Basically all this started because i need to compare the CDR provided by my carrier and the logs in vicidial

I am comparing OUTBOUND CALLS ONLY using DIAL NEXT NUMBER + MANUAL !!

But i realize a lot of errors and i am not sure where to start debugging.

For example i found a scenario where i have call in vicidial_log with lenght_in_sec = 0 and with no entry in call_log and it seems ok because if the call lenght was 0, maybe there was an error and the call never had valid channel.

BUT when i was comparing with my carrier, those calls has 1, 2, 5 minutes duration !!!, i though my carrier was wrong. After that i noticed that those calls have recordings and them matches with what my carriers reports.

Now i am a little scare because i dont know if the reports i have using all the time are accurate enough

[mflorell]

What vicidial.php version and build are you using?

I found the beta version of the script I mentioned, just need to update it for the language changes we finished within the last month to make it work with the current svn/trunk code.

[covarrubiasgg]

VERSION: 2.10-464a
BUILD: 150101-1511

But i think we are about to upgrade next friday, because on that build we are in the middle of the translation upgrade, that build is 15 days old and there are already 20+ updates available .

[mflorell]

It has been added as the "Export Calls Report Carrier", it's linked from the Admin Utilities page.

Yes we have been very busy fixing issues and closing Issue Tracker tickets recently

[covarrubiasgg]

You are awesome! already made the upgrade and the Export Call Report Carrier work like a charm. I just need to figure out what happened with the calls that are not on the call_log, but the report works perfect with the ones that do have data on call_log.

Thank you again and again and again =D

[mflorell]

Glad I could help! Please post if you find anything out about the calls with missing records. It is a known issue, but not one that we can replicate easily enough to find a reason for it.

[covarrubiasgg]

UPDATE

I have been debugging a lot regarding the missing records, at first i touhgth maybe the call_log FASTAGI was crashing and not recording those entries, so i installed the old cdr_mysql.so and let asterisk to handle the cdr, my surprise was there was missing records too.

So i compared the records from the call_log table and from the newly created cdr table and they were exactly the same !!!

I realized that the uniqueid field on the vicidial_log table is wrong.

i will show you the following example:

This is from vicidial_log, please note the uniqueid and the call_date

MariaDB [asterisk]> select * from vicidial_log WHERE phone_number = XXXXXX2073;
+----------------------+---------+------------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+----------+-----------+------------+-------------+----------+--------------+
| uniqueid | lead_id | list_id | campaign_id | call_date | start_epoch | end_epoch | length_in_sec | status | phone_code | phone_number | user | comments | processed | user_group | term_reason | alt_dial | called_count |
+----------------------+---------+------------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+----------+-----------+------------+-------------+----------+--------------+
| 1422985665.1005 | 570008 | 2015020302 | PORTAMXL | 2015-02-03 09:48:16 | 1422985696 | 1422985697 | 1 | A | 52 | XXXXXX2073 | 27 | MANUAL | N | OPERADORES | AGENT | MAIN | 1 |
| 1423866185.8955 | 570008 | 2015020302 | PORTAMXL | 2015-02-13 14:23:17 | 1423866197 | 1423866246 | 49 | DSP | 52 | XXXXXX2073 | 695 | MANUAL | N | OPERADORES | CALLER | MAIN | 2 |
| 1424109481.1279 | 570008 | 2015020302 | PORTAMXL | 2015-02-16 09:58:11 | 1424109491 | 1424109555 | 64 | CALLBK | 52 | XXXXXX2073 | 315 | MANUAL | N | OPERADORES | CALLER | MAIN | 3 |
| 1424196063.000570008 | 570008 | 2015020302 | PORTATJ | 2015-02-17 10:01:03 | 1424196063 | 1424196063 | 0 | A | 52 | XXXXXX2073 | 315 | MANUAL | N | OPERADORES | AGENT | MANUAL | 4 |
| 1424196101.000570008 | 570008 | 2015020302 | PORTATJ | 2015-02-17 10:01:41 | 1424196101 | 1424196101 | 0 | A | 52 | XXXXXX2073 | 315 | MANUAL | N | OPERADORES | AGENT | MANUAL | 5 |
+----------------------+---------+------------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+----------+-----------+------------+-------------+----------+--------------+
5 rows in set, 328 warnings (0.22 sec)

This is from call_log and was generated by the fastagi please note the uniqueid and the call_date

MariaDB [asterisk]> select * from call_log WHERE number_dialed LIKE '%XXXXXX2073%';
+-----------------+----------------------------------+--------------------+-------+---------------+-------------+---------------+----------------------+---------------------+-------------+---------------------+------------+---------------+---------------+
| uniqueid | channel | channel_group | type | server_ip | extension | number_dialed | caller_code | start_time | start_epoch | end_time | end_epoch | length_in_sec | length_in_min |
+-----------------+----------------------------------+--------------------+-------+---------------+-------------+---------------+----------------------+---------------------+-------------+---------------------+------------+---------------+---------------+
| 1422985665.1005 | SIP/arad4593-00000109 | Local Channel Line | Local | 192.168.4.147 | 8XXXXXX2073 | 8XXXXXX2073 | M2030947450000570008 | 2015-02-03 09:47:45 | 1422985665 | 2015-02-03 09:48:17 | 1422985697 | 32 | 0.53 |
| 1423866185.8955 | SIP/arad4593-000007a9 | Local Channel Line | Local | 192.168.4.147 | 8XXXXXX2073 | 8XXXXXX2073 | M2131423050000570008 | 2015-02-13 14:23:05 | 1423866185 | 2015-02-13 14:23:53 | 1423866233 | 48 | 0.80 |
| 1424109481.1279 | SIP/arad4593-00000110 | Local Channel Line | Local | 192.168.4.147 | 8XXXXXX2073| 8XXXXXX2073 | M2160958010000570008 | 2015-02-16 09:58:01 | 1424109481 | 2015-02-16 09:59:15 | 1424109555 | 74 | 1.23 |
| 1424196032.1092 | Local/8600073@default-000001aa;1 | Local Channel Line | Local | 192.168.4.148 | 8XXXXXX2073 | 8XXXXXX2073 | M2171000320000570008 | 2015-02-17 10:00:32 | 1424196032 | 0000-00-00 00:00:00 | 0 | 0 | 0.00 |
| 1424196067.1100 | Local/8600073@default-000001ad;1 | Local Channel Line | Local | 192.168.4.148 | 8XXXXXX2073 | 8XXXXXX2073 | M2171001070000570008 | 2015-02-17 10:01:07 | 1424196067 | 0000-00-00 00:00:00 | 0 | 0 | 0.00 |
+-----------------+----------------------------------+--------------------+-------+---------------+-------------+---------------+----------------------+---------------------+-------------+---------------------+------------+---------------+---------------+
5 rows in set (0.39 sec)

This is from bit_cdr generated by the cdr_mysql module please note the uniqueid and the call_date

MariaDB [asterisk]> select * from bit_cdr WHERE dst LIKE '%XXXXXX2073%';
+---------------------+-------------------------------------+------------+-------------+----------+----------------------------------+-----------------------+---------+--------------------------------+----------+---------+-------------+----------+-------------+-----------+-----------------+-----------------+----------+-------------+
| calldate | clid | src | dst | dcontext | channel | dstchannel | lastapp | lastdata | duration | billsec | disposition | amaflags | accountcode | userfield | uniqueid | linkedid | sequence | peeraccount |
+---------------------+-------------------------------------+------------+-------------+----------+----------------------------------+-----------------------+---------+--------------------------------+----------+---------+-------------+----------+-------------+-----------+-----------------+-----------------+----------+-------------+
| 2015-02-17 10:00:32 | "M2171000320000570008" <6642314593> | 6642314593 | 8XXXXXX2073 | default | Local/8600073@default-000001aa;1 | SIP/arad4593-000000df | Dial | SIP/arad4593/045XXXXXX2073,,To | 31 | 31 | NO ANSWER | 3 | | | 1424196032.1092 | 1424196032.1092 | 1193 | |
| 2015-02-17 10:01:07 | "M2171001070000570008" <6642314593> | 6642314593 | 8XXXXXX2073 | default | Local/8600073@default-000001ad;1 | SIP/arad4593-000000e1 | Dial | SIP/arad4593/045XXXXXX2073,,To | 34 | 34 | NO ANSWER | 3 | | | 1424196067.1100 | 1424196067.1100 | 1202 | |
+---------------------+-------------------------------------+------------+-------------+----------+----------------------------------+-----------------------+---------+--------------------------------+----------+---------+-------------+----------+-------------+-----------+-----------------+-----------------+----------+-------------+
2 rows in set (0.01 sec)

As you can see the uniqueid generated by the call_log and bit_cdr are the same, but vicidial_log is getting a different uniqueid, i am not familiar on how vicidial generates the vicidial_log but it seems like there is a bug somewhere, i will try to look around.

[mflorell]

The uniqueid depends on how the call was placed. How exactly were these example calls placed, and what is the vicidial.php version and build you are using?

[covarrubiasgg]

VERSION: 2.10-472a
BUILD: 150120-0749

This setup only has dial_next_number campaigns and agents are allowed to place MANUAL Calls...

[covarrubiasgg]

Afte your comments in saying that it depends on the type of call how the uniqueid is going to be generate, i realized that only those calls which contains this kind of format are not matching the call_log table.

(Note the long numbers after the decimal)

Code: Select all

MariaDB [asterisk]> SELECT a.call_date, a.phone_number, a.uniqueid, b.uniqueid from vicidial_log AS a LEFT JOIN call_log AS b ON a.uniqueid=b.uniqueid WHERE DATE_FORMAT(call_date,'%Y-%m-%d') BETWEEN '2015-02-17' AND '2015-02-17' AND b.uniqueid IS NULL limit 20;
+---------------------+--------------+----------------------+----------+
| call_date           | phone_number | uniqueid             | uniqueid |
+---------------------+--------------+----------------------+----------+
| 2015-02-17 08:49:19 | XXXXXX3801   | 1424191759.000557750 | NULL     |
| 2015-02-17 09:11:38 | XXXXXX3002   | 1424193098.000568358 | NULL     |
| 2015-02-17 09:12:14 | XXXXXX4649   | 1424193134.000616428 | NULL     |
| 2015-02-17 09:12:20 | XXXXXX3720   | 1424193140.000563806 | NULL     |
| 2015-02-17 09:14:19 | XXXXXX5412   | 1424193259.000557075 | NULL     |
| 2015-02-17 09:16:07 | XXXXXX7289   | 1424193367.000531229 | NULL     |
| 2015-02-17 09:16:50 | XXXXXX4966   | 1424193410.000396330 | NULL     |
| 2015-02-17 09:16:51 | 6671504874   | 1424193411.000436474 | NULL     |
| 2015-02-17 09:17:25 | XXXXXX1449   | 1424193445.000531618 | NULL     |
| 2015-02-17 09:17:54 | XXXXXX9158   | 1424193474.000567663 | NULL     |
| 2015-02-17 09:18:11 | XXXXXX9972   | 1424193491.000564587 | NULL     |
| 2015-02-17 09:18:03 | XXXXXX2082   | 1424193483.000449450 | NULL     |
| 2015-02-17 09:18:23 | XXXXXX6073   | 1424193503.000563974 | NULL     |
| 2015-02-17 09:18:46 | XXXXXX9195   | 1424193526.000572334 | NULL     |
| 2015-02-17 09:18:50 | XXXXXX3215   | 1424193530.000530735 | NULL     |
| 2015-02-17 09:19:02 | XXXXXX2610   | 1424193542.000564992 | NULL     |
| 2015-02-17 09:19:23 | XXXXXX9646   | 1424193563.000572398 | NULL     |
| 2015-02-17 09:19:28 | XXXXXX9955   | 1424193568.000572444 | NULL     |
| 2015-02-17 09:20:08 | XXXXXX5551   | 1424193608.000570656 | NULL     |
| 2015-02-17 09:20:42 | XXXXXX8165   | 1424193642.000571148 | NULL     |
+---------------------+--------------+----------------------+----------+

Here is a mixed example, please note on how only the ones with the long format does not exists on the call log:

Code: Select all

MariaDB [asterisk]> SELECT a.call_date, a.phone_number, a.uniqueid, b.uniqueid from vicidial_log AS a LEFT JOIN call_log AS b ON a.uniqueid=b.uniqueid WHERE DATE_FORMAT(call_date,'%Y-%m-%d') BETWEEN '2015-02-17' AND '2015-02-17' limit 20;
+---------------------+--------------+----------------------+---------------+
| call_date           | phone_number | uniqueid             | uniqueid      |
+---------------------+--------------+----------------------+---------------+
| 2015-02-17 08:21:35 | XXXXXX5929   | 1424190089.4         | 1424190089.4  |
| 2015-02-17 08:23:38 | XXXXXX6524   | 1424190213.9         | 1424190213.9  |
| 2015-02-17 08:49:19 | XXXXXX3801   | 1424191759.000557750 | NULL          |
| 2015-02-17 09:09:49 | XXXXXX3801   | 1424192986.11        | 1424192986.11 |
| 2015-02-17 09:09:58 | XXXXXX3329   | 1424192991.27        | 1424192991.27 |
| 2015-02-17 09:10:05 | XXXXXX8679   | 1424193000.32        | 1424193000.32 |
| 2015-02-17 09:10:33 | XXXXXX3801   | 1424193030.18        | 1424193030.18 |
| 2015-02-17 09:10:41 | XXXXXX4339   | 1424193039.39        | 1424193039.39 |
| 2015-02-17 09:10:58 | XXXXXX0958   | 1424193050.44        | 1424193050.44 |
| 2015-02-17 09:11:29 | XXXXXX8635   | 1424193083.27        | 1424193083.27 |
| 2015-02-17 09:11:29 | XXXXXX1605   | 1424193083.51        | 1424193083.51 |
| 2015-02-17 09:11:38 | XXXXXX3002   | 1424193098.000568358 | NULL          |
| 2015-02-17 09:11:50 | XXXXXX0056   | 1424193105.61        | 1424193105.61 |
| 2015-02-17 09:12:07 | XXXXXX3255   | 1424193120.46        | 1424193120.46 |
| 2015-02-17 09:12:14 | XXXXXX4649   | 1424193134.000616428 | NULL          |
| 2015-02-17 09:12:17 | XXXXXX0597   | 1424193132.51        | 1424193132.51 |
| 2015-02-17 09:12:20 | XXXXXX3720   | 1424193140.000563806 | NULL          |
| 2015-02-17 09:12:31 | XXXXXX0478   | 1424193131.66        | 1424193131.66 |
| 2015-02-17 09:12:41 | XXXXXX8124   | 1424193146.73        | 1424193146.73 |
| 2015-02-17 09:12:52 | XXXXXX0920   | 1424193169.83        | 1424193169.83 |
+---------------------+--------------+----------------------+---------------+

But if i looked more closely those numbers and i realized that all of them exists on the call_log table but using a different uniqueid....

[DomeDan]

I have too been down that road and found that I can not join tables with uniqueid

I had forgot about it but did some research now and this is what I found

Uniqueid comes from the asterisk variable and here is a little explanation of that variable: https://wiki.asterisk.org/wiki/display/ ... +Variables

...Another channel variable that Asterisk automatically creates is the UNIQUEID variable. Each channel within Asterisk receives a unique identifier, and that identifier is stored in the UNIQUEID variable. The UNIQUEID is in the form of 1267568856.11, where 1267568856 is the Unix epoch, and 11 shows that this is the eleventh call on the Asterisk system since it was last restarted.

Looked at a random NA (auto-dial) call in my database and for example I can see this call in vicidial_log:
uniqueid: 1409645562.2968535 start_epoch: 1409645584 end_epoch: 1409645584 phone_number: XXXXXXXX status: NA
and in call_log:
uniqueid: 1409645562.2968535 start_epoch: 1409645562 end_epoch: 1409645582 number_dialed: 90047XXXXXXXX


compare that to a call with status NI (auto-dial) in vicidial_log:
uniqueid: 1424255217.284224 start_epoch: 1424255234 end_epoch: 1424255295 phone_number: XXXXXXXX status: NI
and in call_log:
uniqueid: 1424255217.284224 start_epoch: 1424255234 end_epoch: 1424255292 number_dialed: 8368
uniqueid: 1424255217.284225 start_epoch: 1424255217 end_epoch: 1424255234 number_dialed: 90047XXXXXXXX


As you can see, a second entry is added to the call_log when asterisk calls a channel to connect the call to an agent
its a bit weird because it starts to call the number and calls it 1424255217.284225 untill its answered, the start/stop epoch is only when its dialing and stop is when it is answered.
then there is an other call entry when the channel with an agent is called, starts the epoch when the external call is answered and stop when the customer is hung up.
a vicidial_log is inserted based on the call to the channel

when there is a NA the channel was not called, so the uniqueid is the same as in the call_log. and start/stop is the same and is when the call is ended and done

So I guess manual dial will get the same uniqeid in both tables too.
but when it comes to 3way calls and stuff like that then there will probably be more entries in call_log with different uniqueid

hope my explanation helped you

[mflorell]

Those long number uniqueids are made up by vicidial because no uniqueid exists because the call was never completed usually due to it being an instant 0-second hangup because of disconnect or busy. Vicidial creates those uniqueids in that format so you can see it was made up, and so that there is something in that field instead of it just being blank.