Configure Asterisk with CDR-Stats and CDR-Pusher

Asterisk supports many backends to store CDRs: SQLite3, PostgreSQL, MySQL and many more.

In this document, we will explain how to configure Asterisk to store CDRs in SQLite3 or Mysql then configure CDR-Pusher to send the CDR to CDR-Stats. Sqlite3 is the one we will recommend as this is by far the easiest to setup.

Store Asterisk CDRs to SQLITE3

The cdr_sqlite module was deprecated and has been removed. Users of this module should use the cdr_sqlite3_custom module instead.

If Asterisk is compiled from source, then providing that SQLite3 is installed, then during make menuselect under Call Detail Recording, cdr_sqlite3_custom can be selected for installation.

For those using Asterisk via RPMs such as in the popular free PBX system, then something like yum install asterisk11-sqlite3.x86_64. Do yum search sqlite3 to find the correct module for your version of Asterisk.

There is only one config file for the cdr_sqlite3_custom.so module, this is configured at /etc/asterisk/cdr_sqlite3_custom.conf and the default settings are as follows:

;
; Mappings for custom config file
;
[master] ; currently, only file "master.db" is supported, with only one table at a time.
table => cdr
columns => calldate, clid, dcontext, channel, dstchannel, lastapp, lastdata,source, destination, duration, billsec, disposition, amaflags, accountcode, uniqueid, userfield, test
values => '${CDR(start)}','${CDR(clid)}','${CDR(dcontext)}','${CDR(channel)}','${CDR(dstchannel)}','${CDR(lastapp)}','${CDR(lastdata)}','${CDR(src)}','${CDR(dst)}','${CDR(duration,f)}','${CDR(billsec,f)}','${CDR(disposition)}','${CDR(amaflags)}','${CDR(accountcode)}','${CDR(uniqueid)}','${CDR(userfield)}','${CDR(test)}'

After installation, restart asterisk. When CDR are written, they will be found at /var/log/asterisk/master.db.

To check that CDR are being written to the SQLite3 DB with the following:

$ sqlite3 /var/log/asterisk/master.db
$ SELECT * FROM cdr LIMIT 10;

The result will be:

SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

For readability, type
.header on
.mode column
Then you can list your CDR with standard SQL commands, e.g.
select * from cdr;

CTRL-D exits the SQLite console

Store Asterisk CDRs to MySQL

There is only one config file for the cdr_mysql.so module, this is configured at /etc/asterisk/cdr_mysql.conf and the default settings are as follows:

;
; Note - if the database server is hosted on the same machine as the
; asterisk server, you can achieve a local Unix socket connection by
; setting hostname=localhost
;
; port and sock are both optional parameters. If hostname is specified
; and is not "localhost", then cdr_mysql will attempt to connect to the
; port specified or use the default port. If hostname is not specified
; or if hostname is "localhost", then cdr_mysql will attempt to connect
; to the socket file specified by sock or otherwise use the default socket
; file.
;
[global]
hostname=localhost
dbname=asteriskcdrdb
password=password
user=asteriskcdruser
table=cdr
;port=3306
;sock=/tmp/mysql.sock
;userfield=1

Enable the last option userfield if you wish to use SetCDRUserField.

Configure with your hostname, dbname, password, user and table.

After installation, restart asterisk.

To check that CDR are being written to the MySQL DB with the following:

$ mysql -uasteriskcdruser -pasteriskcdrdb asteriskcdrdb
$ SELECT * FROM cdr LIMIT 10;

The result will be:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4862
Server version: 5.5.44-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from cdr LIMIT 10;
...
...

CTRL-D exits the MySQL console

Configure CDR-pusher to collect CDRs

Once your CDRs will be stored to a SQLite Database, you will have to install CDR-Pusher on your Asterisk server. You can find instructions how to install CDR-Pusher here: https://github.com/cdr-stats/cdr-pusher

To install Supervisor on CentOS 6 or RHEL6, the procedure is more complex, here it’s how we do it:

$ yum -y install python-setuptools

$ easy_install supervisor

$ wget https://raw.githubusercontent.com/cdr-stats/cdr-stats/develop/install/supervisor/centos/supervisord.conf -O /etc/supervisord.conf

$ wget https://raw.githubusercontent.com/cdr-stats/cdr-stats/develop/install/supervisor/centos/supervisord -O /etc/init.d/supervisor

$ chmod +x /etc/init.d/supervisor

$ supervisord --version

$ /etc/init.d/supervisor stop ; sleep 2 ; /etc/init.d/supervisor start

Also make sure you have recent version of Git.

Check your git version with:

git $ version

If your git version <= 1.7.4, then you will need to install a recent version, you can follow the instructions here how to install a recent Git on CentOS6 here: http://tecadmin.net/how-to-upgrade-git-version-1-7-10-on-centos-6/

After installation of CDR-Pusher you can find the configuration file at ‘/etc/cdr-pusher.yaml’. You will need to configure properly some settings in order to connect CDR-pusher to your SQLite or MySQL CDR backend and to your CDR-Stats server.

Configure CDR-Pusher for SQLite3

Here some of the settings you need to change to fetch SQLite CDR form Asterisk, edit ‘/etc/cdr-pusher.yaml’:

# storage_source_type: type to CDRs to push
storage_source: "sqlite3"

# db_file: specify the database path and name
db_file: "/var/log/asterisk/master.db"

# db_table: the DB table name
db_table: "cdr"

