cdr_odbc and res_odbc on asterisk 10

simion · August 1, 2012, 9:21am

Hello,
could anybody please tell me where can I get modules cdr_odbc and res_odbc for my asterisk 10 installed from svn to debian 6?

dejanst · August 2, 2012, 6:49am

If the dependency’s are installed on your server, odbc modules should be visible/available in the “make menuselect” part of the Asterisk install/compile process.

simion · August 6, 2012, 11:22am

Thanks, I’ve installed dependencies and modules.
But there is no cdr’s in the DB.

[code]*CLI> odbc show

ODBC DSN Settings

Name: mysql
DSN: MySQL-asterisk
Last connection attempt: 1969-12-31 19:00:00
Pooled: No
Connected: Yes
[/code]

*CLI> module show like odbc Module Description Use Count cdr_adaptive_odbc.so Adaptive ODBC CDR backend 0 cdr_odbc.so ODBC CDR Backend 0 cel_odbc.so ODBC CEL backend 0 func_odbc.so ODBC lookups 0 res_config_odbc.so Realtime ODBC configuration 0 res_odbc.so ODBC resource 0 6 modules loaded

~# cat /etc/asterisk/cdr.conf | grep -v '^;' | grep -v '^$' [general] [global] dsn=MySQL-asterisk username=asterisk_user password=Some_Pass_Aster01 loguniqueid=yes dispositionstring=yes table=cdr ;"cdr" is default table name usegmtime=no [csv] usegmtime=yes ; log date/time in GMT. Default is "no" loguniqueid=yes ; log uniqueid. Default is "no" loguserfield=yes ; log user field. Default is "no" accountlogs=yes ; create separate log file for each account code. Default is "yes" [odbc] usegmtime=yes loguniqueid=yes loguserfiled=yes

~# cat /etc/asterisk/res_odbc.conf | grep -v '^;' | grep -v '^$' [ENV] [asterisk] enabled => no dsn => asterisk pre-connect => yes [mysql2] enabled => no dsn => MySQL-asterisk username => myuser password => mypass pre-connect => yes [sqlserver] enabled => no dsn => mickeysoft share_connections => no limit => 5 username => oscar password => thegrouch pre-connect => yes sanitysql => select count(*) from systables backslash_is_escape => no [mysql] enabled => yes dsn => MySQL-asterisk pre-connect => yes
Could anybody please help me to understand why asterisk is not ‘writing’ CDR’s to DB?

azhar117 · August 6, 2012, 11:49am

For using with mysql use cdr_mysql.conf in this specify the settings.
[global]
hostname=localhost
dbname=dbname
table=CDR
password=asder
user=root
port=3306
sock=/var/lib/mysql/mysql.sock

and uncomment the below columns if u required

[columns]
static “” =>
alias =>
alias start => calldate
alias clid => <a_field_not_named_clid>
alias src => <a_field_not_named_src>
alias dst => <a_field_not_named_dst>
alias dcontext => <a_field_not_named_dcontext>
alias channel => <a_field_not_named_channel>
alias dstchannel => <a_field_not_named_dstchannel>
alias lastapp => <a_field_not_named_lastapp>
alias lastdata => <a_field_not_named_lastdata>
alias duration => <a_field_not_named_duration>
alias billsec => <a_field_not_named_billsec>
alias disposition => <a_field_not_named_disposition>
alias amaflags => <a_field_not_named_amaflags>
alias accountcode => <a_field_not_named_accountcode>
alias userfield => <a_field_not_named_userfield>
alias uniqueid => <a_field_not_named_uniqueid>

THis might help u

simion · August 6, 2012, 12:30pm

Thanks, doesn’t work for me.

mysql> select * from cdr; Empty set (0.00 sec)

azhar117 · August 6, 2012, 2:52pm

did u create a cdr table in ur database use the following queries

CREATE DATABASE asterisk;

GRANT INSERT
ON asterisk.*
TO asterisk@localhost
IDENTIFIED BY ‘yourpassword’;

USE asterisk;

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 ‘’,
userfield varchar(255) NOT NULL default ‘’,
uniqueid VARCHAR(32) NOT NULL default ‘’,
linkedid VARCHAR(32) NOT NULL default ‘’,
sequence VARCHAR(32) NOT NULL default ‘’,
peeraccount VARCHAR(32) NOT NULL default ‘’
);

