Asterisk cdr odbc

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.

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
 

Now you need to configure your datasource. If you choose to use MySQL you need to install MyODBC (Debian: "apt-get install libmyodbc"). http://dev.mysql.com/doc/connector/odbc/en/faq_2.html http://dev.mysql.com/downloads/connector/odbc/3.51.html http://www.iodbc.org/index.php?page=downloads/index
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
 #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


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).

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