Carriers

Which carrier carried that call? call_log vs carrier_log

In VICIdial the call_log table names the carrier that carried a call. vicidial_carrier_log credits everything to Local. Queries and traps, inbound and outbound.

By Published 5 min read
From below of long thin identical blue cables connected to small round electrical connectors

To the point

To find which carrier carried a call, read call_log. The table named after carriers, vicidial_carrier_log, holds the Local leg in its channel column, so grouping by it credits every call to "Local".

  • call_log gets a row from every carrier entry in the dialplan, however the carrier was set up.
  • Outbound: join call_log to vicidial_log on uniqueid. Inbound: vicidial_did_log.
  • vicidial_carrier_log is usually empty, because it needs SIP event logging on.
  • Measured on one ViciBox 12 database: 1.8 million call_log rows, 1.1 million carrier log rows.

Why call_log knows

Every carrier entry in the dialplan calls the call_log AGI as its first step:

exten => _91999NXXXXXX,1,AGI(agi://127.0.0.1:4577/call_log)

A carrier that dials at all writes a row there. It does not matter if the carrier lives in vicidial_server_carriers, was written by hand into sip.conf, or comes from a global in a conf file. It does not depend on sip_event_logging, which the other table does. The channel on that row is the carrier.

A channel reads TECH/peer-hexid. Technology is the part before the slash and the peer sits between the slash and the first dash. For PJSIP it looks like PJSIP/number@endpoint-hexid, so take what follows the last @, then split on the dash.

Why the other table is usually empty

Screens are from our test server. A numbered marker on the picture maps to the list under it.

VICIdial system settings with SIP Event Logging highlighted
Admin, System Settings: the switch that fills vicidial_carrier_log
  1. SIP Event Logging: when off, vicidial_carrier_log stays empty. call_log does not care.

Outbound

Join call_log to vicidial_log on uniqueid, drop inbound and Local rows, and group by the peer:

SELECT vl.campaign_id, c.dial_prefix,
       SUBSTRING_INDEX(SUBSTRING_INDEX(cl.channel,'-',1),'/',-1) AS peer,
       SUBSTRING_INDEX(cl.channel,'/',1) AS tech,
       COUNT(*) AS calls
FROM call_log cl
JOIN vicidial_log vl ON vl.uniqueid = cl.uniqueid
LEFT JOIN vicidial_campaigns c ON c.campaign_id = vl.campaign_id
WHERE cl.channel_group <> 'DID_INBOUND' AND cl.type <> 'Local'
  AND cl.start_time >= NOW() - INTERVAL 30 DAY
GROUP BY vl.campaign_id, c.dial_prefix, peer, tech;
Flow: call_log row, join to vicidial_log on uniqueid, drop inbound and Local rows, group by the peer
The outbound query in four steps

On a month of data this answered what the dialplan cannot. One campaign, one dial prefix, four carriers behind it, and the log showed how many calls each carried. The dialplan says what may happen. The log says what did. To learn how often a failover carrier is used, run this.

The dialed prefix comes from vicidial_campaigns.dial_prefix. A prefix of 99 plus 00 and a number gives a Local channel like Local/9900<number>@default. That is the Local leg again, so the carrier is not there.

Inbound

Inbound calls are in vicidial_did_log. Join vicidial_inbound_dids to get the DID and in-group at once:

SELECT d.did_pattern, d.group_id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(dl.channel,'-',1),'/',-1) AS peer,
       COUNT(*) AS calls
FROM vicidial_did_log dl
LEFT JOIN vicidial_inbound_dids d ON d.did_id = dl.did_id
WHERE dl.call_date >= NOW() - INTERVAL 30 DAY
GROUP BY d.did_pattern, d.group_id, peer;

On our data that listed two inbound carriers and which numbers each delivered. The same rows live in call_log with channel_group = 'DID_INBOUND'.

Where a carrier is defined

PlaceGives youMisses
vicidial_server_carriersdialplan entry, peer block, protocol, registration_string, activehand-written carriers, which one carried a call
extensions-vicidial.confthe generated dialplanthe same; edits will be lost
sip-vicidial.conf, pjsip-vicidial.confgenerated peer blockshand-written trunks
sip.conf, extensions.conf, custom includeshand-written carrierseverything else
vicidial_peer_event_logREACHABLE or UNREACHABLE over timecalls
sip show registry, pjsip show endpointswhat is registered nowhistory

Where configuration and the log disagree, the log is right. It records what happened.

Traps

Bars: 1.8 million call_log rows, 1.1 million vicidial_carrier_log rows, 168 thousand vicidial_did_log rows
Row counts in the database we measured
  • vicidial_carrier_log.channel is the Local leg. It is still good for the dialed string and dialstatus.
  • The two legs do not share a uniqueid. Join each leg to vicidial_log or vicidial_closer_log instead.
  • did_carrier_description is a note field. Zero of 15 rows had a value.
  • There is no vicidial_carriers table. It is vicidial_server_carriers.
  • vicidial_did_gateway_log holds nothing. Zero rows. vicidial_did_log has the data.
  • Agent phones appear as peers. SIP/1013 with rows only in internal calls is a handset.

Reading the result

Say the outbound query returns four rows for one campaign: a main carrier, a second with a few thousand calls, and two with a few hundred. Is the second your failover on purpose? Are the small two in your contract, or a leftover trunk? Add HOUR(cl.start_time) to the grouping to see a carrier that appears only at the busiest hours, which is what a capacity limit on the first carrier looks like.

Call counts are not quality or minutes. For quality use dialstatus outcomes or the answered share of vicidial_log statuses per peer. For billing by the minute, sum length_in_sec.

Questions people ask

Why does my carrier report show only Local?

It reads vicidial_carrier_log.channel, which holds the Local leg. Read call_log instead.

Do I need SIP event logging for this?

No. call_log is written by the dialplan AGI. Only vicidial_carrier_log needs the setting.

Next steps

Run the outbound query for 30 days, look for a peer you did not expect and a carrier you pay for with no rows, and repeat monthly next to your invoices. For caller ID see caller ID groups, and for trunks PJSIP carrier setup. On hosted VICIdial we can help you read your carrier mix, so contact us.

VICIdial is a trademark of its owner. Trustane is an independent hosting provider.

Sources

  1. Trustane carrier attribution findings, measured on a real database
  2. Trustane notes on the VICIdial 2.14 manager manual
  3. Cover photo by Brett Sayles on Pexels

Trustane

Managed VICIdial hosting

Run it on cores of your own

Managed VICIdial, billed by the hour against a balance you top up.

Or try one server free for 7 days, no top-up.