cdr_odbc and res_odbc on asterisk 10

[simion]

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]

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]

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

Code: Select all

*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: Select all

# isql -v MySQL-asterisk
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+


Code: Select all

*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


Code: Select all

~# 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


Code: Select all

~# 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]

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 "<value>" => <column>
alias <cdrvar> => <column>
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]

Thanks, doesn't work for me.

Code: Select all

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


[azhar117]

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]

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

Code: Select all

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

Code: Select all

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]

I've tried:

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

CREATE DATABASE asterisk;
........
and check it

1) sql related things:

Code: Select all

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



2) odbc related

Code: Select all

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



3) * related

Code: Select all

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




4) checking

Code: Select all

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