I can't get Reports to work

Hi,

I have read most of the topics on Reports and done all of the suggestions and still no love. I am the first to say the problem could and probably is something I’ve done wrong.

The system is:

Asterisk 1.4.21.2
Asterisk Addons 1.4.7
FreePBX 2.3.1.7
CentOS 5 (2.6.18-92.1.10.el5)

I have checked and re-checked everything I can find here and elsewhere on the Inet and everything seem to be OK, except when I go to “Reports”, there is no data. The file /var/log/asterisk/cdr-csv/Maaster.csv is 525K, MySQL is installed and cdr status shows:

voicemail*CLI> cdr status
CDR logging: enabled
CDR mode: simple
CDR output unanswered calls: no
CDR registered backend: cdr-custom
CDR registered backend: cdr_manager
CDR registered backend: csv

So it seems to me the csv data is not getting to MySQL. I have no clue as to how the data gets there and I do not know MySQL so if someone has some tips to confirm that configuration, I would appreciate newbie directions.

Thanks,

–Pete

Dear All,
Being too new about the asterisk and the mysql i am having this critical situation right now.
I am having the asterisk 1.6 and each and everything is working fine including inbound and outbound autocalls too but main issue is coming that i am not having any records in CDR’s user filed .
When i do the select userfield from cdr;
then its not showing any thing…no records…
Can anyone come up for me.
will be a nice help.
Thanks
Rak

The first thing to do would be to verify whether or not the cdr are getting to the MySQL table.

By default, the database is ‘asteriskcdrdb’ and the table is ‘cdr’. Are you familiar enough with the MySQL console to check to see if the ‘cdr’ table in the ‘asteriskcdrdb’ database has rows or do you want me to give you step-by-step instructions?

Dean

Hi,

I am able to connect to the server and show the databases:

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| asterisk |
| asteriskcdrdb |
| test |
±-------------------+

I can show the tables:
mysql> show tables;
±------------------------+
| Tables_in_asteriskcdrdb |
±------------------------+
| cdr |
±------------------------+

But I don’t know how to see if there is data in there!

[UPDATE]

Found out how to get this info:
mysql> DESCRIBE cdr;
±------------±-------------±-----±----±--------------------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±-------------±-----±----±--------------------±------+
| calldate | datetime | NO | | 0000-00-00 00:00:00 | |
| clid | varchar(80) | NO | | | |
| src | varchar(80) | NO | | | |
| dst | varchar(80) | NO | | | |
| dcontext | varchar(80) | NO | | | |
| channel | varchar(80) | NO | | | |
| dstchannel | varchar(80) | NO | | | |
| lastapp | varchar(80) | NO | | | |
| lastdata | varchar(80) | NO | | | |
| duration | int(11) | NO | | 0 | |
| billsec | int(11) | NO | | 0 | |
| disposition | varchar(45) | NO | | | |
| amaflags | int(11) | NO | | 0 | |
| accountcode | varchar(20) | NO | | | |
| uniqueid | varchar(32) | NO | | | |
| userfield | varchar(255) | NO | | | |
±------------±-------------±-----±----±--------------------±------+
16 rows in set (0.00 sec)

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

So, no data. Any thoughts?

Thanks,

–Pete

Having Asterisk Addons is not adequate, you have to make sure the mysql modules get built. They may need to be selected or it could be you are missing dependencies.

At the CLI you can type ‘show modules like sql’ and check if you see cdr_addon_mysql.so, res_config_mysql.so and app_addon_sql_mysql.so.

The most common reason for your issue once you’ve verified the table is empty is because the modules are not there.

So you have proved that MySQL is installed and running and has the proper tables for the cdr. You have also proved there is no data in there.

Have you checked the cdr_mysql.conf to make sure the config is correct?

http://www.pbx.in/voip-info/wiki/view/Asterisk+config+cdr_mysql.html

Dean

OK,

voicemail*CLI> module show like sql
Module Description Use Count
0 modules loaded

That would be it I guess.

I was wondering how the data got from the log into the database. So, any pointers on what i did wrong to end up with no modules?

[UPDATE 26/05/2009]

