Relate transferred call with inbound call for reporting

[josecapurro]

Hello there,

I'm writing a custom report and i need to show to which number was a inbound call transferred.

So, i see i have vicidial_log with the detail of all calls, and vicidial_xfer_log with only the transferred ones.

I can't see how to JOIN to produce the result i want.

vicidial_xfer_log doesn't have uniqueid.

Any insights?

[mflorell]

How exactly are these inbound calls being transferred?

[josecapurro]

Those are manual transfers done with the PARK CUSTOMER DIAL button, using the LEAVE 3WAY CALL button.

Some things i see:
- In call_log i can identify the calls by its prefix looking at the extension column. But how can i identify which agent transferred the call?
In the caller_code column i see DCXXXXXXWYYYYYYYYYYW, but i do not know what to do with that.

- In user_call_log i see the transferred calls done by the user by looking at the call_type column. It shows XFER_3WAY.
I have there the user who transferred the call and the campaign, which is fine, but in the number_dialed it only shows what seems the Asterisk channel, in the form Local/XXXXXXXX@default, but i do not have a uniqueid or something to JOIN to another table and retrieve the actual number dialed, like shown in the call_log table on the extension column.

[josecapurro]

More:

If i look up the lead_id of the row in user_call_log, i see the phone number of the incoming call. But i also need to retrieve the number to which the incoming call was transferred, with the length_in_sec and start_time, as shown in call_log.

[williamconley]

Remember that while you refer to it as a "transfer", it's actually a "3-way call" which should be logged in the closer log. Leaving the 3-way call later results in what you call a transfer, but when it starts ... it's a conference (3-way) call initiated by an agent with the power to do so (ie: closer). We actually had to write a Real Time screen version to watch these calls for a client who had a lot of them (since these disappear from the real time screen when the agent leaves the 3-way).

[mflorell]

The 'user_call_log' table is where the 3-way call log data is stored, it will show you the dial string used to place that call as well as the lead_id of the customer call.

[josecapurro]

Final one: and how can i retrieve the duration of the 3WAY call?

[mflorell]

That depends on how that call is being placed. If you are using a dialplan path that goes through the NVA recording agi script there are several options for how it will log the calls.

[geilt]

I am having a HELL of a time trying to figure out how to connect the user_call_log to the ACTUAL call that was made, since there is NO uniqueid on the user_call_log so I can actually find the DURATION of the call that took place.

Do you have any info on how I can connect the call to the actual call_log, or relate it back to the conference / call that took place where the transfer took place?

Please?

[williamconley]

No time to check, but I've bumped into this scenario before in both this and other software.

This scenario: Primary call record covers a period of time, other calls *may* occur during that call and are related, but since they begin and end at random times (based on agent activity) there is no "unique ID" or "matching start/end time". Thus you are left with HOPING that both records are related to yet a 3rd record in another table. For instance, is there an Agent Log that will have a link (directly OR indirectly) to the original call_log and also to the transfer call and/or recording.

Essentially, you are going to have to check the spider web of linked events and records and see if they will pull in the one you're trying to track. If you're lucky. If that works, you can use a JOIN in SQL to directly link the records and you're good. If not ...

The next phase is to build a routine that will use code to return records from the related tables "during this call". Some will create a link table to store the results and run the routine hourly to create an easier-to-use SQL link for later reporting.

Had similar issues in FreePBX. Their solution was imperfect, but pretty dang good.

[mflorell]

If properly configured, the NVA recording script can log the lead_id on those 3way calls. That and the call_date within the range of the original call are usually enough to link the events together.

[williamconley]

If properly configured, the NVA recording script can log the lead_id on those 3way calls. That and the call_date within the range of the original call are usually enough to link the events together.

That identifies a table with the necessary information ... and "within the range of the original call" is the falling down point for a SQL join statement to work. Thus the routine to create links in a sep table for a later simple sql join.