Asterisk cdr odbc

Asterisk CDR with almost any database

There is a module called cdr_odbc that supports the unixODBC library. ODBC is a standard interface between an application and a database. The unixODBC library supports many databases, from Mysql and MS SQL Server to text files. The cdr_odbc also adds functionality for a more stable connection to the database: If the database connection is lost, cdr_odbc automatically reconnects.

Note: There is a backport for Asterisk 1.4 of the Adaptive CDR ODBC that was introduced with Asterisk 1.6. This feature permits to write to arbitrary column names to the CDR table, i.e. you can invent your own additional fields as you like.

Install ODBC

First, you need unixODBC installed. In the past, lots of people have had problems with use ODBC from RPMs, so when possible, please use the ODBC source package. You can install it with the –disable-gui if you don’t have/want X.

On Debian or Ubuntu systems, the standard packages should work fine (“sudo apt-get install unixodbc”).

unixODBC install instructions:

cd /usr/src
tar zxf unixODBC-2.2.7.tar.gz
cd unixODBC-2.2.7
./configure –disable-gui
make
make install

Using ODBC with MySQL

Now you need to configure your datasource. If you choose to use MySQL you need to install MyODBC (Debian: “apt-get install libmyodbc”).

MySSQL ODBC 3.51 Download Page

http://dev.mysql.com/downloads/connector/odbc/3.51.html

Configuring ODBC

Basic odbcinst.ini:

This is where you configure your various odbc drivers. On Debian systems, the drivers’ entries will be created automatically when you install the distribution-supplied odbc driver.

[MySQL]
Description = MySQL ODBC MyODBC Driver
Driver = /usr/lib/libmyodbc3.so
FileUsage = 1

[Text]
Description = ODBC for Text Files
Driver = /usr/lib/libodbctxt.so
Setup = /usr/lib/libodbctxtS.so
FileUsage = 1
CPTimeout =
CPReuse =

[PostgreSQL]
Description = PostgreSQL driver for Linux & Win32
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1

[DB2]
Description = DB2 Driver
Driver = /opt/IBM/db2/V8.1/lib64/libdb2.so
FileUsage = 1
DontDLClose = 1
DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={DB2INSTANCE=db2inst1}

Note: you may have to change the path to the .so files depending on where they are installed.
Default location for Suse 10: /usr/lib/unixODBC

Basic odbc.ini:

This is where you configure your datasources.

[MySQL-asterisk]
Description = MySQL ODBC Driver Testing
Driver = MySQL
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = username
Password = password
Database = database
Option = 3

1. Port =

[Text-asterisk]
Description = ODBC for Text Files test
Driver = Text
Database = /root/flatfile
Trace = Yes
Tracefile = /root/trace.log

[PostgreSQL-asterisk]
Description = Test to Postgres
Driver = PostgreSQL
Trace = Yes
TraceFile = sql.log
Database = asterisk
Servername = localhost
UserName = username
Password = password
Port = 5432
Protocol = 7.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =

[DB2-asterisk]
Database = astdb
Driver = DB2

Configuring Asterisk

cdr_odbc.conf:

[global]
dsn=MySQL-asterisk
username=username
password=password
loguniqueid=yes ;; Required to use the userfield
dispositionstring=yes ;; Required to use disposition like ANSWERED and FAILED

Note: If you don’t use dispositionstring=yes, see the relationship above:
ANSWERED=8, NO ANSWER=4, BUSY=2, FAILED=1 and 0 for none disposition (maybe when the caller hangup the call before somebody pickup the call).

Setting up the CDR Database/Table

IMPORTANT adaptive_odbc information: The tables below will cause you problems with the calldate field. In cdr_adaptive_odbc, call setup, answer, and teardown are stored in each of three different fields; start, answer, and end, and the calldate field is not used. The “default now()” will, of course, auto-populate the calldate and unless you are using usegmtime, you won’t notice it until something really breaks. Additionally, “end” is a keyword to some databases (namely PostgreSQL) and the adaptive code does not quote it, causing the query to die and tearing down the entire odbc connection. The work-around for this is to alias end to calldate, which you will want to do anyway if you want to maintain the functionality of the old cdr_odbc.

Table Setup for Specific DB’s:

Postgres:

CREATE TABLE cdr (
calldate timestamp with time zone NOT NULL default now(),
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 bigint NOT NULL default ‘0’,
billsec bigint NOT NULL default ‘0’,
disposition varchar(45) NOT NULL default ”,
amaflags bigint NOT NULL default ‘0’,
accountcode varchar(20) NOT NULL default ”,
uniqueid varchar(32) NOT NULL default ”,
userfield varchar(255) NOT NULL default ”
);

Mysql:

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 ”
);

Microsoft SQL:

CREATE TABLE cdr ( 
 [calldate]      [datetime]              NOT NULL ,
 [clid]          [varchar] (80)          NOT NULL ,
 [src]           [varchar] (80)          NOT NULL ,
 [dst]           [varchar] (80)          NOT NULL ,
 [dcontext]      [varchar] (80)          NOT NULL ,
 [channel]       [varchar] (80)          NOT NULL ,
 [dstchannel]    [varchar] (80)          NOT NULL ,
 [lastapp]       [varchar] (80)          NOT NULL ,
 [lastdata]      [varchar] (80)          NOT NULL ,
 [duration]      [int]                   NOT NULL ,
 [billsec]       [int]                   NOT NULL ,
 [disposition]   [varchar] (45)          NOT NULL ,
 [amaflags]      [int]                   NOT NULL ,
 [accountcode]   [varchar] (20)          NOT NULL ,
 [uniqueid]      [varchar] (32)          NOT NULL ,
 [userfield]     [varchar] (255)         NOT NULL
);

DB2:

CREATE TABLE cdr (
calldate timestamp NOT NULL default current timestamp,
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 bigint NOT NULL default 0,
billsec bigint NOT NULL default 0,
disposition varchar(45) NOT NULL default ”,
amaflags bigint NOT NULL default 0,
accountcode varchar(20) NOT NULL default ”,
uniqueid varchar(32) NOT NULL default ”,
userfield varchar(255) NOT NULL default ”
);

…..More info coming.

See also:

• Asterisk cdr mysql
• Asterisk billing

• Asterisk | FAQ | Tips & Tricks