Tracing Nanqueue calls in database logs

[developerdan]

Hi Folks,
Hopefully this is the correct place for this, when it comes to NANQUEUE calls there seems to be a missing link in reporting unless im just not able to find it?

From what I can tell if a call comes in that hits an ingroup and the NANQUEUE is set to go to another ingroup where there are agents, thats traceable via the xfer_log table.

However if a call hits NANQUEUE multiple times (in my case twice) before getting to an agent, there is no connection between the call_log entry and the xfer_log entry (or any other log in fact).
We built a stereo recording system for these calls so need the channel field from the call_log table (also the time is useful too).

The only tenuous link I have seen is the uniqueids first 2 characters after the timestamp (i.e. the .32 in : 1621441604.32513) but is this reliable? or is there another more reliable way to do this?

Appreciate any information you have.

Regards,
Dan

[developerdan]

Also just FYI, im not against modifying the AGI script/s as needed but was hoping to avoid it as it makes upgrading harder in future.

[mflorell]

If you are using the CLOSER "Handle Method" on your 'No Agent No Queue' settings, then the lead_id should be the same on all legs of the call, and it should be easier to match up the calls in the log to the original vicidial_did_log entry(which will have the first channel name in it):

Code: Select all

MariaDB [asterisk]> select * from vicidial_did_log order by call_date desc  limit 1;
+------------------+------------------+---------------+------------------+----------------+------------+---------------------+--------+-----------+
| uniqueid         | channel          | server_ip     | caller_id_number | caller_id_name | extension  | call_date           | did_id | did_route |
+------------------+------------------+---------------+------------------+----------------+------------+---------------------+--------+-----------+
| 1621596854.25264 | IAX2/cc352-14011 | 192.168.198.5 | 3125551212       | station 352    | 8135551121 | 2021-05-21 07:34:14 | 94     | IN_GROUP  |
+------------------+------------------+---------------+------------------+----------------+------------+---------------------+--------+-----------+
1 row in set (0.00 sec)

MariaDB [asterisk]> select * from vicidial_closer_log order by call_date desc limit 3;
+-------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+---------------+-----------+---------------+------------+------------+-------------+------------------+------------+----------------+--------------+
| closecallid | lead_id | list_id | campaign_id | call_date           | start_epoch | end_epoch  | length_in_sec | status | phone_code | phone_number | user | comments      | processed | queue_seconds | user_group | xfercallid | term_reason | uniqueid         | agent_only | queue_position | called_count |
+-------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+---------------+-----------+---------------+------------+------------+-------------+------------------+------------+----------------+--------------+
|       55576 | 2013387 |     999 | TEST_IN3    | 2021-05-21 07:34:19 |  1621596859 | 1621596889 |            30 | NP     | 1          | 3125551212   | 6666 | AUTO          | N         |         17.00 | ADMIN      |      48896 | AGENT       | 1621596859.25268 |            |              1 |            1 |
|       55575 | 2013387 |     999 | NANQUE_2    | 2021-05-21 07:34:18 |  1621596858 | 1621596858 |             1 | NANQUE | 1          | 3125551212   | VDCL | NO AGENT DROP | NULL      |          1.00 | NULL       |       NULL | NOAGENT     | 1621596857.25266 |            |              1 |            1 |
|       55574 | 2013387 |     999 | NANQUE_1    | 2021-05-21 07:34:16 |  1621596856 | 1621596856 |             1 | NANQUE | 1          | 3125551212   | VDCL | NO AGENT DROP | NULL      |          1.00 | NULL       |       NULL | NOAGENT     | 1621596854.25264 |            |              1 |            1 |
+-------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+---------------+-----------+---------------+------------+------------+-------------+------------------+------------+----------------+--------------+
3 rows in set (0.00 sec)

