Storing Call Detail Records (CDR)
Storing Call Detail Records (CDR)
Call detail records (CDR) contain information about calls that have passed through your Asterisk system. Storing CDR is a popular
use of databases in Asterisk, because it makes them easier to manage (for example, you can keep track of many Asterisk systems in a single table). Also, by placing records into a database you open up many possibilities, including building your own web interface for tracking statistics such as call usage and most-called locations, billing, or phone company invoice verification. We are inclined to suggest that, because storing CDR in a database is so useful, you should always implement CDR storage to a database on any production system (you can always store CDR to a file as well, so there’s nothing lost).
The best way to store your call detail records is via the cdr_adaptive_odbc module. This module allows you to choose which columns of data built into Asterisk are stored in your table, and it permits you to add additional columns that can be populated with
the CDR() dialplan function. You can even store different parts of CDR data to different tables and databases.
here is a sample SQL command to create the default columns:
CREATE TABLE cdr (
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’
);
You can define all or any subset of these records in the database, and Asterisk will work around what is available. You can also add more columns to store other data relevant to the calls. For example, if you wanted to implement least-cost routing (LCR), you could add columns for route, per-minute cost, and per-minute rate. Once you’ve added those columns, they can be populated via the dialplan by using the CDR() function (e.g., Set(CDR(per_minute_rate)=0.01)).
After creating the table named cdr in the database, you must configure the cdr_adaptive_odbc.conf file in the /etc/asterisk folder. The following example will utilize the asterisk connection we’ve defined in res_odbc.conf and store the data in the cdr table:
; cdr_adaptive_odbc.conf
[adaptive_connection]
connection=asterisk
table=cdr
Yes, really, that’s all you need. After configuring cdr_adaptive_odbc.conf, just reload the cdr_adaptive_odbc.so module from the Asterisk console by running module reload cdr_adaptive_odbc.so. You can verify that the Adaptive ODBC backend has been loaded by running cdr show status:
*CLI> cdr show status
Call Detail Record (CDR) settings
———————————-
Logging: Enabled
Mode: Simple
Log unanswered calls: No
Log congestion: No
* Registered Backends
——————-
cdr-syslog
Adaptive ODBC
cdr-custom
csv
cdr_manager
Now place a call that gets answered (e.g., using Playback() , or Dial() ing another channel and answering it). You should get some CDRs stored into your database. You can check by running SELECT * FROM CDR; from your database console.
With the basic CDR information stored in the database, you might want to add some additional information to the cdr table, such as the route rate. You can use the ALTER TABLE directive to add a column called route_rate to the table:
sql> ALTER TABLE cdr ADD COLUMN route_rate varchar(10);
Now reload the cdr_adaptive_odbc.so module from the Asterisk console:
*CLI> module reload cdr_adaptive_odbc.so
and populate the new column from the Asterisk dialplan using the CDR() function, like so:
exten => _NXXNXXXXXX,1,Verbose(1,Example of adaptive ODBC usage)
same => n,Set(CDR(route_rate)=0.01)
same => n,Dial(SIP/my_itsp/${EXTEN})
same => n,Hangup()
After the alteration to your database and dialplan, you can place a call and then look at
your CDRs. You should see something like the following:
+———————+———-+———+————+
| src | duration | billsec | route_rate |
+———————+———-+———+————+
| 0000FFFF0008 | 37 | 30 | 0.01 |
+———————+———-+———+————+
In reality, storing rating in the call record might not be ideal (CDR is typically used as a raw resource, and things such as rates are added downstream by billing software). The ability to add custom fields to CDR is very useful, but be careful not to use your call records to replace a proper billing platform. Best to keep your CDR clean and do further processing downstream.
