Asterisk CDR Storage Backend Guide

Asterisk CDR Storage Backend Guide

Call Detail Records, usually called CDRs, are the summary records Asterisk creates for call reporting, billing, fraud review, missed call reports, and operational analysis. The default CSV backend is useful for a small system, but a database backend is much easier to query, back up, archive, and connect to reporting tools.

This guide shows practical storage options for current Asterisk systems, with a complete MySQL/MariaDB example using ODBC, plus SQLite and PostgreSQL notes.

Backend Recommendations

Backend	Best fit	Notes
CSV (cdr_csv.so)	Default local logging and emergency fallback	Simple, but awkward for reporting at volume
SQLite (cdr_sqlite3_custom.so)	Single-server systems and quick deployments	No external service, but writes lock the database
Adaptive ODBC (cdr_adaptive_odbc.so)	Recommended database path for MySQL/MariaDB and other ODBC databases	Flexible schema, supports custom CDR variables, current replacement for old MySQL module
PostgreSQL (cdr_pgsql.so)	Native PostgreSQL logging without ODBC	Good when you want a direct PostgreSQL backend
RADIUS (cdr_radius.so)	Centralized accounting across multiple telephony systems	Common in carrier or accounting environments

Do not plan new systems around cdr_mysql.so. That module was deprecated long ago and removed in Asterisk 19. For MySQL or MariaDB on Asterisk 19 and newer, use cdr_adaptive_odbc.so.

What CDR Tracks

Asterisk CDRs are written from the perspective of Party A, usually the channel that originated the dial attempt. Party B is the channel or endpoint Party A communicates with. This matters because transfers and multiple dial attempts can create several related CDRs.

CDR field	Typical database column	Description	Writable from dialplan
start	calldate	Time the CDR was created	No
answer	answer	Time the call was answered or bridged	No
end	endtime	Time the CDR ended	No
clid	clid	Caller ID string	No
src	src	Caller ID number	No
dst	dst	Destination extension or dialed value	No
dcontext	dcontext	Destination context	No
channel	channel	Party A channel name	No
dstchannel	dstchannel	Party B channel name	No
lastapp	lastapp	Last dialplan application executed by Party A	No
lastdata	lastdata	Arguments to the last application	No
duration	duration	Seconds from start to end	No
billsec	billsec	Seconds from answer to end	No
disposition	disposition	Final state such as ANSWERED, NO ANSWER, BUSY, FAILED, or CONGESTION	No
amaflags	amaflags	Accounting flag	Yes
accountcode	accountcode	Party A account code	Yes
peeraccount	peeraccount	Party B account code	Yes
uniqueid	uniqueid	Unique ID for the Party A channel	No
linkedid	linkedid	Shared ID that groups related CDRs	No
sequence	sequence	Sequence number for a single CDR record	No
userfield	userfield	Free-form field for local reporting data	Yes

You can read CDR values in the dialplan with ${CDR(fieldname)}. The standard writable fields are accountcode, amaflags, peeraccount, and userfield. You can also create custom CDR variables, but the backend must support them and your schema must include matching columns or aliases.

CDRs can only be modified before the record is finalized. For example, do not expect Set(CDR(userfield)=...) after Dial() returns to change the CDR for the bridge that already ended.

Base CDR Configuration

The main CDR engine configuration is /etc/asterisk/cdr.conf.

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

Important settings:

• enable = yes: enables the CDR engine globally.
• channeldefaultenabled = yes: creates CDRs for new channels unless disabled in the dialplan with Set(CDR_PROP(disable)=1).
• unanswered = yes: logs calls to extensions that never answer and never create an outgoing party.
• congestion = yes: records congested attempts distinctly instead of hiding them in failed call reports.
• endbeforehexten = no: leaves CDR behavior compatible with the default sample configuration. Change only if you need finalized values inside hangup logic.
• batch = no: writes records immediately. Batch mode can reduce backend pressure on high-volume systems, but unsafely terminated Asterisk processes can lose buffered records.
• safeshutdown = yes: waits for pending CDR submission during normal shutdown.