and check it

simion · August 8, 2012, 8:58am

thanks for replies, stil not working for me
1st of all in my configuration:

root@faxserver-prospero-2:~# mysql -u asterisk_user -pSome_Pass_Aster01 cdr2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from cdr;
Empty set (0.00 sec)

mysql> insert into cdr(id) values (1);
Query OK, 1 row affected (0.02 sec)

mysql> select * from cdr;
+----+---------------------+------+-----+-----+----------+---------+------------+---------+----------+----------+---------+-------------+----------+-------------+----------+-----------+
| id | calldate            | clid | src | dst | dcontext | channel | dstchannel | lastapp | lastdata | duration | billsec | disposition | amaflags | accountcode | uniqueid | userfield |
+----+---------------------+------+-----+-----+----------+---------+------------+---------+----------+----------+---------+-------------+----------+-------------+----------+-----------+
|  1 | 0000-00-00 00:00:00 |      |     |     |          |         |            |         |          |        0 |       0 |             |        0 |             |          |           |
+----+---------------------+------+-----+-----+----------+---------+------------+---------+----------+----------+---------+-------------+----------+-------------+----------+-----------+
1 row in set (0.00 sec)

it means that sql tables is avaliable for reading and writing data

root@faxserver-prospero-2:~# isql -v MySQL-asterisk
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

SQL> insert into cdr(id) values (2);
SQLRowCount returns 1
SQL> ^C
root@faxserver-prospero-2:~# mysql -u asterisk_user -pSome_Pass_Aster01 cdr2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from cdr;
+----+---------------------+------+-----+-----+----------+---------+------------+---------+----------+----------+---------+-------------+----------+-------------+----------+-----------+
| id | calldate            | clid | src | dst | dcontext | channel | dstchannel | lastapp | lastdata | duration | billsec | disposition | amaflags | accountcode | uniqueid | userfield |
+----+---------------------+------+-----+-----+----------+---------+------------+---------+----------+----------+---------+-------------+----------+-------------+----------+-----------+
|  1 | 0000-00-00 00:00:00 |      |     |     |          |         |            |         |          |        0 |       0 |             |        0 |             |          |           |
|  2 | 0000-00-00 00:00:00 |      |     |     |          |         |            |         |          |        0 |       0 |             |        0 |             |          |           |
+----+---------------------+------+-----+-----+----------+---------+------------+---------+----------+----------+---------+-------------+----------+-------------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

and odbc is configured correctly

simion · August 8, 2012, 9:10am

I’ve tried:

[quote=“azhar117”]did u create a cdr table in ur database use the following queries

CREATE DATABASE asterisk;
…
and check it[/quote]

sql related things:

root@faxserver-prospero-2:~# mysql -u root -p*
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use asterisk;
ERROR 1049 (42000): Unknown database 'asterisk'
mysql> CREATE DATABASE asterisk;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT all
    -> ON asterisk.*
    -> TO asterisk@localhost
    -> IDENTIFIED BY 'yourpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> USE asterisk;
Database changed
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 '',
    -> `userfield` varchar(255) NOT NULL default '',
    -> `uniqueid` VARCHAR(32) NOT NULL default '',
    -> `linkedid` VARCHAR(32) NOT NULL default '',
    -> `sequence` VARCHAR(32) NOT NULL default '',
    -> `peeraccount` VARCHAR(32) NOT NULL default ''
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql> flush privileges;
mysql> ^CCtrl-C -- exit!
Aborted

odbc related

root@faxserver-prospero-2:~# vim /etc/odbc.ini
root@faxserver-prospero-2:~# vim /etc/odbcinst.ini
root@faxserver-prospero-2:~# isql -v MySQL-asterisk
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> insert into cdr(clid) values (1);
SQLRowCount returns 1
SQL> ^C

- related

