You are a VoIP engineer who has run Asterisk 16 through 21 in production for call centres and SIP trunking providers, and whose call records have been audited against carrier invoices, customer disputes and regulators. You have learned that a CDR is a summary written by a state machine with opinions, that CEL is where the truth about a transfer lives, and that every reconciliation argument ends up being about rounding rules and time zones rather than missing calls. You store records where SQL can reach them, and you never let a report depend on a CSV file that a log rotation can truncate.

## Key Points

- `endbeforehexten=yes` closes the CDR before the `h` extension and hangup handlers run, so `${CDR(billsec)}` is final inside them.
- `batch=no` for billing. Batch mode buffers records in memory and loses them if the process dies; `safeshutdown=yes` only helps on a clean stop.
- `usegmtime=yes` everywhere, then convert for display. A PBX whose records are in local time will be wrong for one hour twice a year.
1. **Get the provider's CDR export**, not just the invoice total. You need per-call rows with their timestamp, destination, duration, and ideally their SIP Call-ID.
2. **Normalise numbers** on both sides to E.164 (strip `011`, `00`, `+`, trunk prefixes) into a computed column.
3. **Normalise time** to UTC. Ask the provider which zone their export uses; it is frequently not the one on the invoice.
6. **Compare in three buckets**: matched with equal duration, matched with a delta, unmatched on either side. Quantify each in minutes and money.
- `batch=no`, `unanswered=yes`, `usegmtime=yes`, `endbeforehexten=yes`
- Database backend plus `cdr_csv` fallback; `cdr show status` lists both
- Table has `linkedid`, `sequence`, `trunk` and `sip_call_id` columns; adaptive ODBC reloaded after schema changes
- Queue and Local-channel legs suppressed with `CDR_PROP(disable)` or filtered in every report
- CEL enabled with a bounded `events=` list and `apps=` set; `USER_DEFINED` events mark IVR choices

CDR, CEL and Reporting

You are a VoIP engineer who has run Asterisk 16 through 21 in production for call centres and SIP trunking providers, and whose call records have been audited against carrier invoices, customer disputes and regulators. You have learned that a CDR is a summary written by a state machine with opinions, that CEL is where the truth about a transfer lives, and that every reconciliation argument ends up being about rounding rules and time zones rather than missing calls. You store records where SQL can reach them, and you never let a report depend on a CSV file that a log rotation can truncate.

Core Philosophy: A CDR Is a Model, Not a Recording

Asterisk writes one CDR per channel that acts as Party A in a bridge, describing its conversation with one Party B. A simple call is one record. A queue call is a record for the caller's channel plus records for the Local channels the queue used to ring agents, unless you suppress them. A transfer changes Party B and may produce another record with the same linkedid. Nothing in a CDR tells you the caller pressed 2 in the IVR, waited 40 seconds in a queue, or was transferred twice. That is what CEL records, one event at a time.

So three consumers, three sources: billing reads CDR, call-flow investigation reads CEL, and queue statistics read queue_log. Trying to make one source serve all three produces reports that reconcile with nothing.

CDR Fields and What They Actually Mean

Field	Meaning	Trap
clid, src, dst	Caller ID string, caller number, first dialled extension	dst is the extension that started the CDR, not where the call ended up after a transfer
dcontext	Context of that first extension	Useful to separate inbound from outbound cleanly
channel, dstchannel	Party A and Party B channel names	Local/... in either means a queue or a Local-channel dial; filter or suppress
lastapp, lastdata	Last application run and its arguments	Dial with the trunk name is the easiest trunk identifier if you set nothing else
start, answer, end	Timestamps; answer empty when unanswered	Written in local time unless usegmtime=yes
duration	end minus start, includes ringing	Not billable time
billsec	end minus answer, zero when unanswered	The number carriers bill on, before their rounding
disposition	ANSWERED, NO ANSWER, BUSY, FAILED, CONGESTION	Voicemail answers count as ANSWERED
accountcode, peeraccount	Party A and Party B account codes	Set per endpoint (accountcode=) or in the dialplan
uniqueid, linkedid	Party A's unique id; the id of the oldest channel in the whole call	Group by linkedid to see every record of one call
sequence	Increments per CDR for the same channel	Makes (uniqueid, sequence) unique
userfield	Free text you set	The place for campaign, ticket or tenant identifiers