After changes, reload the CDR engine and check status:

*CLI> module reload cdr
*CLI> cdr show status

Some options, including ignorestatechanges, cannot be changed fully on reload. Restart Asterisk during a maintenance window if you change options that affect how CDRs are created or forked.

Complete Example: MySQL or MariaDB with Adaptive ODBC

Use this for current Asterisk releases when you want CDRs in MySQL or MariaDB.

Install ODBC Pieces

Install unixODBC and the database driver for your distribution. Package names vary, so verify the installed driver name afterward.

sudo apt install unixodbc odbc-mariadb
odbcinst -q -d

The odbcinst output might show a driver named MariaDB Unicode, MariaDB, MySQL, or another distribution-specific name. Use the exact name in /etc/odbc.ini.

Create Database and User

CREATE DATABASE asteriskcdrdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER 'asterisk_cdr'@'localhost' IDENTIFIED BY 'strong_password_here';
GRANT INSERT, SELECT ON asteriskcdrdb.* TO 'asterisk_cdr'@'localhost';
FLUSH PRIVILEGES;

If your reporting tool runs as a different database user, give that reporting user SELECT only.

Create the CDR Table

USE asteriskcdrdb;

CREATE TABLE cdr (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    calldate DATETIME NOT NULL,
    answer DATETIME NULL,
    endtime DATETIME NULL,
    clid VARCHAR(80) NOT NULL DEFAULT '',
    src VARCHAR(80) NOT NULL DEFAULT '',
    dst VARCHAR(80) NOT NULL DEFAULT '',
    dcontext VARCHAR(80) NOT NULL DEFAULT '',
    channel VARCHAR(80) NOT NULL DEFAULT '',
    dstchannel VARCHAR(80) NOT NULL DEFAULT '',
    lastapp VARCHAR(80) NOT NULL DEFAULT '',
    lastdata VARCHAR(255) NOT NULL DEFAULT '',
    duration INT UNSIGNED NOT NULL DEFAULT 0,
    billsec INT UNSIGNED NOT NULL DEFAULT 0,
    disposition VARCHAR(45) NOT NULL DEFAULT '',
    amaflags VARCHAR(20) NOT NULL DEFAULT '',
    accountcode VARCHAR(80) NOT NULL DEFAULT '',
    peeraccount VARCHAR(80) NOT NULL DEFAULT '',
    uniqueid VARCHAR(152) NOT NULL DEFAULT '',
    linkedid VARCHAR(152) NOT NULL DEFAULT '',
    sequence INT UNSIGNED NOT NULL DEFAULT 0,
    userfield VARCHAR(255) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    INDEX idx_cdr_calldate (calldate),
    INDEX idx_cdr_linkedid (linkedid),
    INDEX idx_cdr_uniqueid_sequence (uniqueid, sequence),
    INDEX idx_cdr_src_calldate (src, calldate),
    INDEX idx_cdr_dst_calldate (dst, calldate),
    INDEX idx_cdr_disposition_calldate (disposition, calldate),
    INDEX idx_cdr_accountcode_calldate (accountcode, calldate)
);

The calldate column stores the CDR start value. The end field is stored as endtime because end is awkward as a SQL column name.

Configure the System ODBC DSN

Create or edit /etc/odbc.ini:

[asterisk-cdr]
Description = Asterisk CDR database
Driver = MariaDB Unicode
SERVER = 127.0.0.1
PORT = 3306
DATABASE = asteriskcdrdb
OPTION = 3

Test the DSN before touching Asterisk:

isql -v asterisk-cdr asterisk_cdr strong_password_here

Configure Asterisk ODBC Connection

Create or edit /etc/asterisk/res_odbc.conf:

