Asterisk CDR Reports from MySQL/MariaDB (FreePBX asteriskcdrdb)

Short answer: FreePBX writes call detail records to the cdr table in the asteriskcdrdb database. Connect with a read-only database user and query it with SQL: count calls with COUNT(DISTINCT linkedid) (one call often has several CDR rows), use disposition for answered or not, billsec for talk time, and always filter on a calldate range so MariaDB can use the index. Export with mysql --batch to a CSV file, and treat the data as personal data.

We ran DESCRIBE, SHOW INDEX, EXPLAIN and every report query below (read-only) on our lab server (Debian 12, FreePBX 17.0.33, Asterisk 22.11, MariaDB 10.11.18) on 7 October 2026. The lab holds only a dozen test calls, so we show real output only where it illustrates a point.

Where FreePBX stores CDRs

Asterisk hands each finished call record to its CDR backends. On our FreePBX 17 lab, asterisk -rx "cdr show status" lists two backends, cdr_manager and Adaptive ODBC. The ODBC one writes to MariaDB: /etc/asterisk/cdr_adaptive_odbc.conf points it at the asteriskcdrdb connection and table cdr. The same database also holds cel (channel event logging), queuelog and transient_cdr.

Two settings from that status output matter for reports:

• Log unanswered calls: No (the default on our lab). Dial attempts that are never answered may not get their own row, so a “missed calls” report can under-count. To record them, add the line unanswered = yes to /etc/asterisk/cdr_general_custom.conf (FreePBX’s cdr.conf includes that file inside [general], so no section header is needed) and run asterisk -rx "module reload cdr". We checked on a separate test Asterisk 22.11 instance that the reload switches “Log unanswered calls” without a restart. Expect many more rows: one per phone that rang.
• Times are local server time. calldate is a datetime without a time zone. Check the server time zone before comparing with your phone bill.

The FreePBX GUI report is at Reports > CDR Reports, and it has a CSV file option. SQL is for the questions that screen does not answer.

The cdr table columns

DESCRIBE asteriskcdrdb.cdr on our lab returned 26 columns. The ones you will use in reports:

Column	Type	Meaning
calldate	datetime	When the record started (indexed)
clid	varchar(80)	Full caller ID, name and number
src	varchar(80)	Caller ID number of the calling party
dst	varchar(80)	Extension the call was sent to in the dialplan (indexed)
dcontext	varchar(80)	Dialplan context, for example from-internal or from-trunk
channel / dstchannel	varchar(80)	Calling and called channel names, for example PJSIP/1001-0000001a
lastapp / lastdata	varchar(80)	Last dialplan application and its arguments
duration	int	Seconds from start to end, ringing included
billsec	int	Seconds from answer to end: the talk time
disposition	varchar(45)	ANSWERED, NO ANSWER, BUSY, FAILED or CONGESTION
accountcode	varchar(20)	Account code, if you set one (indexed)
uniqueid	varchar(32)	ID of the calling channel (indexed)
linkedid	varchar(32)	ID shared by all records of one call (indexed)
sequence	int	Order of records within a call
did	varchar(50)	FreePBX: the inbound number dialled (indexed)
recordingfile	varchar(255)	FreePBX: call recording file name, if recorded (indexed)
cnum / cnam	varchar(80)	FreePBX: caller number and name
outbound_cnum / outbound_cnam	varchar(80)	FreePBX: caller ID sent to the trunk

The remaining columns are amaflags, userfield, dst_cnam and peeraccount.

One call, several rows

A call through an IVR, a ring group or a transfer creates several CDR rows that share a linkedid. Count calls with COUNT(DISTINCT linkedid) and count legs with COUNT(*). On our lab, the first report below showed exactly that difference:

+------------+-------+----------+
| day        | calls | cdr_rows |
+------------+-------+----------+
| 2026-10-05 |     3 |        6 |
| 2026-10-06 |     3 |        6 |
+------------+-------+----------+

Connect with a read-only user

On Debian, root can open the database through the local socket with mysql asteriskcdrdb. For scripts and reporting tools, create a separate user that can only read the CDR table. Do not reuse the FreePBX database user from /etc/freepbx.conf, which can change everything.

CREATE USER 'cdrreport'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT ON asteriskcdrdb.cdr TO 'cdrreport'@'localhost';

