MySQL CDR Asterisk 22

U

usdmatt

Most guides for MySQL call recording with Asterisk seem to be extremely old or confusing, missing details or difficult to adapt to FreeBSD. Also, even though cdr_mysql still exists, many sites suggest this is deprecated and newer guides all point to the more awkward to configure ODBC integration.
I've managed to get it working for me (seemingly) with the configuration below. (I'm not using MySQL for anything else, Asterisk is configured via the standard config files).

Ayn further input would be appreciated. Using the various information available online to configure both cdr_odbc and cdr_adaptive_odbc seemed to create 2 entries in the log for me, one using the "start" field, and one trying to use "calldate", even though I have not configured the "start => calldate" alias anywhere. With a calldate field in the table, I would get 2 entries, but the one that populated the calldate field had no answer or end time. Removing the cdr_odbc config stopped this from happening and I now get a single CDR entry with the start, answer & end fields all correctly set, but I'm still not convinced my config is ideal. There is practically no official documentation I can find for how this should actually be configured...

I'm assuming that Asterisk and MySQL is already working, and I have the following packages installed (ignoring their dependencies). Asterisk is the latest in the packages tree and seems to be linked to the mysql80-client, so that's the server I am also using.

  • asterisk22-22.4.1
  • mysql80-server-8.0.41
  • mysql-connector-odbc-unixodbc-mysql80

Create a database and table for the call records.
As mentioned I'm using the "start" field instead of "calldate" even though most guides use the latter. I don't really see the point of renaming this and having to create an alias in Asterisk if it wants to call it "start".

Code: 
mysql> create database pbx;
mysql> create user pbx@localhost identified by 'pass123';
mysql> grant all on pbx.* to pbx@localhost;
mysql> CREATE TABLE `cdr` (
  `accountcode` varchar(80) DEFAULT NULL,
  `src` varchar(80) DEFAULT NULL,
  `dst` varchar(80) DEFAULT NULL,
  `dcontext` varchar(80) DEFAULT NULL,
  `clid` varchar(80) DEFAULT NULL,
  `channel` varchar(80) DEFAULT NULL,
  `dstchannel` varchar(80) DEFAULT NULL,
  `lastapp` varchar(80) DEFAULT NULL,
  `lastdata` varchar(80) DEFAULT NULL,
  `start` datetime DEFAULT NULL,
  `answer` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `duration` int DEFAULT NULL,
  `billsec` int DEFAULT NULL,
  `disposition` varchar(45) DEFAULT NULL,
  `amaflags` varchar(45) DEFAULT NULL,
  `userfield` varchar(256) DEFAULT NULL,
  `uniqueid` varchar(150) DEFAULT NULL,
  `linkedid` varchar(150) DEFAULT NULL,
  `peeraccount` varchar(80) DEFAULT NULL,
  `sequence` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Create the ODBC configuration:
Note that the odbcinst.ini file contains the driver library path. This can be checked using pkg info -l mysql-connector-odbc-unixodbc-mysql80.

/usr/local/etc/odbc.ini
Code: 
[asterisk-mysql]
Description = MySQL connection to asterisk
Driver = MySQL
Database = pbx
Server = localhost
UserName = pbx
Password = pass123
Port = 3306
Socket = /tmp/mysql.sock

/usr/local/etc/odbcinst.ini
Code: 
[MySQL]
Description = MySQL
Driver = /usr/local/lib/libmyodbc8a.so
FileUsage = 1

Create the asterisk configuration: (I also commented out the start => calldate alias in cdr_mysql, but that file shouldn't be used anyway)
My cdr_odbc.conf file is empty.

/usr/local/etc/asterisk/res_odbc.conf
Code: 
[asterisk-mysql]
enabled => yes
dsn => asterisk-mysql
username => pbx
password => pass123
pre-connect => yes

/usr/local/etc/asterisk/cdr_adaptive_odbc.conf
Code: 
[first]
connection=asterisk-mysql
table=cdr

I only have one phone on this test system at the moment and no outgoing lines, but calling a test extension creates the following row in my table:

Code: 
*************************** 5. row ***************************
accountcode: NULL
        src: 200
        dst: 904
   dcontext: group_1008
       clid: "System" <200>
    channel: PJSIP/user_1008_200-00000001
 dstchannel: NULL
    lastapp: Echo
   lastdata: NULL
      start: 2025-06-13 11:16:48
     answer: 2025-06-13 11:16:48
        end: 2025-06-13 11:16:50
   duration: 2
    billsec: 2
disposition: ANSWERED
   amaflags: DOCUMENTATION
  userfield: NULL
   uniqueid: 1749809808.2
   linkedid: 1749809808.2
peeraccount: NULL
   sequence: 1

When making a call to myself the lastdata field seems to be truncated, but it appears to make no difference even if I increase the field size and is the same in the CSV log so must be a limit set by Asterisk itself.

Code: 
accountcode: NULL
        src: 200
        dst: 200
   dcontext: group_1008
       clid: "System" <200>
    channel: PJSIP/user_1008_200-00000007
 dstchannel: PJSIP/user_1008_200-00000008
    lastapp: Dial
   lastdata: PJSIP/user_1008_200/sip:user_1008_200@1.2.3.4:57062;x-ast-orig-host=10.0.0.
      start: 2025-06-13 11:00:21
     answer: NULL
        end: 2025-06-13 11:00:24
   duration: 2
    billsec: 0
disposition: NO ANSWER
   amaflags: DOCUMENTATION
  userfield: NULL
   uniqueid: 1749808821.13
   linkedid: 1749808821.13
peeraccount: NULL
   sequence: 7
 
Just as an update, after a reboot it seems I had to re-enable entries in cdr_odbc.conf to get things working again so I've no idea what's going on there, but if you have similar issues getting it working, this is my contents, which is pretty much the default / those found on other guides.

Code: 
[global]
dsn=asterisk-mysql
loguniqueid=yes
dispositionstring=yes
table=cdr
newcdrcolumns=yes
 
You must log in or register to reply here.
Back
Top