Search:
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.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").MySQL ODBC Install Help
http://dev.mysql.com/doc/connector/odbc/en/faq_2.htmlMySSQL ODBC 3.51 Download Page
http://dev.mysql.com/downloads/connector/odbc/3.51.htmlIODBC (Required by MySQL ODBC)
http://www.iodbc.org/index.php?page=downloads/indexConfiguring 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
#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
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 (
);
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 | FAQ | Tips & Tricks
Created by: bkw,Last modification on Wed 28 of Oct, 2009 [22:05 UTC] by fsilvestre
Page Changes
