Recording Association with Call Log

Discussions about development of VICIDIAL and astGUIclient

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

Recording Association with Call Log

Postby Acidshock » Wed Feb 27, 2013 1:32 pm

Hi Guys,

I am hoping I am wrong on this one. Is there any way to programatically associate a call recording with the actual log entry of the call that took place? I have been looking at vicidial_log and recording_log... The recording log does not store the uniqueid. I am assuming this would be an easy thing to fix and I can probably pull off a patch for this feature. It just seems silly so I am wondering if there may be a much larger reason for this or maybe its already doable and I am just looking in the wrong places. Thanks!!

VERSION: 2.6-389a
BUILD: 121206-0630
Asterisk:1.4.39.2-vici
Redux 3.1.15
VERSION: 2.14-698a | BUILD: 190207-2301 | Asterisk:13.24.1-vici | Vicibox 8.1.2
Acidshock
 
Posts: 428
Joined: Wed Mar 03, 2010 3:19 pm

Re: Recording Association with Call Log

Postby williamconley » Wed Feb 27, 2013 2:12 pm

If you were to list your installation method, the answer to this sort of question would be more tailored to your needs. For the Time Being I will presume you have a valid installation with something like Vicibox 4.0.3 (the present .iso from Vicibox.com).

The information for a recorded call is linked to the lead and the user. If you use the "Search for a Lead" link under "lists" you can locate the lead and see any/all recordings associated with that lead. The same concept applies to the users, they have all their recorded calls listed at the bottom of their records as well. The fact that they appear in those two places, properly associated with the lead and user, means they are being tracked properly and stored properly.

Beware the UniqueID field ... it is not (LOL) actually Unique. Do not rely on it being unique in all situations. Especially in multi-server scenarios. Note that the field is NOT defined as the Primary Index.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: Recording Association with Call Log

Postby Acidshock » Wed Feb 27, 2013 2:53 pm

Redux 3.1.15 = Vicibox Redux 3.1.15

What is a problem that I see is that the information can be presented in a manner that allows a person to imperically determine what recording belongs to what call but it doesnt allow for you to programatically pull the call log from the database and say that this call log entry belongs to this recording and vice versa. Is it at all possible to link a call log entry to a recording without trying to guess it by time, date, user, and leadid.

I will give an example. I want to retroactively pull all recordings that were dispositioned with Status X. However the disposition of a lead can change throughout time. So if it were dispositioned CallBack, then dispositioned Answering Machine, then dispositioned Sold. How is it possible to programatically pull the call recording location for the call that ended with a CallBack disposition?

Thanks!
VERSION: 2.14-698a | BUILD: 190207-2301 | Asterisk:13.24.1-vici | Vicibox 8.1.2
Acidshock
 
Posts: 428
Joined: Wed Mar 03, 2010 3:19 pm

Re: Recording Association with Call Log

Postby williamconley » Wed Feb 27, 2013 3:31 pm

Interesting. Most facilities will only ship the recordings of leads that are Still Sale at the end of the day. Alternately, they would prefer to download ALL recordings for any lead which was Ever a SALE (ie: not only downloading the Sale recording, but also the not interested that preceded it and the cancel that came after ...). Have you verified the "vicidial_id" field to see if this is your "uniqueid" data?

You can modify the filename of the recordings with several available variables. If that helps. It would be interesting to modify the recording system to allow uniqueid to begin the filename.

I would suspect that your best bet would be to modify your system to include the uniqueID field during the creation of the recording log, if that is not already in the "vicidialID" field, which I believe it is. But be careful that in the future you will find that the uniqueid field is not actually unique. Do not rely solely on that field for a 1:1 match up (or be prepared to have two records with the same ID eventually and "deal with it").