OK, in doing other things, I found this http://www.voip-info.org/wiki/view/Asterisk+cdr+mysql

I had prviously done these steps, but I do recall having problems with the svn checkout step, I am only guessing that it didn’t work but I went on my merry way regardless!

I ran through all of the steps again and it worked, I recompiled the addons, confirmed that there were no errors related to mysql and voila, I had calls in my Reports!

I only had 2 there as that’s all that had happed since getting this working, but I had 529K worth of data in the Master.csv so I used this: http://www.voip-info.org/wiki/view/Asterisk+CDR+csv+mysql+import to import the data and now all of my calls are in the Reports. As a note for the import, make sure you adjust these lines if you have changed any defaults:

$locale_db_host = ‘localhost’;
$locale_db_name = ‘asteriskcdrdb’;
$locale_db_login = ‘asteriskuser’;
$locale_db_pass = ‘***’;

The last 3 lines db_name, db_login and db_pass were all different in my system from the defaults in the script.

Thanks to everyone who contributed, it helped me to find the answer!

Now, on to seeing why the directory doesn’t work…

Thanks,

–Pete

Rak:

I had the same situation in one server. I confirmed I had no mysql libraries enabled in asterisk as Philippe pointed out (module show like mysql), confirming there were no such libraries loaded. Then I went through Pete’s link http://www.voip-info.org/wiki/view/Asterisk+cdr+mysql, which basically stated I should install libmysqlclient*dev libraries, then recompile asterisk-addons. I did all this, restarted asterisk, and now CDRs are getting populated. HTH

There are many people around having problems with cdr_addon_mysql.so
lacking this the system won’t save your call logs to cdr table.

I had this issue with Asterisknow distribution, but I think that the solution will be the same: recompile asterisk addons as it failed to load.

Anyway before proceeding you could check if logging is enabled and module loaded.

pbx*CLI> cdr show status

Call Detail Record (CDR) settings

Logging: Enabled
Mode: Simple
Log unanswered calls: No

pbx*CLI> module show like cdr
Module Description Use Count
cdr_addon_mysql.so MySQL CDR Backend 0

Now it’s working but to rebuild it I had to install mysql-devel and asterisk16-devel frome the repos, then I downloaded asterisk-addons-1.6.2.3.tar.gz and unpacked it to /usr/src
I made a symlink:

ln -s asterisk-addons-1.6.2.3 asterisk-addons

Then I ran:

./configure

./make menuconfig

./make all

Don’t make install!

I replaced only cdr_addon_mysql.so in /var/lib/asterisk/modules as this file was already present from the asterisk16-addons distribution package. Maybe it was built in a wrong way by the package maintainer.

Now you have to restart asterisk.

If it doesn’t build it’s only matter of deps.

Now you can run mysql -u freepbx -p
this is what you should get:

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| asterisk |
| asteriskcdrdb |
| test |
±-------------------+
4 rows in set (0.00 sec)

mysql> use asteriskcdrdb;
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> show tables;
±------------------------+
| Tables_in_asteriskcdrdb |
±------------------------+
| cdr |
±------------------------+
1 row in set (0.01 sec)

mysql> describe cdr;
±------------±-------------±-----±----±--------------------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±-------------±-----±----±--------------------±------+
| calldate | datetime | NO | | 0000-00-00 00:00:00 | |
| clid | varchar(80) | NO | | | |
| src | varchar(80) | NO | | | |
| dst | varchar(80) | NO | | | |
| dcontext | varchar(80) | NO | | | |
| channel | varchar(80) | NO | | | |
| dstchannel | varchar(80) | NO | | | |
| lastapp | varchar(80) | NO | | | |
| lastdata | varchar(80) | NO | | | |
| duration | int(11) | NO | | 0 | |
| billsec | int(11) | NO | | 0 | |
| disposition | varchar(45) | NO | | | |
| amaflags | int(11) | NO | | 0 | |
| accountcode | varchar(20) | NO | | | |
| uniqueid | varchar(32) | NO | | | |
| userfield | varchar(255) | NO | | | |
±------------±-------------±-----±----±--------------------±------+
16 rows in set (0.00 sec)

mysql> select * from cdr;

You should start to get something as you receive or make calls.