[asterisk-cdr]
enabled => yes
dsn => asterisk-cdr
username => asterisk_cdr
password => strong_password_here
pre-connect => yes
sanitysql => select 1

Configure Adaptive CDR Logging

Create or edit /etc/asterisk/cdr_adaptive_odbc.conf:

[asterisk-cdr]
connection = asterisk-cdr
table = cdr
alias start => calldate
alias end => endtime

cdr_adaptive_odbc.so compares the CDR fields to the table columns. Built-in fields with matching column names are inserted automatically. The aliases above map start to calldate and end to endtime.

If you add custom CDR variables such as CDR(customer_id), add a matching customer_id column to the table and reload cdr_adaptive_odbc.so so the module rereads the schema.

Load and Verify

Make sure these modules are loaded:

*CLI> module show like res_odbc
*CLI> module show like cdr_adaptive_odbc

If needed, load them:

*CLI> module load res_odbc.so
*CLI> module load cdr_adaptive_odbc.so

For permanent loading when autoload = no, add these lines to /etc/asterisk/modules.conf:

load = res_odbc.so
load = cdr_adaptive_odbc.so

Reload and check both layers:

*CLI> module reload res_odbc.so
*CLI> module reload cdr_adaptive_odbc.so
*CLI> odbc show all
*CLI> cdr show status

Place a test call, then query the database:

SELECT calldate, src, dst, duration, billsec, disposition, linkedid
FROM cdr
ORDER BY calldate DESC
LIMIT 10;

SQLite Backend

SQLite is the simplest database backend because it writes to a local file. It is a good fit for small single-server systems, lab systems, and deployments that only need light reporting.

Verify the module:

*CLI> module show like cdr_sqlite3_custom

Configure /etc/asterisk/cdr_sqlite3_custom.conf with the modern advanced mapping style:

[cdr]
table = cdr
columns = calldate, answer, endtime, clid, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode, peeraccount, uniqueid, linkedid, sequence, userfield
fields = start, answer, end, clid, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode, peeraccount, uniqueid, linkedid, sequence, userfield
busy_timeout = 1000

The section name controls the database file. In this example, Asterisk writes to /var/log/asterisk/cdr.db. If the table does not already exist, the module can create it. If the table exists, the configured columns must match.

Query recent calls:

sqlite3 /var/log/asterisk/cdr.db

SELECT calldate, src, dst, duration, disposition
FROM cdr
ORDER BY calldate DESC
LIMIT 10;

SQLite locks the database during writes. Avoid it for high call concurrency, multiple writers, or reporting workloads that run large queries against the live CDR database.

PostgreSQL Backend

PostgreSQL is a good choice when your organization already runs PostgreSQL or you want stronger reporting features. You can use native cdr_pgsql.so for a direct backend, or cdr_adaptive_odbc.so if you want the same adaptive behavior shown in the MySQL/MariaDB example.

Create Database and Role

sudo -u postgres createdb asteriskcdrdb
sudo -u postgres psql asteriskcdrdb

CREATE TABLE cdr (
    id BIGSERIAL PRIMARY KEY,
    calldate TIMESTAMP NOT NULL DEFAULT NOW(),
    clid VARCHAR(80) NOT NULL DEFAULT '',
    src VARCHAR(80) NOT NULL DEFAULT '',
    dst VARCHAR(80) NOT NULL DEFAULT '',
    dcontext VARCHAR(80) NOT NULL DEFAULT '',
    channel VARCHAR(80) NOT NULL DEFAULT '',
    dstchannel VARCHAR(80) NOT NULL DEFAULT '',
    lastapp VARCHAR(80) NOT NULL DEFAULT '',
    lastdata VARCHAR(255) NOT NULL DEFAULT '',
    duration INTEGER NOT NULL DEFAULT 0,
    billsec INTEGER NOT NULL DEFAULT 0,
    disposition VARCHAR(45) NOT NULL DEFAULT '',
    amaflags INTEGER NOT NULL DEFAULT 0,
    accountcode VARCHAR(80) NOT NULL DEFAULT '',
    peeraccount VARCHAR(80) NOT NULL DEFAULT '',
    uniqueid VARCHAR(152) NOT NULL DEFAULT '',
    linkedid VARCHAR(152) NOT NULL DEFAULT '',
    sequence INTEGER NOT NULL DEFAULT 0,
    userfield VARCHAR(255) NOT NULL DEFAULT ''
);

