Call Detail Records (CDR) contain information about calls that have passed through your Asterisk system. They are discussed further in Chapter 13, Managing Your Asterisk System. This is a popular use of databases in Asterisk because CDR can be easier to manage if you store the records in a database (for example, you could keep track of many Asterisk systems in a single table).

[options]
systemname=toronto

Let’s create a table in our database to store CDR. Log in to the PostgreSQL server with the psql application:

# psql -U asterisk -h localhost asterisk
Password:

And create the asterisk_cdr table:

asterisk=> CREATE TABLE asterisk_cdr
(
  id bigserial NOT NULL,
  calldate timestamptz,
  clid varchar(80),
  src varchar(80),
  dst varchar(80),
  dcontext varchar(80),
  channel varchar(80),
  dstchannel varchar(80),
  lastapp varchar(80),
  lastdata varchar(80),
  duration int8,
  billsec int8,
  disposition varchar(45),
  amaflags int8,
  accountcode varchar(20),
  uniqueid varchar(40),
  userfield varchar(255),
  CONSTRAINT asterisk_cdr_id_pk PRIMARY KEY (id)
) 
WITHOUT OIDS;

You can verify the table was created by using the \dt command (describe tables):

asterisk=> \dt asterisk_cdr
            List of relations
 Schema |     Name     | Type  |  Owner   
--------+--------------+-------+----------
 public | asterisk_cdr | table | asterisk
(1 row)

Next, configure Asterisk to store its CDR into the database. This is done in the /etc/asterisk/cdr_odbc.conf file with the following configuration:

[global]
dsn=asterisk-connector
username=asterisk
password=welcome
loguniqueid=yes
table=asterisk_cdr

If Asterisk is already running, from the Asterisk CLI execute module reload cdr_odbc.so. You can also just type reload, to reload everything.

*CLI> reload

Verify the status of CDR by entering the following command and looking for CDR registered backend: ODBC:

*CLI> cdr status
CDR logging: enabled
CDR mode: simple
CDR registered backend: cdr-custom
CDR registered backend: cdr_manager
CDR registered backend: ODBC

Now, perform a call through your Asterisk box and verify you have data in the asterisk_cdr table. The easiest way to test a call is with the Asterisk CLI command console dial (assuming that you have a sound card and chan_oss installed). However, you can utilize any method at your disposal to place a test call:

*CLI> console dial 100@default
-- Executing [100@default:1] Playback("OSS/dsp", "tt-weasels") in new stack
-- <OSS/dsp> Playing 'tt-weasels' (language 'en')

Then connect to the database and perform a SELECT statement to verify you have data in the asterisk_cdr table. You could also do SELECT * FROM asterisk_cdr;, but that will return a lot more data:

# psql -U asterisk -h localhost asterisk
Password:

asterisk=> SELECT id,dst,channel,uniqueid,calldate FROM asterisk_cdr;
 id | dst | channel |       uniqueid       |        calldate        
----+-----+---------+----------------------+------------------------
  1 | 100 | OSS/dsp | toronto-1171611019.0 | 2007-02-16 02:30:19-05
(1 rows)