ConfigManual
Asterisk

Asterisk CDR with MySQL: Storing and Querying Call Records Properly

Himanshu Pal

Himanshu Pal

What a CDR records

A Call Detail Record is the row Asterisk writes when a call ends. It is the raw material for billing, agent reporting, dispute resolution with carriers, and any question beginning "how many calls…". Getting the storage right early matters, because CDRs are one of the few things you cannot reconstruct after the fact.

The standard fields are consistent across storage backends:

  • clid — the caller ID as presented
  • src — the source (calling number)
  • dst — the destination (dialled number)
  • dcontext — the destination context in the dialplan
  • channel — the channel name
  • duration — total duration of the call
  • billsec — duration after the call was answered
  • disposition — the outcome: ANSWERED, NO ANSWER, BUSY
  • amaflags — the billing flag: DOCUMENTATION, BILL, IGNORE
  • accountcode — the channel's account code
  • uniqueid — the channel's unique identifier

duration vs billsec — the field people get wrong

These two are the source of most billing errors. duration covers the whole call including ringing; billsec counts only from the moment it was answered. Billing should almost always use billsec — charging on duration means charging customers for ring time, which is both wrong and noticeable.

An unanswered call has a non-zero duration and a billsec of zero. That is a reliable way to separate real conversations from attempts.

What you can and cannot change

This trips people up when they try to enrich records from the dialplan. CDR fields are accessible through the ${CDR(fieldname)} function, but all field names are read-only except accountcode, userfield and amaflags.

There is also a timing constraint: CDRs may only be modified before the bridge between two channels is torn down. In practice that means you cannot change a CDR after the Dial application has returned. Set what you need on the way in, not on the way out:

exten => _X.,1,Set(CDR(accountcode)=CUST1042)
 same => n,Set(CDR(userfield)=campaign-spring)
 same => n,Dial(PJSIP/trunk/${EXTEN},30)
 same => n,Hangup()

userfield is the general-purpose slot — use it for a campaign ID, ticket reference or anything else your reporting needs to join on.

Storing to MySQL

The modern approach is the ODBC-backed CDR module rather than a database-specific one, because it adapts to whatever columns your table actually has. Configure the connection in /etc/odbc.ini and res_odbc.conf, then point the CDR module at it in cdr_adaptive_odbc.conf:

[asterisk_cdr]
connection = asterisk
table = cdr

The adaptive module writes any CDR field whose name matches a column in your table and silently ignores the rest — so you control the schema by choosing your columns. A workable table:

CREATE TABLE cdr (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  calldate    DATETIME NOT NULL,
  clid        VARCHAR(80),
  src         VARCHAR(80),
  dst         VARCHAR(80),
  dcontext    VARCHAR(80),
  channel     VARCHAR(80),
  duration    INT,
  billsec     INT,
  disposition VARCHAR(45),
  amaflags    VARCHAR(45),
  accountcode VARCHAR(20),
  userfield   VARCHAR(255),
  uniqueid    VARCHAR(32),
  INDEX idx_calldate (calldate),
  INDEX idx_src (src),
  INDEX idx_dst (dst),
  INDEX idx_accountcode (accountcode)
);

Add the indexes from the start. A CDR table grows relentlessly and an unindexed one turns every report into a full table scan within months.

Storing uniqueid is worth insisting on: it is what lets you correlate a CDR with call recordings, log lines and CEL events. Without it, tying a complaint to a specific call becomes guesswork.

Queries that answer real questions

Answered calls per day:

SELECT DATE(calldate) AS day,
       COUNT(*) AS calls,
       SUM(billsec) AS billed_seconds
FROM cdr
WHERE disposition = 'ANSWERED'
GROUP BY DATE(calldate)
ORDER BY day DESC;

Answer-seizure ratio — the proportion of attempts that connected, and a direct measure of route quality:

SELECT dcontext,
       COUNT(*) AS attempts,
       SUM(disposition = 'ANSWERED') AS answered,
       ROUND(100 * SUM(disposition = 'ANSWERED') / COUNT(*), 1) AS asr_pct
FROM cdr
WHERE calldate >= NOW() - INTERVAL 7 DAY
GROUP BY dcontext;

A sharp drop in ASR on one route is one of the earliest signals of a carrier problem — often visible before users start complaining.

Spend by account:

SELECT accountcode,
       COUNT(*) AS calls,
       ROUND(SUM(billsec)/60, 1) AS billed_minutes
FROM cdr
WHERE calldate >= '2026-08-01'
  AND disposition = 'ANSWERED'
GROUP BY accountcode
ORDER BY billed_minutes DESC;

Operational advice

  • Keep the CSV backend enabled as well. It costs almost nothing and gives you a local fallback if the database is unreachable — otherwise a database outage means permanently lost records.
  • Watch for silent write failures. If the ODBC connection drops, records can vanish without an obvious error. Alert on "zero CDRs written in the last hour" rather than trusting it.
  • Archive on a schedule. Move old rows to a history table so the live table stays fast.
  • Consider CEL for detail. CDRs summarise a call; Channel Event Logging records each event within it. If you need to analyse transfers and queue behaviour, CDRs alone will not tell the story.

Frequently asked questions

Should I bill on duration or billsec?

billsec. It counts only answered time, whereas duration includes ringing — billing on duration overcharges every call.

Which CDR fields can I set from the dialplan?

Only accountcode, userfield and amaflags. Everything else is read-only, and changes must be made before the bridge is torn down — that is, before Dial returns.

Why are some calls missing from my CDR table?

Usually a database write failure rather than a missing call. Check the ODBC connection and Asterisk's logs, and keep the CSV backend on as a safety net.

What is uniqueid for?

It identifies the channel, letting you join a CDR to recordings, log entries and CEL records for the same call. Always store it.

asteriskcdrmysqlbillingreporting