[asterisk-users] CDR in an MySQL-Database

[Ryan, Travis]

-----Original Message-----
From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Ryan, Travis
Sent: Monday, July 06, 2015 4:20 PM
To: 'Asterisk Users Mailing List - Non-Commercial Discussion'
Subject: Re: [asterisk-users] CDR in an MySQL-Database



-----Original Message-----
From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of jg
Sent: Monday, July 06, 2015 4:14 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [asterisk-users] CDR in an MySQL-Database


> Hi list!
>
> I'd like to save all information about calls (CDR) in a MySQL-Database.
> I created the DB and a user for Asterisk on a separate server, then I
> configured my cdr_mysql.conf so:
>
> [global]
> hostname=192.168.10.3
> dbname=asterisk
> table=cdr
> password=MYSECRET
> user=asterisk
> port=3306
>
> and my cdr.conf so:
>
> [general]
> enable=yes
> unanswered = yes
> safeshutdown=yes
>
> [mysql]
> usegmtime=no
> loguniqueid=yes
> loguserfield=yes
> accountlogs=yes
>
> I created the table in the DB so:
>
> CREATE TABLE IF NOT EXISTS `cdr` (
>    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
>    `calldate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
>    `clid` varchar(80) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `src` varchar(80) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `dst` varchar(80) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `dcontext` varchar(80) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `lastapp` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `lastdata` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `duration` float unsigned DEFAULT NULL,
>    `billsec` float unsigned DEFAULT NULL,
>    `disposition` enum('ANSWERED','BUSY','FAILED','NO ANSWER','CONGESTION')
> COLLATE utf8_bin DEFAULT NULL, `channel` varchar(50) COLLATE utf8_bin DEFAULT
> NULL, `dstchannel` varchar(50) COLLATE utf8_bin DEFAULT NULL,
>    `amaflags` varchar(50) COLLATE utf8_bin DEFAULT NULL,
>    `accountcode` varchar(20) COLLATE utf8_bin DEFAULT NULL,
>    `uniqueid` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
>    `userfield` float unsigned DEFAULT NULL,
>    `answer` datetime NOT NULL,
>    `end` datetime NOT NULL,
>    PRIMARY KEY (`id`),
>    KEY `calldate` (`calldate`),
>    KEY `dst` (`dst`),
>    KEY `src` (`src`),
>    KEY `dcontext` (`dcontext`),
>    KEY `clid` (`clid`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
>
> Then I restarted Asterisk (core restart now).
> Unfortunately it does not work, since I get on boot:
>
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1468 load_mysql_config: MySQL RealTime: No database user found, using 'asterisk' as default.
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1474 load_mysql_config: MySQL RealTime: No database password found, using 'asterisk' as default.
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1480 load_mysql_config: MySQL RealTime: No database host found, using localhost via socket.
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1486 load_mysql_config: MySQL RealTime: No database name found, using 'asterisk' as default.
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1492 load_mysql_config: MySQL RealTime: No database port found, using 3306 as default.
> [Jul  6 21:20:39] WARNING[9735]: res_config_mysql.c:1509 load_mysql_config: MySQL RealTime: No database socket found (and unable to detect a suitable path).
>
> And of course:
>
> OpenWrt*CLI> cdr show status
>
> Call Detail Record (CDR) settings
> ----------------------------------
>    Logging:                    Enabled
>    Mode:                       Simple
>    Log unanswered calls:       Yes
>
> * Registered Backends
>    -------------------
>      cdr-custom
>
> Asterisk 1.8 runs on an OpenWRT-Switch.
> Any idea?
>
> Thanks
> Luca Bertoncello
> (lucabert at lucabert.de)
>
Did you study this: http://www.asteriskdocs.org/ ?

jg

-- 


I was only able to make it work with ODBC. Looks like you're trying to do realtime, and I'm not familiar. I have it working just fine with ODBC for the CDR and realtime for everything else. There are more steps to getting odbc to work, but apparently Digium only really supports the ODBC version of things, and not the community supported realtime/native MySQL setup.




I think I just used the following to guide me.

http://www.voip-info.org/wiki/view/Asterisk+cdr+odbc


Travis