Topic: CDR Database with 3.1.0-120

[compsos]

Hi Jeff
Asterisk 18-1.8.5.0-1
Seems like the new Sail version is not opening the database for entries

Code: [Select]

[Jul 14 17:29:56] WARNING[13487] res_config_mysql.c: MySQL RealTime: No database user found, using 'asterisk' as default.
[Jul 14 17:29:56] WARNING[13487] res_config_mysql.c: MySQL RealTime: No database password found, using 'asterisk' as default.
[Jul 14 17:29:56] WARNING[13487] res_config_mysql.c: MySQL RealTime: No database host found, using localhost via socket.
[Jul 14 17:29:56] WARNING[13487] res_config_mysql.c: MySQL RealTime: No database name found, using 'asterisk' as default.
[Jul 14 17:29:56] WARNING[13487] res_config_mysql.c: MySQL RealTime: No database port found, using 3306 as default.
[Jul 14 17:29:56] WARNING[13487] res_config_mysql.c: MySQL RealTime: No database socket found, using '/var/lib/mysql/mysql.sock' as default.
[Jul 14 17:29:56] WARNING[13487] res_config_mysql.c: MySQL RealTime: No database socket found (and unable to detect a suitable path).
The database does exist and the entries is sark_cdr_mysql.conf do match MYSQL asterisk database. We are getting the same error on 2 independant systems. it look as though asterisk is not using the sark settings but it's on configuration.

Any clues on where to look as we are trying to reset up a CDR reading system.

[SARK devs]

Hi Gordon

Let's see if I can help.  First of all, the messages you show are generated by Asterisk realtime, they aren't as fas as I know anything to do with Asterisk cdr generation.   SARK V3 doesn't use MySQL at all and as a result, the sark_cdr_mysql.conf file should have everything commented out (unless you have an old version carried over from V2). 

If you want Asterisk to generate CDRs into MySQL the you can to create the database and tell Asterisk to use it.  It's pretty easy to set up; there is a good article here

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

Once you've done that then Asterisk will generate both normal and MySQL cdrs.

Hope this helps.

Jeff

[compsos]

Thanks Jeff
Yes I did find it all commented out and un-commenting but it did not help.
Setup the new database as per the article added the connection details to sark_cdr_mysql.conf (not much difference to what was there)
Reloaded Asterisk
Tested and works
Now back to getting the info back out as a report/s.

[compsos]

Looking at astCDR as it does not look like it is over the top with features.

I know you have moved to sqlite db system so this may be the easiest to use. I am getting an error when the checkconfig.php file runs but do not want to do anything that will distrub the running of sail.

Code: [Select]

CONFIGURATION CHECK     CHECKED REQUIRED RESOURCE   RESULT    Sqlite_open PHP function  missing   Sqlite_query PHP function  missing   Sqlite_close PHP function  missing   Asterisk cdr.db SQLite database readable
Jeff do you know anything about the sqlite PHP functions? Are the in use by Sail?

Also on the mysql side, found the "Grant " statement giving the user asterisk only insert rights cause asterisk to fail to load. Raising those rights solved the problem.

[SARK devs]

do you know anything about the sqlite PHP functions? Are the in use by Sail?

The SAIL PHP code (which currently is only used in the generator) uses PDO to talk to sqlite so no, I don't think it needs the php sqlite functions you refer to.  I have seen reference to Asterisk building SQLite CDRs but I've not done any work on it.

Kind Regards

Jeff

[compsos]

Trying astCDRview from http://astcdrview.berlios.de/documentation.php

I think this might be the stumbling block

Code: [Select]

Asterisk and astCDRview databases are version 2
On my system we have

Code: [Select]

sqlite-3.3.6-5
python-sqlite-1.1.7-1.2.1.0
python-sqlite2-2.3.3-1.el5.rf
sqlite-devel-3.3.6-5

Asterisk is writing to the cdr.db but sqlite3 complains that it is encrypted.

Jeff I assume the sqlite for sail is ver3? If so then it is back to mysql db.

just found this answer but it seems strange when using sqlite3 to open the file

If you get an error message:
 
      Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database
 
 then most probably your code is accessing a SQLite3 database.
 
 sqlite_open() only supports SQLite2.  Use PDO to access SQLite3.

[SARK devs]

SARK uses sqlite3, as does SME Server 8.0

Best

Jeff

[compsos]

We are close to having a working solution,  it is an independent webpage outside the the "sail" screens.
For a client who needs a report on calls and costs over a selected period, is it worthwhile to add a new table to the asterisk mysql DB to record ITSP call charges or just store in a stand alone file?

[SARK devs]

I'm not sure it matters too much at this point. Better just to get something up and running to evaluate the functionality and usability.

Best

Jeff

[compsos]

We installed Asterisk-CDR-Viewer and made it working. You can check it out here http://code.google.com/p/asterisk-cdr-viewer/

[SARK devs]

Can you tell us what you did to make it work?

Best

S

[compsos]

Sorry Jeff my colleague was little short on words.

We did look at about 5 different packages but this one worked without a large array of supporting modules.

This needs to be scripted but basically....
download the package
Add the mysql_addons module to asterisk
Load the module in asterisk
Check that the asterisk DB exists and data is being added
Create an Ibay as a local webenabled.
Expand the the downloaded file to the html directory
Edit the include/config.inc.php to link to the asterisk DB
Open the connection http://yourdomain/youribay

Ideally would be better to add into Sail like the version 2 and install under /opt/sail

We have also done a small cost calculator modification to the package
http://compsos.com.au/callcosts.jpg

This is some what basic in that it reads call costs from a file and at this stage using logic in the webpage for the calculation of a particular ITSP.
There rules are

• Local & National calls un-timed fixed price
• 13xxx calls at fixed price
• mobile calls price for 1min + (1min price/60 * secs past 1 Min)
• International (rate* secs) but as there are multiple rates per country (district and mobile). At this stage we have basically left international alone.
• systems with more than 1 ITSP have not been considered.

If anyone is interested in the costing we can pass on the modifications.

Any questions please ask.