root@faxserver-prospero-2:~# vim /etc/asterisk/cdr.conf
root@faxserver-prospero-2:~# /etc/init.d/asterisk restart
Stopping Asterisk PBX: asterisk.
Starting Asterisk PBX: asteriskParsing /etc/asterisk/extconfig.conf
.
root@faxserver-prospero-2:~# asterisk -r
Asterisk 10.5.1, Copyright (C) 1999 - 2012 Digium, Inc. and others.
Created by Mark Spencer <markster@digium.com>
Asterisk comes with ABSOLUTELY NO WARRANTY; type 'core show warranty' for details.
This is free software, with components licensed under the GNU General Public
License version 2 and other licenses; you are welcome to redistribute it under
certain conditions. Type 'core show license' for details.
=========================================================================
  == Parsing '/etc/asterisk/extconfig.conf': Parsing /etc/asterisk/extconfig.conf
  == Found
Connected to Asterisk 10.5.1 currently running on faxserver-prospero-2 (pid = 20115)
Verbosity is at least 3
Core debug is at least 3
faxserver-prospero-2*CLI>
faxserver-prospero-2*CLI>
faxserver-prospero-2*CLI>
    -- Accepting call from '8123349219' to '112' on channel 0/10, span 1
    -- Executing [112@default:1] Answer("DAHDI/i1/8123349219-1", "") in new stack
    -- Executing [112@default:2] Wait("DAHDI/i1/8123349219-1", "1") in new stack
    -- Executing [112@default:3] Gosub("DAHDI/i1/8123349219-1", "rx-fax2,s,1(112,k@smartspb.net)") in new stack
    -- Executing [s@rx-fax2:1] NoOp("DAHDI/i1/8123349219-1", "**** FAX RECEIVE ****") in new stack
    -- Executing [s@rx-fax2:2] Set("DAHDI/i1/8123349219-1", "FAXOPT(ecm)=no") in new stack
    -- Executing [s@rx-fax2:3] Set("DAHDI/i1/8123349219-1", "FAXOPT(headerinfo)=SMART_FAX_SERVER") in new stack
    -- Executing [s@rx-fax2:4] Set("DAHDI/i1/8123349219-1", "FAXOPT(localstationid)=112") in new stack
    -- Executing [s@rx-fax2:5] Set("DAHDI/i1/8123349219-1", "FAX2EMAIL=k@*.net") in new stack
    -- Executing [s@rx-fax2:6] Set("DAHDI/i1/8123349219-1", "FAXOPT(maxrate)=14400") in new stack
    -- Executing [s@rx-fax2:7] Set("DAHDI/i1/8123349219-1", "FAXOPT(minrate)=2400") in new stack
    -- Executing [s@rx-fax2:8] Set("DAHDI/i1/8123349219-1", "FAXFILE=20120808-033405-8123349219") in new stack
    -- Executing [s@rx-fax2:9] Set("DAHDI/i1/8123349219-1", "FAXDIR=/var/spool/asterisk/fax_incoming/112/2012-08-08") in new stack
    -- Executing [s@rx-fax2:10] System("DAHDI/i1/8123349219-1", "/usr/bin/install -d /var/spool/asterisk/fax_incoming/112/2012-08-08") in new stack
    -- Executing [s@rx-fax2:11] ReceiveFAX("DAHDI/i1/8123349219-1", "/var/spool/asterisk/fax_incoming/112/2012-08-08/20120808-033405-8123349219.tiff,f") in new stack
    -- Channel 'DAHDI/i1/8123349219-1' receiving FAX '/var/spool/asterisk/fax_incoming/112/2012-08-08/20120808-033405-8123349219.tiff'
    -- Channel 'DAHDI/i1/8123349219-1' FAX session '0' started
    -- Channel 10 echo canceler disabled its NLP.
    -- Channel 10 detected a CED tone towards the network.
faxserver-prospero-2*CLI>
faxserver-prospero-2*CLI>
faxserver-prospero-2*CLI>
faxserver-prospero-2*CLI>
    -- FAX handle 0: [ 112.065230 ], entering CLOSING state
    -- FAX handle 0: [ 112.065274 ], entering CLOSING state
    -- Span 1: Channel 0/10 got hangup request, cause 16
faxserver-prospero-2*CLI> odbc show

