Call detail records (CDRs) contain information about calls that have passed through your Asterisk system. They are discussed further in Chapter 24, System Monitoring and Logging. Storing CDRs 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.

[options]
systemname=toronto

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 permits you to add additional columns that you can populate with the CDR() dialplan function. You can even store different parts of CDR data to different tables and databases, if that is required.

More information about the standard CDR columns in Asterisk is available in Table 24.2, “Default CDR fields”. 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 additional 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 your table in the database (which we’ll assume you’ve called cdr), you need to 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

* 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 into 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       | 
+--------------+----------+---------+------------+

You now have enough information to calculate how much the call should have cost you, which enables you to either bill customers or check your records against what the phone company is sending you, so you can do monthly auditing of your phone bills.

[mysql_connection]
connection=asterisk_mysql
table=cdr

[mssql_connection]
connection=production_mssql
table=call_records

[mssql_connection]
connection=production_mssql
table=call_records
alias src => Source
alias dst => Destination
alias accountcode => AccountCode
alias billsec => BillableTime

[logging_for_device_0000FFFF0008]
connection=asterisk_mysql
table=cdr_for_0000FFFF0008
filter src => 0000FFFF0008

[mysql_connection]
connection=asterisk_mysql
table=cdr

[filtered_mysql_connection]
connection=asterisk_mysql
table=cdr
filter src => 0000FFFF0008
static "DoNotCharge" => accountcode