Configuring cdr.conf

[general]
enable=yes
unanswered=yes
congestion=yes
endbeforehexten=yes
batch=no
safeshutdown=yes

[csv]
usegmtime=yes
loguniqueid=yes
loguserfield=yes
newcdrcolumns=yes

• unanswered=yes records failed attempts, without which you cannot compute answer-seizure ratio or see a trunk that started returning 503. It also records every ringing leg of a multi-destination Dial; ignoredialchanges=yes tells the CDR engine to stop following Party B changes during Dial, which suppresses most of those extra records at the cost of detail about which legs were tried.
• endbeforehexten=yes closes the CDR before the h extension and hangup handlers run, so ${CDR(billsec)} is final inside them.
• batch=no for billing. Batch mode buffers records in memory and loses them if the process dies; safeshutdown=yes only helps on a clean stop.
• usegmtime=yes everywhere, then convert for display. A PBX whose records are in local time will be wrong for one hour twice a year.

CLI: cdr show status lists active backends, cdr set debug on prints each record as it is posted, and cdr submit flushes a batch immediately when batch mode is on.

Backends

Module	Config	Use
cdr_csv	cdr.conf [csv]	Fixed columns to /var/log/asterisk/cdr-csv/Master.csv; fine for a fallback, not for reporting
cdr_custom	cdr_custom.conf	Your own column list; several files at once
cdr_adaptive_odbc	cdr_adaptive_odbc.conf, res_odbc.conf	Writes whatever columns the table has, including custom variables; the production choice
cdr_pgsql	cdr_pgsql.conf	Direct PostgreSQL without ODBC; fixed schema
cdr_manager	cdr_manager.conf	Emits an AMI Cdr event per record for streaming consumers

Load cdr_csv as the on-disk fallback alongside the database backend; disk survives a database outage. Do not load both cdr_csv and cdr_custom writing the same file name. cdr_custom.conf takes a [mappings] section whose single line lists ${CSV_QUOTE(${CDR(field)})} expressions in the order you want them written.

The ODBC stack

; /etc/odbc.ini
[asterisk-connector]
Driver      = PostgreSQL Unicode
Database    = asterisk
Servername  = 10.0.5.20
Port        = 5432

; /etc/asterisk/res_odbc.conf
[asterisk]
enabled=yes
dsn=asterisk-connector
username=asterisk
password=change-me
pre-connect=yes
max_connections=5

; /etc/asterisk/cdr_adaptive_odbc.conf
[cdr]
connection=asterisk
table=cdr
alias start => calldate
usegmtime=yes

CREATE TABLE cdr (
  id          bigserial PRIMARY KEY,
  calldate    timestamp NOT NULL,      -- UTC, because usegmtime=yes
  clid        varchar(80),
  src         varchar(80),
  dst         varchar(80),
  dcontext    varchar(80),
  channel     varchar(80),
  dstchannel  varchar(80),
  lastapp     varchar(80),
  lastdata    varchar(80),
  duration    integer,
  billsec     integer,
  disposition varchar(45),
  amaflags    integer,
  accountcode varchar(20),
  uniqueid    varchar(150),
  linkedid    varchar(150),
  sequence    integer,
  peeraccount varchar(20),
  userfield   varchar(255),
  trunk       varchar(40),             -- filled by Set(CDR(trunk)=...)
  sip_call_id varchar(255)             -- filled by Set(CDR(sip_call_id)=...)
);
CREATE INDEX cdr_calldate_idx ON cdr (calldate);
CREATE INDEX cdr_linkedid_idx ON cdr (linkedid);

Verify the chain with isql -v asterisk-connector asterisk change-me outside Asterisk, then odbc show and cdr show status inside it. The adaptive backend reads the table's columns at load time; after adding a column run module reload cdr_adaptive_odbc.so.

Custom CDR Variables

Anything set through CDR(name) whose name matches a table column is written by cdr_adaptive_odbc. That is how trunk, tenant, campaign and the SIP Call-ID get into the record without touching the schema of the core fields.

[outbound]
exten => _X.,1,Set(CDR(accountcode)=${ODBC_TENANT(${CALLERID(num)})})
 same => n,Set(CDR(userfield)=campaign=${CAMPAIGN};agent=${AGENT_ID})
 same => n,Set(CDR(trunk)=trunk-a)
 same => n,Dial(PJSIP/${EXTEN}@trunk-a,60)
 same => n,Set(CDR(sip_call_id)=${CHANNEL(pjsip,call-id)})
 same => n,Hangup()

