This is work in progress.
This is a placeholder for my (mickecarlsson) notes using cdr_adaptive_odbc.conf and FreePBX Distro
As the distro does not include all packages (yet) per default some steps are necessary to make this work
This page is not referenced from any wiki start page as I want to keep it so until it is tested thoroughly.
yum install libtool-ltdl-devel unixODBC unixODBC-devel mysql-connector-odbc
First, check where your odbc.ini is with odbcinst -j:
unixODBC 2.2.11 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini USER DATA SOURCES..: /root/.odbc.ini
Edit /etc/odbcinst.ini to like something like this, and make sure that driver and setup files exists:
# Driver from the MyODBC package # Setup from the unixODBC package [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc3.so Setup = /usr/lib/libodbcmyS.so FileUsage = 1
Note: Double check that the files referenced actually exists
Edit /etc/odbc.ini and add:
[MySQL-asteriskcdrdb] Description = MySQL ODBC Driver Testing Driver = MySQL Socket = /var/lib/mysql/mysql.sock Server = localhost Database = asteriskcdrdb Option = 3
Edit /etc/asterisk/res_odbc.conf so that it looks something like this:
[odbccdr] enabled => yes dsn => MySQL-asteriskcdrdb username => <your username for MySQL, same as in section AMPDBUSER in Advanced Settings > password => <your password for MySQL same as in section AMPDBPASS in Advanced Settings >
Test that your setup is working:
odbcinst -s -q
You should get something like this:
Now test that you can access the database via odbc:
isql -v MySQL-asteriskcdrdb <your database user name> <your database password>
You should get something like this:
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
Type quit to exit.
Add Index to cdr to speed up selection in CDR Viewer:
ALTER TABLE `cdr` ADD INDEX ( `calldate` ); ALTER TABLE `cdr` ADD INDEX ( `dst` ); ALTER TABLE `cdr` ADD INDEX ( `accountcode` );
Add did to the table (this is added in 2.10):
use asteriskcdrdb; ALTER TABLE cdr ADD did VARCHAR(20) NOT NULL DEFAULT '';
Edit /etc/asterisk/cdr_adaptive_odbc.conf and add:
[first] connection=odbccdr table=cdr alias start => calldate
Note 1: line with connection= must match section in res_odbc.conf, in this case [odbccdr]
Note 2: Due to the change in cdr_adaptive_odbc.so driver, calldate does not exists any more
It is replaced by three fields:
start = Date and time when call started answer = Date and time when call was answered end = Date and time when call was ended
If you want to have the behavior of the standard cdr functions, just add alias start => calldate to the config file
Note. You want to do this!
(this is from http://www.voip-info.org/wiki/view/Asterisk+cdr+odbc)
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 start to calldate, which you will want to do anyway if you want to maintain the functionality of the old cdr_odbc.
Help from Asterisk:
pbx-lab*CLI> core show function CDR
-= Info about function 'CDR' =-
Gets or sets a CDR variable.
All of the CDR field names are read-only, except for 'accountcode', 'user field', and 'amaflags'. You may, however, supply a name not on the above list, and create your own variable, whose value can be changed with this function, and this variable will be stored on the cdr.
NOTE: For setting CDR values, the 'l' flag does not apply to setting the 'accountcode', 'userfield', or 'amaflags'.
Raw values for 'disposition':
0 - NO ANSWER
1 - NO ANSWER (NULL record)
2 - FAILED
4 - BUSY
8 - ANSWERED
Raw values for 'amaflags':
1 - OMIT
2 - BILLING
3 - DOCUMENTATION
Example: exten => 1,1,Set(CDR(userfield)=test)
CDR field name:
clid - Caller ID.
lastdata - Last application arguments.
disposition - ANSWERED, NO ANSWER, BUSY, FAILED.
src - Source.
start - Time the call started.
amaflags - DOCUMENTATION, BILL, IGNORE, etc.
dst - Destination.
answer - Time the call was answered.
accountcode - The channel's account code.
dcontext - Destination context.
end - Time the call ended.
uniqueid - The channel's unique id.
dstchannel - Destination channel.
duration - Duration of the call.
userfield - The channel's user specified field.
lastapp - Last application.
billsec - Duration of the call once it was answered.
channel - Channel name.
sequence - CDR sequence number.
f: Returns billsec or duration fields as floating point values.
l: Uses the most recent CDR on a channel with multiple records
r: Searches the entire stack of CDRs on the channel.
s: Skips any CDR's that are marked 'LOCKED' due to forkCDR() calls.
(on setting/writing CDR vars only)
u: Retrieves the raw, unprocessed value.
For example, 'start', 'answer', and 'end' will be retrieved as epoch
values, when the 'u' option is passed, but formatted as YYYY-MM-DD HH:MM:SS otherwise. Similarly, disposition and amaflags will return their raw