Tracing Nanqueue calls in database logs

Discussions about development of VICIDIAL and astGUIclient

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Tracing Nanqueue calls in database logs

Postby developerdan » Fri May 21, 2021 3:34 am

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
 
Posts: 33
Joined: Thu Apr 18, 2013 3:53 am

Re: Tracing Nanqueue calls in database logs

Postby developerdan » Fri May 21, 2021 6:20 am

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.
developerdan
 
Posts: 33
Joined: Thu Apr 18, 2013 3:53 am

Re: Tracing Nanqueue calls in database logs

Postby mflorell » Fri May 21, 2021 6:40 am

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)
mflorell
Site Admin
 
Posts: 17804
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Tracing Nanqueue calls in database logs

Postby developerdan » Fri May 21, 2021 7:06 am

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
 
Posts: 33
Joined: Thu Apr 18, 2013 3:53 am

Re: Tracing Nanqueue calls in database logs

Postby developerdan » Fri May 21, 2021 7:10 am

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
developerdan
 
Posts: 33
Joined: Thu Apr 18, 2013 3:53 am

Re: Tracing Nanqueue calls in database logs

Postby mflorell » Fri May 21, 2021 7:11 am

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.
mflorell
Site Admin
 
Posts: 17804
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida


Return to Development

Who is online

Users browsing this forum: Google [Bot] and 6 guests