VoIP Mailing List Archives :: View topic - [asterisk-users] CDR in an MySQL-Database

Sponsor: VoiceMeUp - Corporate & Wholesale VoIP Services

VoIP Mailing List Archives
Mailing list archives for the VoIP community

[asterisk-users] CDR in an MySQL-Database

VoIP Mailing List Archives Forum Index -> Asterisk Users

View previous topic :: View next topic

Author

Message

lucabert at lucabert.de
Guest

Posted: Mon Jul 06, 2015 2:22 pm Post subject: [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: OpenWrtCLI> 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@lucabert.de) -- _____________________________________________________________________ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users

webaccounts173 at jgoe...
Guest

Posted: Mon Jul 06, 2015 3:14 pm Post subject: [asterisk-users] CDR in an MySQL-Database

Quote:

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@lucabert.de)

RyanT at OscarWinski.com
Guest

Posted: Mon Jul 06, 2015 3:20 pm Post subject: [asterisk-users] CDR in an MySQL-Database

-----Original Message-----
From: asterisk-users-bounces@lists.digium.com [mailto:asterisk-users-bounces@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

Quote:

Did you study this: http://www.asteriskdocs.org/ ?

jg

--
_____________________________________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --
New to Asterisk? Join us for a live introductory webinar every Thurs:
http://www.asterisk.org/hello

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users

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.

--
_____________________________________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --
New to Asterisk? Join us for a live introductory webinar every Thurs:
http://www.asterisk.org/hello

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users

RyanT at OscarWinski.com
Guest

Posted: Mon Jul 06, 2015 3:23 pm Post subject: [asterisk-users] CDR in an MySQL-Database

-----Original Message-----
From: asterisk-users-bounces@lists.digium.com [mailto:asterisk-users-bounces@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@lists.digium.com [mailto:asterisk-users-bounces@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

Quote:

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

--
_____________________________________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --
New to Asterisk? Join us for a live introductory webinar every Thurs:
http://www.asterisk.org/hello

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users

jnovack at stromberg-c...
Guest

Posted: Mon Jul 06, 2015 7:33 pm Post subject: [asterisk-users] CDR in an MySQL-Database

Luca Bertoncello wrote:

Quote:

Been doing this with MySql for the last 10 years, though not on an openWrt machine
MySql is on the Asterisk machine.
Also have additional database tables to block by callerId and name
Did have some issues with the dialplan syntax when moving from 1.4 to 11, but it just works
I assume OpenWRT is a pre compiled Asterisk package?
You may not have the proper configuration to use MySql
Your error message(s) seem to say it expects to find the MySql server on "localhost" but you say it is on a different machine!!
perhaps you need to fix that first?

John Novack

--

Dog is my Co-pilot

--
_____________________________________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --
New to Asterisk? Join us for a live introductory webinar every Thurs:
http://www.asterisk.org/hello

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users

lucabert at lucabert.de
Guest

Posted: Tue Jul 07, 2015 12:15 am Post subject: [asterisk-users] CDR in an MySQL-Database

John Novack <jnovack@stromberg-carlson.org> schrieb:

Quote:

I think, the team of OpenWRT did NOT prepare the CDR-MySQL-Module, since I
could not find cdr_addon_mysql.so...
I "resolved" writing the data in a CSV, and then importing the data in the
MySQL-DB with a script...

Thanks
Luca Bertoncello
(lucabert@lucabert.de)

--
_____________________________________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --
New to Asterisk? Join us for a live introductory webinar every Thurs:
http://www.asterisk.org/hello

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users

Display posts from previous:

	VoIP Mailing List Archives Forum Index -> Asterisk Users	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum