Recording Association with Call Log

[Acidshock]

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

[williamconley]

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.

[Acidshock]

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!

[williamconley]

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.

[DomeDan]

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

[mflorell]

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.

[Rudolfmdlt]

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

[muratyilmaz.dev]

@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?

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.

[martinch]

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.