thr3ads.net - asterisk users - [asterisk-users] CDR in an MySQL-Database [Jul 2015]

If this information is useful, please help other people find it:
Share via:

Luca Bertoncello

2015-Jul-06 19:22 UTC

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

2015-Jul-06 20:14 UTC

head link

[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

Ryan, Travis

2015-Jul-06 20:20 UTC

head link

[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

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

John Novack

2015-Jul-07 00:32 UTC

head link

[asterisk-users] CDR in an MySQL-Database

Luca Bertoncello wrote:> 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)
>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

Luca Bertoncello

2015-Jul-07 05:15 UTC

head link

[asterisk-users] CDR in an MySQL-Database

John Novack <jnovack at stromberg-carlson.org> schrieb:
> 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?
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 at lucabert.de)

Seemingly Similar Threads

Search for more seemingly similar threads

asterisk users - Jul 2015 - CDR in an MySQL-Database

[asterisk-users] CDR in an MySQL-Database

[asterisk-users] CDR in an MySQL-Database

[asterisk-users] CDR in an MySQL-Database

[asterisk-users] CDR in an MySQL-Database

[asterisk-users] CDR in an MySQL-Database

Seemingly Similar Threads