alternately, you could create a script or modify an existing one to create a relation table to allow you to link recordings with calls (bearing in mind that one "call" may have several "recordings", so this is not a 1:1 relationship, so it would not be wise to merely add a new field to the call_log table. Additionally, altering the DB structure breaks upgrade, whereas creating a new table and a new script to populate it will often survive upgrade with no problems.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: Recording Association with Call Log

Postby DomeDan » Thu Feb 28, 2013 4:17 am

I have been joining uniqueid with vicidial_id in my custom scripts and i haven't had much problem
but I use ALLFORCE so I have not tested the scenario with multiple recordings in one call.

I wrote a little query to you, because I myself like learning from examples:
Code: Select all
echo "SELECT location FROM recording_log r \
LEFT JOIN vicidial_log l ON l.uniqueid=r.vicidial_id \
WHERE r.user = '6666' \
AND r.start_time > '2013-02-28 09:00' \
AND r.end_time < '2013-02-28 21:00' \
AND l.status = 'SALE'" | mysql -ucustom -p -D asterisk | xargs wget

edit it and put in on a linux cli and it will download the specific recordings with wget, in this case SALE made by user 6666 between a time range

one thing I had to stop and think a while for was that the location will by NULL before the mix-script (or which one it was) updates the record with the correct location
Vicidial Partner. Region: Sweden/Norway.
Does Vicidial installation, configuration, customization, add-ons, CRM implementation, support, upgrading, network-related, pentesting etc. Remote and onsite assistance.
Email: domedan (at) gmail.com
DomeDan
 
Posts: 1226
Joined: Tue Jan 04, 2011 9:17 am
Location: Sweden

Re: Recording Association with Call Log

Postby mflorell » Thu Feb 28, 2013 7:09 am

The recording_log.vicidial_id should match up to either the vicidial_log.uniqueid or the vicidial_closer_log.closecallid, depending on whether it is an outbound call or an inbound call.

The closecallid is an auto-increment integer with no period "." in it, so it is easy to differentiate from the two.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Recording Association with Call Log

Postby Rudolfmdlt » Tue May 27, 2014 2:04 pm

Hi DomeDan,

Your code snippets are always useful, thanks! (My SQL is not that great!) In one of my call centres we don't care who the agent is, we just need to backup all the sales for a customer at the end of the month or campaign.

Code: Select all
echo " SELECT location FROM recording_log r LEFT JOIN vicidial_log l ON l.uniqueid=r.vicidial_id WHERE l.campaign_id = '1002' AND l.status = 'SALE' AND r.start_time > '2013-02-01 01:00'
AND r.end_time < '2014-05-31 23:59' " | mysql -ucustom -p -D asterisk | xargs wget


Thought I'd leave that here for people who want all recordings associated with a specific status is a specific campaign.

Thanks again for your help people!

Rudolf
Rudolfmdlt
 
Posts: 92
Joined: Thu Jul 25, 2013 2:07 am

Re: Recording Association with Call Log

Postby muratyilmaz.dev » Fri Oct 21, 2022 5:13 am

@mflorell

For the first time in 3 years, this is the first complaint from my customer.

Audio recordings were not appearing on my custom report page.

I have a code like this;

SELECT GROUP_CONCAT(rl.location SEPARATOR '<br>')
FROM recording_log AS rl
WHERE vl.uniqueid = rl.vicidial_id
)
AS new_location,


When I looked, I noticed something:

vicidial_log.uniqued = 1666170510.365384
call_log.uniqued = 1666170510.365384
recording_log.vicidial_id = 1666170510.365382

There is no match because the last digits are different.

Do you have a comment?


mflorell wrote:The recording_log.vicidial_id should match up to either the vicidial_log.uniqueid or the vicidial_closer_log.closecallid, depending on whether it is an outbound call or an inbound call.

The closecallid is an auto-increment integer with no period "." in it, so it is easy to differentiate from the two.
Murat Yılmaz / Software Developer - agola.net - Turkey
ViciBox v.9.0.3 | Version: 2.14b0.5 | SVN Version: 3346 | DB Schema Version: 1615 | Build: 200630-2117 | Cloud-Cluster
muratyilmaz.dev
 
Posts: 17
Joined: Sun Feb 16, 2020 4:24 am
Location: Turkey

Re: Recording Association with Call Log

Postby martinch » Wed Apr 19, 2023 10:50 am

Just for reference;

- If the vicidial_id is a floating point number, the lead was an Outbound auto / manual call. You can tell from the period / full stop present in the value. This value is generated by Asterisk.

- If the vicidial_id is an integer, the lead was an Inbound call as there is no period / full stop present. This value is generated by MariaDB.

This query would find outbound calls;

Code: Select all
SELECT filename FROM recording_log as rl
JOIN vicidial_log as vl
ON rl.vicidial_id = vcl.uniqueid;


This query would find inbound calls;

Code: Select all
SELECT filename FROM recording_log as rl
JOIN vicidial_closer_log as vcl
ON rl.vicidial_id = vcl.closecallid;


You can also do a combination of the two using a case statement to indicate call direction (outbound or inbound).

Hope this helps.
Project Lead @ mDial -> https://github.com/TheBlode/mDial
martinch
 
Posts: 273
Joined: Thu Nov 15, 2018 9:14 am
Location: England, UK


Return to Development

Who is online

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