Put the credentials in ~/.my.cnf of the account that runs the reports (a [client] section with user and password), set it to mode 600, and the commands below work without a password on the command line. We did not create this user on our lab, to keep its database unchanged.

Useful CDR report queries

All queries filter on a date range. Change the dates or the INTERVAL to suit. Run them with mysql -t asteriskcdrdb < report.sql for a table.

Calls per day

SELECT DATE(calldate) AS day, COUNT(DISTINCT linkedid) AS calls, COUNT(*) AS cdr_rows
FROM cdr
WHERE calldate >= '2026-10-01' AND calldate < '2026-11-01'
GROUP BY day ORDER BY day;

Answered vs not answered

SELECT disposition, COUNT(*) AS legs
FROM cdr
WHERE calldate >= CURDATE() - INTERVAL 30 DAY
GROUP BY disposition ORDER BY legs DESC;

This counts legs, not calls. An inbound call answered by an IVR or announcement is ANSWERED even if no person picked up, because the PBX answered it. To find inbound calls no extension answered, group by call and look at the phone legs:

SELECT linkedid, MIN(calldate) AS started, MAX(did) AS did, MAX(src) AS caller
FROM cdr
WHERE calldate >= CURDATE() - INTERVAL 7 DAY AND did <> ''
GROUP BY linkedid
HAVING SUM(disposition = 'ANSWERED' AND dstchannel LIKE 'PJSIP/%') = 0
ORDER BY started;

Check a few results against the GUI before you trust it: trunk channels are also PJSIP/, and voicemail or queue setups can need different conditions.

Calls made per extension

SELECT cnum AS extension, COUNT(DISTINCT linkedid) AS calls,
       SUM(billsec) AS talk_seconds, SEC_TO_TIME(SUM(billsec)) AS talk_time
FROM cdr
WHERE calldate >= CURDATE() - INTERVAL 30 DAY AND cnum REGEXP '^[0-9]{2,6}$'
GROUP BY cnum ORDER BY calls DESC;

Calls received per extension

The called extension is inside dstchannel (PJSIP/1001-0000001a). Cut it out with SUBSTRING_INDEX:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(dstchannel, '/', -1), '-', 1) AS extension,
       COUNT(*) AS legs,
       SUM(disposition = 'ANSWERED') AS answered,
       SUM(disposition <> 'ANSWERED') AS not_answered
FROM cdr
WHERE calldate >= CURDATE() - INTERVAL 30 DAY AND dstchannel LIKE 'PJSIP/%'
GROUP BY extension ORDER BY legs DESC;

Top destinations (masked)

SELECT CONCAT(LEFT(dst, GREATEST(LENGTH(dst) - 4, 0)), 'XXXX') AS destination,
       COUNT(*) AS calls, ROUND(AVG(billsec)) AS avg_seconds
FROM cdr
WHERE calldate >= CURDATE() - INTERVAL 30 DAY
  AND disposition = 'ANSWERED' AND LENGTH(dst) >= 7
GROUP BY destination ORDER BY calls DESC LIMIT 20;

Masking the last four digits groups numbers by prefix and keeps full numbers out of the report. Drop the CONCAT only when the reader is allowed to see them.

Average call duration

SELECT COUNT(*) AS answered_legs,
       ROUND(AVG(billsec)) AS avg_talk_seconds,
       SEC_TO_TIME(ROUND(AVG(billsec))) AS avg_talk,
       MAX(billsec) AS longest_seconds
FROM cdr
WHERE calldate >= CURDATE() - INTERVAL 30 DAY AND disposition = 'ANSWERED';

Busiest hour of the day

SELECT HOUR(calldate) AS hour_of_day, COUNT(DISTINCT linkedid) AS calls
FROM cdr
WHERE calldate >= CURDATE() - INTERVAL 30 DAY
GROUP BY hour_of_day ORDER BY calls DESC LIMIT 5;

The busiest hour is what you need for trunk sizing: feed it into our Erlang calculator.

Inbound calls per DID

SELECT did, COUNT(DISTINCT linkedid) AS calls
FROM cdr
WHERE calldate >= CURDATE() - INTERVAL 30 DAY AND did <> ''
GROUP BY did ORDER BY calls DESC;

Export a report to CSV