MariaDB [asterisk]> select * from vicidial_log_extended order by call_date desc limit 3;
+------------------+---------------+---------------------+---------+----------------------+----------------+---------------------+---------------------+---------------------+--------------------+
| uniqueid         | server_ip     | call_date           | lead_id | caller_code          | custom_call_id | start_url_processed | dispo_url_processed | multi_alt_processed | noanswer_processed |
+------------------+---------------+---------------------+---------+----------------------+----------------+---------------------+---------------------+---------------------+--------------------+
| 1621596859.25268 | 192.168.198.5 | 2021-05-21 07:34:19 | 2013387 | Y5210734190002013387 |                | Y                   | Y                   | N                   | N                  |
| 1621596857.25266 | 192.168.198.5 | 2021-05-21 07:34:18 | 2013387 | Y5210734180002013387 |                | N                   | XU                  | N                   | N                  |
| 1621596854.25264 | 192.168.198.5 | 2021-05-21 07:34:16 | 2013387 | Y5210734160002013387 |                | N                   | XU                  | N                   | N                  |
+------------------+---------------+---------------------+---------+----------------------+----------------+---------------------+---------------------+---------------------+--------------------+
3 rows in set (0.00 sec)


[developerdan]

Hi Matt,
Thanks for the response, really appreciate you taking the time.

Unfortunately the leads are businesses in this case and there are times when the same "lead" is calling this in at the same time.
(sorry I cant paste as nicely as you!)
Here is an example of what I mean :
This is 2 calls that came in about 5 mins apart, if I joined back to the vicidial_did_log table it would be pickup both of these (in actuality there are possibly hundreds of calls from this number in a day)

Code: Select all

|| *closecallid* || *lead_id* || *list_id* || *campaign_id* || *call_date* || *start_epoch* || *end_epoch* || *length_in_sec* || *status* || *phone_code* || *phone_number* || *user* || *comments* || *processed* || *queue_seconds* || *user_group* || *xfercallid* || *term_reason* || *uniqueid* || *agent_only* || *queue_position* || *called_count* ||
|| 3319344 || 1763 || 999 || group1|| 2021-05-19 17:26:44 || 1621441604 || 1621441604 || 1 || NANQUE || 44 || 000000000 || VDCL || NO AGENT DROP ||  || 0.00 ||  ||  || NOAGENT || 1621441604.32513 ||  || 1 || 5 ||
|| 3319345 || 1763 || 999 || group2|| 2021-05-19 17:26:46 || 1621441606 || 1621441606 || 1 || NANQUE || 44 || 000000000 || VDCL || NO AGENT DROP ||  || 0.00 ||  ||  || NOAGENT || 1621441606.32515 ||  || 1 || 5 ||
|| 3319346 || 1763 || 999 || group3|| 2021-05-19 17:26:47 || 1621441607 || 1621441644 || 37 || OWB || 44 || 000000000 || agent1|| AUTO || N || 0.00 || agents || 294860 || AGENT || 1621441607.32517 ||  || 1 || 5 ||
|| 3319358 || 1763 || 999 || group1|| 2021-05-19 17:31:14 || 1621441874 || 1621441874 || 1 || NANQUE || 44 || 000000000 || VDCL || NO AGENT DROP ||  || 0.00 ||  ||  || NOAGENT || 1621441873.32597 ||  || 1 || 5 ||
|| 3319359 || 1763 || 999 || group2|| 2021-05-19 17:31:15 || 1621441875 || 1621441875 || 1 || NANQUE || 44 || 000000000 || VDCL || NO AGENT DROP ||  || 0.00 ||  ||  || NOAGENT || 1621441875.32599 ||  || 1 || 5 ||
|| 3319360 || 1763 || 999 || group3|| 2021-05-19 17:31:17 || 1621441877 || 1621441919 || 42 || OWB || 44 || 000000000 || agent2 || AUTO || N || 0.00 || agents || 294865 || AGENT || 1621441876.32601 ||  || 1 || 5 ||

[developerdan]

Hi Matt,
I just had an idea, pretty sure this works.
The vicidial_log_extended has the caller_code in it.
That has an entry for each close_log entry AND that will connect back to the call_log (or did_log) which contains the original uniqueid and channel information that I need!

As a bonus, im pretty sure thats do-able in one query too.

Thanks for all the help.
Regards,
Dan

[mflorell]

So all calls come into the system with the same CIDnumber?

If so, just set the DID In-Group Handle Method to "CID", then all subsequent Handle Methods to "CLOSER", this way you will create a new lead_id for every call, and that lead_id will follow the call through the NANQUEs until it makes it to an agent. Then you use the lead_id to find the oldest vicidial_closer_log entry, and the uniqueid from that entry will match to only one vicidial_did_log entry.