Outbound calls, insert channel uniqueid on log tables

[basha04]

Hello,

On an outbound dialplan I have the following line (simplified for demostration ). The UNIQUEID is the asterisk channel uniqueid.
The issue is that the uniqueid on the viocidial_log, call_log and recording_log(vicidial_id column) are not the same, usually it is a +2 uniqueid on the tables.
For example if the asterisk channel uniqueid = 1730707949.117281 on the table it is 1730707949.117283 .

exten => _741100XX!,n,AGI(myScript.php,${UNIQUEID})

Is it possible to have the same uniqueid on both tables and the channel ?


Specs:
Vicibox : v.10.0.1 220503
VERSION : 2.14-906a
BUILD : 240214-2120
SVN : 3804

[williamconley]

The issue is that the uniqueid on the viocidial_log, call_log and recording_log(vicidial_id column) are not the same

I presume that's an issue because you are trying to join those tables in a meaningful way for a report of some sort. But your problem is that these logs generate data from different sources, which is they those logs are different logs. The uniqueID for the recording is a different channel than the conversation, the agent's channel is different than the lead's channel. These are used to identify each LEG of the call.

Vicidial has log IDs that are placed in the other logs for identification and cross-table linking. These are identifiable in the pages that load those tables for viewing/reporting.

Please also note that "uniqueid" ... is NOT a unique field because the values are (rarely, but technically) duplicated.

[basha04]

I presume that's an issue because you are trying to join those tables in a meaningful way for a report of some sort.

Correct. I have build some api to send data gathered from inbound/outbound calls . Joining with uniqueid column seems the most straightforward way and would be better in terms of performance even if I am using a slave for this.

Please also note that "uniqueid" ... is NOT a unique field because the values are (rarely, but technically) duplicated.

In the call log table uniqueid is a primary key not possible to be duplicated, on vicidial_log there is no uniqueid twice , at least on my system which is up and running for years. There are duplicates on recording_log table, but it doesn't matter as long as it contains the same recording file.

Vicidial uses lead_id for table connection , at least this is the common field on the reports generated by admin_modify_lead.php.
If multiple calls for the same number I have to use the date to distinguish the call.

At the moment, since my script is executed on h extension I am retrieving the last record from above tables for the phone_number.
With this method the only issue is if 1234567789 number is in call and another agent manually tries to call 1234567789 when the first call will hang up will retrieve the data from the second call, because I am using order by insert_record_dt which is a column which I have added to the tables

Code: Select all

`insert_record_dt` timestamp NOT NULL DEFAULT current_timestamp()

[basha04]

I am posting what I ended up doing in case somebody finds it useful.

I added startTime variable on the beginning of dialplan for the outbound calls.

Code: Select all

exten => _741100XX!,1,Set(startTime=${STRFTIME(${EPOCH},,%Y-%m-%d %H:%M:%S)})

I added number_dialed_call_log variable which will be used for the call_log table

Code: Select all

exten => _741100XX!,n,Set(number_dialed_call_log=${EXTEN})

I am using below queries

vicidial_log , where the ? on the phone_number is the dialed number without the extension prefix (for example if the extension is 7411000011624 the dialedNumber 1000011624 ) and the ? on insert_record_dt is the startTime variable from the channel

Code: Select all

SELECT uniqueid, lead_id, campaign_id, call_date, length_in_sec, status, phone_number, user, user_group, term_reason
FROM vicidial_log
WHERE phone_number = ?
AND insert_record_dt >= ?
ORDER BY insert_record_dt ASC LIMIT 1

Code: Select all