CREATE INDEX idx_cdr_calldate ON cdr (calldate);
CREATE INDEX idx_cdr_linkedid ON cdr (linkedid);
CREATE INDEX idx_cdr_src_calldate ON cdr (src, calldate);
CREATE INDEX idx_cdr_dst_calldate ON cdr (dst, calldate);
CREATE INDEX idx_cdr_disposition_calldate ON cdr (disposition, calldate);

CREATE ROLE asterisk_cdr WITH LOGIN PASSWORD 'strong_password_here';
GRANT INSERT, SELECT ON cdr TO asterisk_cdr;
GRANT USAGE, SELECT ON SEQUENCE cdr_id_seq TO asterisk_cdr;

Configure Native PostgreSQL CDR

Create or edit /etc/asterisk/cdr_pgsql.conf:

[global]
hostname = localhost
port = 5432
dbname = asteriskcdrdb
user = asterisk_cdr
password = strong_password_here
table = cdr

Load and verify:

*CLI> module show like cdr_pgsql
*CLI> module reload cdr_pgsql.so
*CLI> cdr show status

If you need additional custom CDR variables in PostgreSQL, consider using cdr_adaptive_odbc.so instead of the native PostgreSQL backend so schema and aliases are explicit.

Adding Reporting Data from the Dialplan

Use accountcode for stable grouping such as department, customer, tenant, or cost center. Use userfield for one local value that helps your reports.

[from-internal]
exten => _X.,1,Set(CDR(accountcode)=sales)
same => n,Set(CDR(userfield)=outbound-sales-call)
same => n,Dial(PJSIP/${EXTEN},30)
same => n,Hangup()

For multiple custom reporting values, use custom CDR variables with an adaptive backend:

[from-internal]
exten => _X.,1,Set(CDR(customer_id)=42)
same => n,Set(CDR(route_class)=domestic)
same => n,Dial(PJSIP/${EXTEN},30)
same => n,Hangup()

Then add matching database columns and reload the adaptive backend:

ALTER TABLE cdr ADD COLUMN customer_id VARCHAR(40) NOT NULL DEFAULT '';
ALTER TABLE cdr ADD COLUMN route_class VARCHAR(40) NOT NULL DEFAULT '';

*CLI> module reload cdr_adaptive_odbc.so

Useful Reporting Queries

Recent calls:

SELECT calldate, src, dst, duration, billsec, disposition
FROM cdr
ORDER BY calldate DESC
LIMIT 20;

Answered call volume by day:

SELECT DATE(calldate) AS day, COUNT(*) AS calls, SUM(billsec) AS billable_seconds
FROM cdr
WHERE disposition = 'ANSWERED'
  AND calldate >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY DATE(calldate)
ORDER BY day;

Calls grouped by linked call ID:

SELECT linkedid, MIN(calldate) AS first_record, COUNT(*) AS cdr_rows, SUM(billsec) AS total_billsec
FROM cdr
GROUP BY linkedid
ORDER BY first_record DESC
LIMIT 20;

Common failure destinations:

SELECT dst, disposition, COUNT(*) AS attempts
FROM cdr
WHERE calldate >= CURRENT_DATE - INTERVAL 7 DAY
  AND disposition <> 'ANSWERED'
GROUP BY dst, disposition
ORDER BY attempts DESC
LIMIT 20;

For PostgreSQL, replace MySQL date expressions such as CURRENT_DATE - INTERVAL 30 DAY with CURRENT_DATE - INTERVAL '30 days'.

