ODBC using cdr_odbc

Compile, configure, and install the latest unixODBC package:

• 	tar -zxvf unixODBC-2.2.9.tar.gz &&
  	cd unixODBC-2.2.9 &&
  	./configure --sysconfdir=/etc --prefix=/usr --disable-gui &&
  	make &&
  	make install
  

Compile, configure, and install the latest FreeTDS package:

• 	tar -zxvf freetds-0.62.4.tar.gz &&
  	cd freetds-0.62.4 &&
  	./configure --prefix=/usr --with-tdsver=7.0 \
                   --with-unixodbc=/usr/lib &&
  	make && make install
  

Compile, or recompile, asterisk so that it will now add support for cdr_odbc.

• 	make clean && ./configure --with-odbc &&
  	make update &&
  	make &&
  	make install
  

Setup odbc configuration files. These are working examples from my system. You will need to modify for your setup. You are not required to store usernames or passwords here.

• 	/etc/odbcinst.ini
  	   [FreeTDS]
  	   Description    = FreeTDS ODBC driver for MSSQL
  	   Driver         = /usr/lib/libtdsodbc.so
  	   Setup          = /usr/lib/libtdsS.so
  	   FileUsage      = 1
  
  	/etc/odbc.ini
  	   [MSSQL-asterisk]
  	   description         = Asterisk ODBC for MSSQL
  	   driver              = FreeTDS
  	   server              = 192.168.1.25
  	   port                = 1433
  	   database            = voipdb
  	   tds_version         = 7.0
  	   language            = us_english
  

Only install one database connector. Do not confuse asterisk by using both ODBC (cdr_odbc) and FreeTDS (cdr_tds). This command will erase the contents of cdr_tds.conf

• 		[ -f /etc/asterisk/cdr_tds.conf ] > /etc/asterisk/cdr_tds.conf
  

NOTE: unixODBC requires the freeTDS package, but asterisk does not call freeTDS directly.

Now set up cdr_odbc configuration files. These are working samples from my system. You will need to modify for your setup. Define your usernames and passwords here, secure file as well.

• 		/etc/asterisk/cdr_odbc.conf
  		   [global]
  		   dsn=MSSQL-asterisk
  		   username=voipdbuser
  		   password=voipdbpass
  		   loguniqueid=yes
  

And finally, create the 'cdr' table in your mssql database.

• 		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] (150)         NOT NULL ,
  		        [userfield]     [varchar] (255)         NOT NULL
  		)
  

Start asterisk in verbose mode, you should see that asterisk logs a connection to the database and will now record every call to the database when it's complete.