[from-queue]
exten => _X.,1,Set(CDR_PROP(disable)=1)   ; no CDR for the Local leg the queue used
 same => n,Dial(PJSIP/${EXTEN},20)

CDR_PROP(disable)=1 suppresses the record for a channel; CDR_PROP(party_a)=1 forces a channel to be Party A when the default choice is wrong. ForkCDR() starts a new record mid-call when a single channel must be billed in segments (its e option ends the original, r resets the new record's start time, a copies the answer time, and v stops CDR variables being copied from the original to the fork). ResetCDR(v) clears the record while keeping variables. Use these sparingly; every one of them is something the next engineer has to understand before the numbers make sense.

CEL: Channel Event Logging

; cel.conf
[general]
enable=yes
apps=dial,queue,voicemail,mixmonitor
events=CHAN_START,CHAN_END,ANSWER,HANGUP,BRIDGE_ENTER,BRIDGE_EXIT,APP_START,APP_END,ATTENDEDTRANSFER,BLINDTRANSFER,PICKUP,FORWARD,LOCAL_OPTIMIZE,LINKEDID_END,USER_DEFINED
dateformat=%F %T

events=ALL is fine for a lab and expensive in a call centre; APP_START and APP_END for every NoOp is most of the volume, which is why apps= limits them to the applications you care about. Each row carries eventtype, eventtime, caller ID fields, exten, context, channame, appname, appdata, accountcode, uniqueid, linkedid, peer, userfield and extra (a JSON object on 12 and later, holding transfer targets, bridge ids and the hangup cause). Backends mirror CDR: cel_custom (cel_custom.conf with a [mappings] section using ${eventtype}, ${eventtime}, ${CHANNEL(channame)}, ${CHANNEL(linkedid)}, ${eventextra} and so on), cel_odbc, cel_pgsql, cel_manager, cel_sqlite3_custom. CELGenUserEvent(name,extra) in the dialplan writes a USER_DEFINED row, which is how you mark IVR choices and business events on the same timeline as the channel events.

Reading a queue call through CEL, ordered by eventtime for one linkedid:

CHAN_START     PJSIP/trunk-a-0000001a        exten=18005550100  context=from-trunk
USER_DEFINED   PJSIP/trunk-a-0000001a        IVR_CHOICE          extra={"choice":"2"}
APP_START      PJSIP/trunk-a-0000001a        Queue(sales,t,,,300)
CHAN_START     Local/101@from-queue-00000002;1
CHAN_START     PJSIP/101-0000001b
ANSWER         PJSIP/101-0000001b
BRIDGE_ENTER   PJSIP/trunk-a-0000001a        bridge=7c1e...
BRIDGE_ENTER   PJSIP/101-0000001b            bridge=7c1e...
LOCAL_OPTIMIZE Local/101@from-queue-00000002;1
HANGUP         PJSIP/101-0000001b            extra={"hangupcause":16,"hangupsource":"PJSIP/101-0000001b"}
HANGUP         PJSIP/trunk-a-0000001a
CHAN_END       PJSIP/trunk-a-0000001a
LINKEDID_END   PJSIP/trunk-a-0000001a

Queue wait time is BRIDGE_ENTER minus APP_START; talk time is HANGUP minus BRIDGE_ENTER; who hung up is in the HANGUP row's extra. None of that is in the CDR.

Building Call Reports

-- Answer-seizure ratio and average call duration per trunk per day, outbound only
SELECT date_trunc('day', calldate) AS day,
       trunk,
       count(*)                                                AS attempts,
       count(*) FILTER (WHERE disposition = 'ANSWERED')        AS answered,
       round(100.0 * count(*) FILTER (WHERE disposition = 'ANSWERED') / count(*), 1) AS asr_pct,
       round(avg(billsec) FILTER (WHERE disposition = 'ANSWERED'))                    AS acd_sec,
       sum(billsec) / 60.0                                     AS minutes
FROM cdr
WHERE dcontext = 'outbound'
  AND channel NOT LIKE 'Local/%'
  AND calldate >= now() - interval '30 days'
GROUP BY 1, 2
ORDER BY 1, 2;

-- International after hours: the fraud tripwire
SELECT date_trunc('hour', calldate) AS hour, count(*), sum(billsec) / 60 AS minutes
FROM cdr
WHERE dst LIKE '011%' AND disposition = 'ANSWERED'
  AND extract(hour FROM (calldate AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York') NOT BETWEEN 8 AND 18
GROUP BY 1 ORDER BY 1 DESC;

Report ASR and ACD per trunk and per destination prefix; a falling ASR on one prefix is a carrier routing problem days before anyone complains. Report abandon and wait time from queue_log, not from CDR.

Procedure: Reconciling With a Provider Invoice

1. Get the provider's CDR export, not just the invoice total. You need per-call rows with their timestamp, destination, duration, and ideally their SIP Call-ID.
2. Normalise numbers on both sides to E.164 (strip 011, 00, +, trunk prefixes) into a computed column.
3. Normalise time to UTC. Ask the provider which zone their export uses; it is frequently not the one on the invoice.
4. Match on Call-ID first. If you stored ${CHANNEL(pjsip,call-id)} in sip_call_id, join on it directly. Otherwise match on destination plus start time within a five-second window, and treat multiple matches as suspects.
5. Apply the provider's rounding model to your billsec before comparing minutes. A 30-second minimum with 6-second increments is GREATEST(30, ceil(billsec / 6.0) * 6); sum that, not raw billsec.
6. Compare in three buckets: matched with equal duration, matched with a delta, unmatched on either side. Quantify each in minutes and money.
7. Explain every unmatched row. Provider-only rows are usually calls Asterisk marked NO ANSWER because the 200 OK arrived after the caller cancelled, or records lost during a restart with batch mode on. Asterisk-only rows are usually calls that failed before the provider accepted them and should show FAILED or CONGESTION.

-- Per-call comparison, worst deltas first, unmatched at the top
SELECT a.sip_call_id, a.calldate, a.dst, a.billsec, p.duration_sec,
       p.duration_sec - a.billsec AS delta
FROM cdr a
LEFT JOIN provider_cdr p ON p.call_id = a.sip_call_id
WHERE a.trunk = 'trunk-a' AND a.disposition = 'ANSWERED'
  AND a.calldate >= '2026-08-01' AND a.calldate < '2026-09-01'
ORDER BY (p.call_id IS NULL) DESC, abs(p.duration_sec - a.billsec) DESC;

A systematic delta of one or two seconds on every call is the difference between the provider's answer signal and your answer timestamp, and is not worth a dispute. A cluster of long provider durations against short billsec values on one day means your BYE did not reach them; find the trace.

Checklist

• batch=no, unanswered=yes, usegmtime=yes, endbeforehexten=yes
• Database backend plus cdr_csv fallback; cdr show status lists both
• Table has linkedid, sequence, trunk and sip_call_id columns; adaptive ODBC reloaded after schema changes
• Queue and Local-channel legs suppressed with CDR_PROP(disable) or filtered in every report
• CEL enabled with a bounded events= list and apps= set; USER_DEFINED events mark IVR choices
• Reports group by linkedid when they count calls and by row when they count legs
• Reconciliation joins on Call-ID and applies the provider's rounding before comparing totals

Common Mistakes

• Reading ${CDR(billsec)} in the h extension with endbeforehexten=no, then wondering why it is always zero.
• Counting rows as calls. Queue calls produce several records; linkedid is the call.
• Local time in the database, discovered during the daylight-saving change when an hour of calls appears to have negative duration.
• Batch mode on a billing system and a kernel update that lost the last five minutes of the month.
• Treating dst as the answering party after a transfer; CEL's ATTENDEDTRANSFER row has the real target in extra.
• Comparing raw billsec with an invoice and disputing the difference that is entirely the provider's stated rounding.
• Two CDR backends writing the same CSV path, producing interleaved half-lines nobody can parse.

Limits and When Not to Use This

CDR and CEL describe what Asterisk did; they do not rate calls, apply tariffs, or produce invoices. That is a billing system's job, fed from these tables. Queue performance belongs to queue_log and the queue skill. For live dashboards, subscribe to cdr_manager and cel_manager events or ARI rather than polling the table. And when the requirement is lawful-intercept or regulatory retention of call metadata with a chain of custody, these files are raw material, not the compliant record; that needs write-once storage and an access log around it.