mysql --batch prints tab-separated rows with a header line. Spreadsheets open that directly, but caller ID names can contain commas or quotes, so convert it with Python’s csv module for a clean CSV. We ran this on our lab:

mysql --batch --raw asteriskcdrdb -e "SELECT DATE(calldate) AS day, COUNT(DISTINCT linkedid) AS calls FROM cdr GROUP BY day" \
  | python3 -c 'import csv,sys; w=csv.writer(sys.stdout); [w.writerow(l.rstrip("\n").split("\t")) for l in sys.stdin]' \
  > calls.csv

day,calls
2026-10-05,3
2026-10-06,3

Put the SQL in a file and the line in a cron job to mail a monthly report. Server-side SELECT ... INTO OUTFILE also works on MariaDB, but it needs the FILE privilege, which a reporting user should not have, and writes the file as the database server user.

Indexes and slow reports

SHOW INDEX FROM cdr on our lab lists single-column indexes on calldate, dst, accountcode, uniqueid, did, recordingfile, dstchannel and linkedid. There is no index on src, cnum or disposition.

Write date filters as a range on the bare column. EXPLAIN on our lab showed the difference: WHERE calldate >= '2026-10-01' AND calldate < '2026-11-01' used the calldate index as a range scan, while WHERE DATE(calldate) = '2026-10-05' had no usable key (possible_keys: NULL) and scanned the whole index. With millions of rows that is the difference between milliseconds and minutes.

If you often filter by caller extension over long periods, a composite index helps:

Adding an index changes the table FreePBX owns. Take a dump first (mariadb-dump asteriskcdrdb cdr > cdr-before-index.sql), run it in a quiet hour, and note the change: a restore or module update could rebuild the table without it.

ALTER TABLE asteriskcdrdb.cdr ADD INDEX idx_cnum_calldate (cnum, calldate),
  ALGORITHM=INPLACE, LOCK=NONE;

For general MariaDB slow query work, see MariaDB slow queries on 11 and 12.

Privacy and retention

CDRs hold phone numbers, names, times and who called whom. Under laws such as the GDPR that is personal data. Practical rules:

• Give report users SELECT on the cdr table only, and only to people who need it.
• Mask numbers in reports that leave the IT team, as in the top destinations query.
• Decide a retention period and enforce it. FreePBX keeps CDRs until you delete them.
• Remember that recordingfile points to audio files that are even more sensitive, and that backups contain all of it.

The next command permanently deletes old call records. Dump the rows first and check the count with a SELECT COUNT(*) using the same WHERE.

mariadb-dump asteriskcdrdb cdr --where="calldate < NOW() - INTERVAL 13 MONTH" > cdr-older-than-13-months.sql
mysql asteriskcdrdb -e "SELECT COUNT(*) FROM cdr WHERE calldate < NOW() - INTERVAL 13 MONTH"
mysql asteriskcdrdb -e "DELETE FROM cdr WHERE calldate < NOW() - INTERVAL 13 MONTH"

Check that it worked, and common problems

• New calls appear: make a test call, then SELECT calldate, src, dst, disposition, billsec FROM cdr ORDER BY calldate DESC LIMIT 5;
• No new rows: check asterisk -rx "cdr show status" lists the Adaptive ODBC backend, and asterisk -rx "odbc show" shows the connection as connected.
• Times look wrong: compare SELECT NOW(), MAX(calldate) FROM cdr; with date on the server.
• Missed calls look too low: unanswered logging is off (see above), or the IVR answered the call.
• Call counts look doubled: you counted rows, not DISTINCT linkedid.
• Reports are slow: replace DATE(calldate) = ... with a range, check with EXPLAIN, and add an index only if needed.

Official documentation: Asterisk: CDR dialplan function (fields) · Asterisk: Cdr AMI event (dispositions) · MariaDB: EXPLAIN

Related: Asterisk Call Recording with MixMonitor: Formats, Stereo, Retention · How Many SIP Channels Do You Need? Size SIP Trunks with Erlang B · Erlang Calculator: How Many SIP Channels (Erlang B) or Call Centre Agents (Erlang C) · MySQL Health Snapshot Script: Free 10-Second MariaDB Check · MariaDB Slow Queries on 11/12: Diagnosis Steps

See also: Asterisk AMI Originate a Call with Python · FreePBX Backup and Restore from the Command Line (fwconsole)

Frequently asked questions