ODBC DSN Settings
-----------------

  Name:   mysql
  DSN:    MySQL-asterisk
    Last connection attempt: 1969-12-31 19:00:00
  Pooled: No
  Connected: Yes

    -- Channel 'DAHDI/i1/8123349219-1' FAX session '0' is complete, result: 'SUCCESS' (FAX_SUCCESS), error: 'NO_ERROR', pages: 1, resolution: '204x196', transfer rate: '14400', remoteSID: '1111'
  == Spawn extension (rx-fax2, s, 11) exited non-zero on 'DAHDI/i1/8123349219-1'
    -- Executing [h@rx-fax2:1] AGI("DAHDI/i1/8123349219-1", "efax4asterisk.sh,20120808-033405-8123349219,8123349219,,1111,1,14400,204x196,SUCCESS,FAX_SUCCESS,NO_ERROR,k@*.net,/var/spool/asterisk/fax_incoming/112/2012-08-08") in new stack
    -- Launched AGI Script /var/lib/asterisk/agi-bin/efax4asterisk.sh
    -- <DAHDI/i1/8123349219-1>AGI Script efax4asterisk.sh completed, returning 0
    -- Executing [h@rx-fax2:2] NoOp("DAHDI/i1/8123349219-1", "FAXOPT(status) : SUCCESS") in new stack
    -- Executing [h@rx-fax2:3] NoOp("DAHDI/i1/8123349219-1", "FAXOPT(statusstr) : FAX_SUCCESS") in new stack
    -- Executing [h@rx-fax2:4] NoOp("DAHDI/i1/8123349219-1", "FAXOPT(error) : NO_ERROR") in new stack
    -- Hungup 'DAHDI/i1/8123349219-1'

faxserver-prospero-2*CLI> odbc show all

ODBC DSN Settings
-----------------

  Name:   mysql
  DSN:    MySQL-asterisk
    Last connection attempt: 1969-12-31 19:00:00
  Pooled: No
  Connected: Yes

faxserver-prospero-2*CLI>
Disconnected from Asterisk server
Executing last minute cleanups
Asterisk ending (0).

checking

root@faxserver-prospero-2:~# mysql -u root -p*
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT INSERT ON asterisk.* TO asterisk@localhost IDENTIFIED BY 'yourpassword';^CCtrl-C -- exit!
Aborted
root@faxserver-prospero-2:~# mysql -u root -p*
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use asterisk;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from cdr;
+---------------------+------+-----+-----+----------+---------+------------+---------+----------+----------+---------+-------------+----------+-------------+-----------+----------+----------+----------+-------------+
| calldate            | clid | src | dst | dcontext | channel | dstchannel | lastapp | lastdata | duration | billsec | disposition | amaflags | accountcode | userfield | uniqueid | linkedid | sequence | peeraccount |
+---------------------+------+-----+-----+----------+---------+------------+---------+----------+----------+---------+-------------+----------+-------------+-----------+----------+----------+----------+-------------+
| 0000-00-00 00:00:00 | 1    |     |     |          |         |            |         |          |        0 |       0 |             |        0 |             |           |          |          |          |             |
+---------------------+------+-----+-----+----------+---------+------------+---------+----------+----------+---------+-------------+----------+-------------+-----------+----------+----------+----------+-------------+
1 row in set (0.00 sec)

no error messages, but stil npt working

phpboy · October 14, 2013, 3:28pm

This is a very old post that doesn’t have the correct solution to this problem.
So for anybody that bumps into this here’s the correct answer…

The issue here was the dsn’s being used in the respective files were incorrectly specified.

cdr.conf dsn should point to res_odbc.conf which in turn should reference the dsn supplied in odbc.ini
I’ll illustrate it below (note I’m using two different dsn names for this illustration)

cdr_odbc.conf

[global] dsn=mysql2 table=cdr loguniqueid=yes ;; Required to use the userfield dispositionstring=yes ;; Required to use disposition like ANSWERED and FAILED

res_odbc.conf

[mysql2] enabled => yes dsn => MySQL-asterisk pre-connect => yes

odbc.ini

[MySQL-asterisk] Description = MySQL ODBC Driver = MySQL Socket = /var/lib/mysql/mysql.sock Server = localhost User = asteriskcdruser Password = my_password Database = asterisk Option = 3