Retention and Archiving

CDRs often contain phone numbers, account codes, caller ID names, and routing details. Keep them only as long as your business, billing, support, and compliance needs require.

A practical retention policy usually has four parts:

• Keep recent CDRs online for fast reporting.
• Archive older CDRs to compressed files or cheaper storage.
• Delete data past the retention window.
• Document who can query CDRs and why.

Example purge commands:

DELETE FROM cdr WHERE calldate < NOW() - INTERVAL 18 MONTH;

DELETE FROM cdr WHERE calldate < CURRENT_DATE - INTERVAL '18 months';

DELETE FROM cdr WHERE calldate < datetime('now', '-18 months');

The first example is for MySQL/MariaDB, the second is for PostgreSQL, and the third is for SQLite.

For larger MySQL or PostgreSQL tables, partition by month and drop old partitions instead of running large deletes. This reduces table bloat, lock time, and index churn.

Always test retention SQL on a copy or inside a transaction before running it against production CDRs.

Troubleshooting

Symptom	Checks
No CDR rows appear	Run cdr show status, verify CDR logging is enabled, and confirm at least one backend is registered
Module is missing	Run module show like cdr, then check make menuselect and required development libraries before rebuilding Asterisk
MySQL/MariaDB does not work on Asterisk 19 or newer	Replace cdr_mysql.so with res_odbc.so plus cdr_adaptive_odbc.so
ODBC connection fails	Test with isql, then check odbc show all, /etc/odbc.ini, /etc/asterisk/res_odbc.conf, driver names, usernames, and database grants
Adaptive ODBC loads but writes no rows	Check Asterisk logs for schema mismatch messages, verify table and column names, and reload cdr_adaptive_odbc.so after schema changes
Duplicate CDR rows	Check cdr show status for multiple registered backends, and remove any backend you do not want
Missing unanswered calls	Set unanswered = yes, then retest with a call that never creates an outgoing channel
Missing congestion records	Set congestion = yes, then confirm the dial attempt maps to congestion instead of a generic failure
CDRs disappear during crashes	Disable batch mode or lower size and time; keep safeshutdown = yes for normal shutdowns
Long reports slow down calls	Move reporting to a replica or archive database, add date-based indexes, and avoid heavy queries against SQLite

Useful CLI commands:

*CLI> cdr show status
*CLI> cdr show active
*CLI> cdr submit
*CLI> cdr set debug on
*CLI> module show like cdr
*CLI> module show like odbc
*CLI> odbc show all

Remember to disable CDR debug after troubleshooting:

*CLI> cdr set debug off

CDR vs CEL

Use CDR when you need summarized call records for billing, basic reports, traffic analysis, missed calls, and account-code reporting.

Use Channel Event Logging, or CEL, when you need the timeline of what happened during the call. CEL is better for transfers, holds, parking, bridge enter and leave events, conferences, queue analysis, and detailed troubleshooting.

Need	Use
Billable seconds by caller or account code	CDR
Missed call list	CDR
Total calls by day or trunk	CDR
Transfer path and bridge history	CEL
Hold time and parking events	CEL
Reconstructing complex call flow	CEL

Many production systems keep both. CDR gives fast summary reporting. CEL gives the audit trail when the summary is not enough.

Practical Checklist

Before considering the CDR backend done:

• cdr show status shows CDR logging enabled and the expected backend registered.
• A test answered call writes one or more rows.
• A test unanswered call behaves the way your reporting needs expect.
• linkedid is present and indexed.
• Reporting users have read-only database access.
• The database user used by Asterisk has only the permissions it needs.
• Retention and backup jobs are documented.
• CSV fallback is either intentionally enabled or intentionally disabled.

For more focused MySQL setup details, see the CDR with MySQL Backend snippet, but use the ODBC approach above for new Asterisk 19 and newer deployments.