`insert_record_dt` timestamp NOT NULL DEFAULT current_timestamp(),
`KEY `phone_insdt` (`phone_number`,`insert_record_dt`)

call_log , where the ? on the number_dialed is the number_dialed_call_log variable from the channel and the ? on insert_record_dt is the startTime variable from the channel

Code: Select all

SELECT uniqueid, extension, number_dialed, caller_code, start_time, end_time, length_in_sec, start_epoch
FROM call_log
WHERE number_dialed = ?
AND insert_record_dt >= ?
ORDER BY insert_record_dt ASC LIMIT 1

Index used

Code: Select all

KEY `nr_ins_dt` (`number_dialed`,`insert_record_dt`)

recording_log, where the ? on the filename is the dialed number without the extension prefix (for example if the extension is 7411000011624 the dialedNumber 1000011624 ) and the ? on insert_record_dt is the startTime variable from the channel

Code: Select all

SELECT recording_id, start_time, end_time, length_in_sec, filename, location, lead_id, user, vicidial_id
FROM recording_log
WHERE filename LIKE ?
AND insert_record_dt >= ?
ORDER BY recording_id ASC LIMIT 1

Index used

Code: Select all

KEY `fnam_ins_dt` (`filename`,`insert_record_dt`)

After this data is gathered I have a main table saving all above records , where the recording url is automatically updated by triggers

Code: Select all

                 id: 2689993
           uniqueid: 1730804281.122218
        vl_uniqueid: 1730804282.122220
         vl_lead_id: 3056852
     vl_campaign_id: PI_ITA
       vl_call_date: 2024-11-05 11:58:02
   vl_length_in_sec: 87
          vl_status: A
    vl_phone_number: 1000011624
            vl_user: eb63778
      vl_user_group: ADMIN
     vl_term_reason: AGENT
    vcl_closecallid: NULL
        vcl_lead_id: NULL
    vcl_campaign_id: NULL
      vcl_call_date: NULL
  vcl_length_in_sec: NULL
         vcl_status: NULL
   vcl_phone_number: NULL
           vcl_user: NULL
     vcl_user_group: NULL
    vcl_term_reason: NONE
       vcl_uniqueid: NULL
        cl_uniqueid: 1730804282.122220
       cl_extension: 7411000011624
   cl_number_dialed: 7411000011624
     cl_caller_code: M1051158010003056852
      cl_start_time: 2024-11-05 11:58:01
        cl_end_time: 2024-11-05 11:59:29
   cl_length_in_sec: 88
     cl_start_epoch: 1730804281
    rl_recording_id: 3764355
      rl_start_time: 2024-11-05 11:58:02
        rl_end_time: 2024-11-05 11:59:29
   rl_length_in_sec: 87
        rl_filename: 1000011624_20241105-115801_eb63778
        rl_location: http://xxxxxxxxxxxx/recordings/2024-11-05/1000011624_20241105-115801_eb63778-all.gsm
         rl_lead_id: 3056852
            rl_user: eb63778
     rl_vicidial_id: NULL
                url: NULL
         tipo_chiam: OutboundVicidial
          call_type: Outbound
         callToSend: OutboundCall
   insert_record_dt: 2024-11-05 14:12:42
          updatedAt: 2024-11-05 14:12:42
              stato: IT
          call_note: Telefonate Outbound Vicidial
  url_response_code: 0
  url_response_text: NULL
postMessageResponse: NULL
       postSentData: {"length_in_sec":88,"parlato":"87","number_dialed":"1000011624","caller_code":"0282396016","uniqueid":"1730804281.122218gesti","start_time":"2024-11-05 11:58:01","status":"A","dialStatus":"ANSWER","campaign_id":"PI_ITA","user_group":"ADMIN","start_epoch":1730804281,"filename":"1000011624_20241105-115801_eb63778","risposta_persa":"Risposta","tipo_chiam":"OutboundVicidial","username":"eb63778","script":"data_out_vicidial.php","stato":"IT","recording_id":3764355,"callType":"call"}
            is_sent: 0


[williamconley]

In the call log table uniqueid is a primary key not possible to be duplicated, on vicidial_log there is no uniqueid twice , at least on my system which is up and running for years. There are duplicates on recording_log table, but it doesn't matter as long as it contains the same recording file.

Beware: Primary key means there can not be two records in that table. That does not make the value a unique value in the rest of the system (asterisk, other tables). In that case "first in wins" and the other is discarded. Relying on asterisk's "uniquid" for linking is usually ok as long as you don't expect it to be a unique value anywhere else (especially in a cluster, which can definitely have a collision). But I still don't recommend it. Major/enterprise systems should definitly avoid this field for linking.