# Asterisk CDR Reports from MySQL/MariaDB (FreePBX asteriskcdrdb)

Source: https://srvscripts.com/guides/asterisk-cdr-mysql-reporting/
Updated: 2026-10-07
Publisher: srvScripts (https://srvscripts.com/)

**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](/tools/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](/guides/mariadb-slow-queries-11-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)](https://docs.asterisk.org/Latest_API/API_Documentation/Dialplan_Functions/CDR/) · [Asterisk: Cdr AMI event (dispositions)](https://docs.asterisk.org/Latest_API/API_Documentation/AMI_Events/Cdr/) · [MariaDB: EXPLAIN](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/explain)

**Related:** [Asterisk Call Recording with MixMonitor: Formats, Stereo, Retention](/guides/asterisk-call-recording-mixmonitor/) · [How Many SIP Channels Do You Need? Size SIP Trunks with Erlang B](/guides/how-many-sip-channels/) · [Erlang Calculator: How Many SIP Channels (Erlang B) or Call Centre Agents (Erlang C)](/tools/erlang-calculator/) · [MySQL Health Snapshot Script: Free 10-Second MariaDB Check](/scripts/mysql-health-snapshot/) · [MariaDB Slow Queries on 11/12: Diagnosis Steps](/guides/mariadb-slow-queries-11-12/)

**See also:** [Asterisk AMI Originate a Call with Python](/guides/asterisk-ami-originate-call-python/) · [FreePBX Backup and Restore from the Command Line (fwconsole)](/guides/freepbx-backup-restore-cli/)

## Frequently asked questions

### Where are FreePBX call logs stored?

In the MariaDB database asteriskcdrdb, table cdr. FreePBX writes them through the Asterisk Adaptive ODBC CDR backend.

### What is the difference between duration and billsec?

duration counts from the start of the record to the end, including ringing. billsec counts only from answer to hang-up, so it is the talk time.

### Why does one call have several CDR rows?

IVRs, ring groups, queues and transfers create a record per leg. All legs of a call share the same linkedid, so count DISTINCT linkedid for calls.

### Why are missed calls not in my CDR?

Asterisk does not log unanswered dial attempts unless unanswered = yes is set in the CDR general settings, and an IVR that answers makes the call ANSWERED.

### How do I export FreePBX CDRs to CSV?

Use the CSV option in Reports, CDR Reports, or run your SQL with mysql –batch and convert the tab-separated output with Python csv.

### Is it safe to add indexes to the cdr table?

Generally yes with a backup first and an online ALTER, but the table belongs to FreePBX, so document the change in case a restore rebuilds it.