CDR with MySQL Backend

CDR with MySQL Backend

Call Detail Records (CDR) log every call that passes through Asterisk. By default they go to CSV files, but storing them in MySQL makes querying, reporting, and building web dashboards much easier.

Requirements

• cdr_mysql.so module compiled and loaded (check with module show like mysql)

• MySQL or MariaDB server installed and running

• A database and table created for CDR storage

Create the Database and Table

mysqladmin -u root -p create asteriskcdrdb

Create a sql file with the following

CREATE TABLE cdr (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    calldate DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    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(80) NOT NULL DEFAULT '',
    duration INT(11) NOT NULL DEFAULT '0',
    billsec INT(11) NOT NULL DEFAULT '0',
    disposition VARCHAR(45) NOT NULL DEFAULT '',
    amaflags INT(11) NOT NULL DEFAULT '0',
    accountcode VARCHAR(20) NOT NULL DEFAULT '',
    uniqueid VARCHAR(32) NOT NULL DEFAULT '',
    userfield VARCHAR(255) NOT NULL DEFAULT '',
    peeraccount VARCHAR(20) NOT NULL DEFAULT '',
    linkedid VARCHAR(32) NOT NULL DEFAULT '',
    sequence INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    INDEX calldate (calldate),
    INDEX src (src),
    INDEX dst (dst)
);

Import the schema

mysql -u root -p asteriskcdrdb < /root/cdr_database.sql

Asterisk Configuration (cdr_mysql.conf)

[global]
hostname = localhost
dbname = asteriskcdrdb
table = cdr
user = asterisk_cdr
password = strong_database_password
port = 3306

Verify CDR is Working

*CLI> cdr mysql status
Connected to asteriskcdrdb@localhost, port 3306
  Using table: cdr

*CLI> module show like cdr_mysql
Module         Description                    Use Count  Status
cdr_mysql.so   MySQL CDR Backend              0          Running

Query Call Records

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

-- Calls longer than 5 minutes
SELECT calldate, src, dst, duration, billsec FROM cdr
WHERE billsec > 300 ORDER BY calldate DESC;

-- Call count by disposition
SELECT disposition, COUNT(*) AS count FROM cdr
GROUP BY disposition;

-- Busiest hour of the day
SELECT HOUR(calldate) AS hour, COUNT(*) AS calls FROM cdr
GROUP BY hour ORDER BY calls DESC;

How it works

1. CDR fields: src is the caller, dst is the destination dialed, duration is total call time (including ring), billsec is answered time only, disposition is the outcome (ANSWERED, NO ANSWER, BUSY, FAILED).

2. cdr_mysql.so: This module writes CDR records to MySQL in real time as each call ends. It must be compiled with Asterisk (enabled in make menuselect under Add-ons).

3. Database user: Create a dedicated MySQL user for Asterisk instead of using root: GRANT INSERT,SELECT ON asteriskcdrdb.* TO 'asterisk_cdr'@'localhost' IDENTIFIED BY 'password';.

4. Indexes: The calldate, src, and dst indexes are critical for query performance. Without them, reporting queries on large tables will be very slow.

5. uniqueid and linkedid: uniqueid identifies a single channel. linkedid ties related channels together (e.g., a transferred call produces multiple CDR records with the same linkedid).

Tips

• Reload CDR without restarting: cdr mysql status and module reload cdr_mysql.so.

• For ODBC-based CDR (works with PostgreSQL, SQLite, etc.), use cdr_adaptive_odbc.so instead.

• Add custom data to CDR with Set(CDR(userfield)=value) in the dialplan.

• Set accountcode on endpoints in pjsip.conf to tag CDR records by department or cost center.

• Partition large CDR tables by month for easier archiving: PARTITION BY RANGE (YEAR(calldate) * 100 + MONTH(calldate)).

• For a web-based CDR viewer, projects like Asterisk-Stat or custom PHP/Python dashboards can query this table directly.

See Also

For a comprehensive overview of all CDR backends (SQLite, MySQL, PostgreSQL) and CDR configuration, see the CDR Storage Backend Guide.