# cdr_fields is list of fields that will be fetched (from SQLite3) and pushed (to PostgreSQL)
# - if dest_field is callid, it will be used in riak as key to insert
cdr_fields:
    - orig_field: uniqueid
      dest_field: callid
      type_field: string
    - orig_field: "'' AS cidnum"
      dest_field: caller_id_number
      type_field: string
    - orig_field: clid
      dest_field: caller_id_name
      type_field: string
    - orig_field: destination
      dest_field: destination_number
      type_field: string
    - orig_field: "CASE WHEN disposition='ANSWER' THEN 16 WHEN disposition='ANSWERED' THEN 16 WHEN disposition='BUSY' THEN 17 WHEN disposition='NOANSWER' THEN 19 WHEN disposition='NO ANSWER' THEN 19 WHEN disposition='CANCEL' THEN 21 WHEN disposition='CANCELED' THEN 21 WHEN disposition='CONGESTION' THEN 34 WHEN disposition='CHANUNAVAIL' THEN 47 WHEN disposition='DONTCALL' THEN 21 WHEN disposition='TORTURE' THEN 21 WHEN disposition='INVALIDARGS' THEN 47 WHEN disposition='FAIL' THEN 41 WHEN disposition='FAILED' THEN 41 ELSE 41 END"
      dest_field: hangup_cause_id
      type_field: int
    - orig_field: CAST(duration AS INTEGER)
      dest_field: duration
      type_field: int
    - orig_field: CAST(billsec AS INTEGER)
      dest_field: billsec
      type_field: int
    - orig_field: "datetime(calldate)"
      dest_field: starting_date
      type_field: date
    - orig_field: accountcode
      dest_field: accountcode
      type_field: string
    - orig_field: channel
      dest_field: extradata
      type_field: jsonb
    - orig_field: lastapp
      dest_field: extradata
      type_field: jsonb
    - orig_field: dcontext
      dest_field: extradata
      type_field: jsonb

Configure CDR-Pusher for MySQL

Here some of the settings you need to change to fetch MySQL CDR from Asterisk, edit ‘/etc/cdr-pusher.yaml’:

# storage_source_type: type to CDRs to push
storage_source: "mysql"

# db_file: specify the database path and name
db_file: ""

# Database DNS
# Use this with MySQL
db_dns: "username:[email protected]/database"

# db_table: the DB table name
db_table: "cdr"

# cdr_fields is list of fields that will be fetched and pushed (to PostgreSQL)
# - if dest_field is callid, it will be used in riak as key to insert
cdr_fields:
    - orig_field: uniqueid
      dest_field: callid
      type_field: string
    - orig_field: clid
      dest_field: caller_id_name
      type_field: string
    - orig_field: "'' AS cidnum"
      dest_field: caller_id_number
      type_field: string
    - orig_field: dst
      dest_field: destination_number
      type_field: string
    - orig_field: "CASE disposition WHEN 'ANSWER' THEN 16 WHEN 'ANSWERED' THEN 16 WHEN 'BUSY' THEN 17 WHEN 'NOANSWER' THEN 19 WHEN 'NO ANSWER' THEN 19 WHEN 'CANCEL' THEN 21 WHEN 'CANCELED' THEN 21 WHEN 'CONGESTION' THEN 34 WHEN 'CHANUNAVAIL' THEN 47 WHEN 'DONTCALL' THEN 21 WHEN 'TORTURE' THEN 21 WHEN 'INVALIDARGS' THEN 47 WHEN 'FAIL' THEN 41 WHEN 'FAILED' THEN 41 ELSE 41 END"
      dest_field: hangup_cause_id
      type_field: int
    - orig_field: duration
      dest_field: duration
      type_field: int
    - orig_field: billsec
      dest_field: billsec
      type_field: int
    - orig_field: accountcode
      dest_field: accountcode
      type_field: string
    - orig_field: calldate
      dest_field: starting_date
      type_field: date
    - orig_field: userfield
      dest_field: extradata
      type_field: jsonb
    - orig_field: dcontext
      dest_field: extradata
      type_field: jsonb
    - orig_field: channel
      dest_field: extradata
      type_field: jsonb
    - orig_field: lastapp
      dest_field: extradata
      type_field: jsonb
    - orig_field: lastdata
      dest_field: extradata
      type_field: jsonb

CDR-Pusher always needs a Primary Key to import CDRs, therefore if you use MySQL, please ensure that you have a Primary Key in your cdr table as it will not be there by default.

You can create a Primary Key with:

ALTER TABLE cdr ADD COLUMN id int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;

Send CDRs from backend to the CDR-Stats Core DB

The application cdr-pusher will need your correct CDR-Stats server settings to push CDRs properly to the core DB, you set this in ‘/etc/cdr-pusher.yaml’ by changing:

pg_datasourcename: "user=postgres password=password host=localhost port=5432 dbname=cdr-pusher sslmode=disable"

Replace ‘postgres’, ‘password’ and ‘localhost’ by your CDR-Stats server settings and make sure you configured Remote Access to PostgreSQL, this is described in our documentation here Configure Postgresql for Remote Access.

You may need to configure these settings as well:

# switch_ip: leave this empty to default to your external IP (accepted value: ""|"your IP")
switch_ip: ""

# cdr_source_type: write the id of the cdr sources type
# (accepted value: unknown: 0, csv: 1, api: 2, freeswitch: 3, asterisk: 4, yate: 5, kamailio: 6, opensips: 7, sipwise: 8, veraz: 9)
cdr_source_type: 4

Restart CDR-Pusher

After changes in ‘/etc/cdr-pusher.yaml’ CDR-pusher will need to be restarted, do this with the following command:

$ /etc/init.d/supervisor stop
$ /etc/init.d/supervisor start