I'm trying to setup the exact same thing. I'm running standalone asterisk 11.7.0 and FOP2 2.27 on CentOS 6.4.
Already set up phonebook and recordings with MySql which works great. Only call history tab left.
As I understand it from this post and View Post 3478 you have to
edit showcdr.php, phonetabs.php and config.php (in /var/www/html/fop2) to set the correct names of which database and table to use.
My question is if there will be a user/permission problem if you use another database for CDR logs than "fop2" (which is already set in config.php for phonebook and recordings). Would it be best/easiest to create a new "cdr" table in the fop2 database (which the fop2 user already has acces rights to)?
The reason for my question is that most "howtos" regarding mysql cdr logging tell you to setup an "asteriskcdrdb" database and "cdr" table with access for an "asterisk" user.
You do not need to create a table on a different database, you can specify a database and table name using the format
databasename.tablename
That is what is set in the stock/default .php files where the cdr table is in the asteriskcdr database (while the rest of the freepbx configs are on the asterisk database, or the fop2 database if you do not use freepbx).
So, if your cdrs are in database asteriskcdrdb and the table is cdr it will work as is, if the database and tables are named differently, just grep for asteriskcdrdb.cdr on the .php files and replace where needed.
GRANT INSERT, SELECT ON asteriskcdrdb.* TO asteriskcdruser@'localhost' IDENTIFIED BY 'XXXXXXXXX';
Created the necessary table and columns:
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 '',
`uniqueid` VARCHAR(32) NOT NULL default '',
`userfield` VARCHAR(255) NOT NULL default '',
`peeraccount` VARCHAR(20) NOT NULL default '',
`linkedid` VARCHAR(32) NOT NULL default '',
`sequence` int(11) NOT NULL default '0'
);
ALTER TABLE `cdr` ADD INDEX ( `calldate` );
ALTER TABLE `cdr` ADD INDEX ( `dst` );
ALTER TABLE `cdr` ADD INDEX ( `accountcode` );
Then edited cdr_mysql.conf for the right credentials.
Everything seems to be working:
asterisk*CLI> cdr mysql status
Connected to asteriskcdrdb on socket file default using table cdr for 12 seconds.
And I can see my logged testcall by running
mysql> SELECT * FROM cdr;
So far so good !
Unfortunately after editing phonetabs.cdr and showcdr.php I still don't see the call history tab.
Is there any more files that I need to edit? Could there be a permission problem since config.php is set to the fop2 database?
Thanks for your answer. It turns out it was a permission issue as suspected. After granting the fop2 user access to the asteriskcdrdb database with the following command:
GRANT ALL PRIVILEGES ON asteriskcdrdb.* to fop2@'localhost' identified by 'password';
it now works perfectly!
Comments
We have to look at two files showcdr.php and phonetabs.php. In both files it must be set the correct name of DB.table of CDR storage.
So if we have
$grid->set_table('asterisk.cdr'); in showcdr.php
we must have
$res = $db->consulta("DESC asterisk.cdr");
in phonetabs.php
Best regards,
Sergio.
I'm trying to setup the exact same thing. I'm running standalone asterisk 11.7.0 and FOP2 2.27 on CentOS 6.4.
Already set up phonebook and recordings with MySql which works great. Only call history tab left.
As I understand it from this post and View Post 3478 you have to
edit showcdr.php, phonetabs.php and config.php (in /var/www/html/fop2) to set the correct names of which database and table to use.
My question is if there will be a user/permission problem if you use another database for CDR logs than "fop2" (which is already set in config.php for phonebook and recordings). Would it be best/easiest to create a new "cdr" table in the fop2 database (which the fop2 user already has acces rights to)?
The reason for my question is that most "howtos" regarding mysql cdr logging tell you to setup an "asteriskcdrdb" database and "cdr" table with access for an "asterisk" user.
Best regards
Carlos
databasename.tablename
That is what is set in the stock/default .php files where the cdr table is in the asteriskcdr database (while the rest of the freepbx configs are on the asterisk database, or the fop2 database if you do not use freepbx).
So, if your cdrs are in database asteriskcdrdb and the table is cdr it will work as is, if the database and tables are named differently, just grep for asteriskcdrdb.cdr on the .php files and replace where needed.
Best regards,
So I created the asteriskcdrdb database:
CREATE DATABASE asteriskcdrdb;
Added the user:
GRANT INSERT, SELECT ON asteriskcdrdb.* TO asteriskcdruser@'localhost' IDENTIFIED BY 'XXXXXXXXX';
Created the necessary table and columns:
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 '',
`uniqueid` VARCHAR(32) NOT NULL default '',
`userfield` VARCHAR(255) NOT NULL default '',
`peeraccount` VARCHAR(20) NOT NULL default '',
`linkedid` VARCHAR(32) NOT NULL default '',
`sequence` int(11) NOT NULL default '0'
);
ALTER TABLE `cdr` ADD INDEX ( `calldate` );
ALTER TABLE `cdr` ADD INDEX ( `dst` );
ALTER TABLE `cdr` ADD INDEX ( `accountcode` );
Then edited cdr_mysql.conf for the right credentials.
Everything seems to be working:
asterisk*CLI> cdr mysql status
Connected to asteriskcdrdb on socket file default using table cdr for 12 seconds.
And I can see my logged testcall by running
mysql> SELECT * FROM cdr;
So far so good !
Unfortunately after editing phonetabs.cdr and showcdr.php I still don't see the call history tab.
Is there any more files that I need to edit? Could there be a permission problem since config.php is set to the fop2 database?
Best regards,
Carlos
Yes, it could be a permission issue. Whatever user/pass you have in config.php in fop2 must be granted access to asteriskcdrdb.cdr
Thanks for your answer. It turns out it was a permission issue as suspected. After granting the fop2 user access to the asteriskcdrdb database with the following command:
GRANT ALL PRIVILEGES ON asteriskcdrdb.* to fop2@'localhost' identified by 'password';
it now works perfectly!
Best regards,
Carlos