Thirdlane.com logo

Thirdlane PBX 5.0.46 and cdrs in mysql

Posted by kyriakos on Thu, 01/03/2008
Forums
Thirdlane platform General Questions

Hello,

i have installed Thirdlane PBX 5.0.46 and i would like to enable CDR MySQL logs. I have installed all necessary asterisk packages. Is there a documentation available regarding the MySQL database, table structure etc required for using Thirdlane PBX and MySQL cdrs? Do i just configure the database like mentioned here http://www.voip-info.org/wiki-Asterisk+cdr+mysql or is it something more.
It would be nice to include the MySQL code for the db creation in the support documentation.

thanks,
KM

Submitted by eeman on Fri, 01/04/2008 Permalink

the table structure, as of now, is static within asterisk.

since you already have webmin installed first ensure you have mysql server installed and then use mysqladmin to set the root password

mysqladmin -uroot password 'newpassword'

then log into the mysql interface in webmin, it will only ask you the login for root one time

  1. Create a database (like asteriskcdrdb)
  2. Create a user under 'user permissions'
  • to be 100% problem free, create 2 identical logins
    • one set to host any
    • one set to host 'localhost' in case a unix socket attempt is made
  • Give these users no permissions in the user permissions area
  • Go to 'database permissions' and create a new permission
    • Specify the database you just created
    • Specify the user you just created
    • Specify host 'any'
    • Grant all permissions to this user for this database
  • From command line, create a file (ex cdr_mysql.sql) and insert the following

    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 ''

    );

  • Now execute mysql -uroot -pnewpassword asteriskcdrdb < cdr_mysql.sql
  • All that remains is populating System Settings -> CDR Settings with the credentials you just created
    • Ensure 'other options' includes userfield=1 and table=cdr
    Submitted by raven on Tue, 09/08/2009 Permalink

    Having a similar problem trying to get MTE to talk to a local mysql database for the first time. Is the table definition above still relevent in Thirdlane 6.0.1 / Asterisk 1.4, or is this right?:

    CREATE TABLE asterisk14cdr (

    accountcode varchar(20) NOT NULL default '',

    src varchar(80) NOT NULL default '',

    dst varchar(80) NOT NULL default '',

    dcontext varchar(80) NOT NULL default '',

    clid 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 '',

    start char(21) NOT NULL default '0000-00-00 00:00:00',

    answer char(21) NOT NULL default '0000-00-00 00:00:00',

    end char(21) NOT NULL default '0000-00-00 00:00:00',

    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',

    userfield varchar(255) NOT NULL default '',

    uniqueid varchar(32) NOT NULL default ''

    );

    Is there somewhere I can get full step by step instructions? (like here?)

    Submitted by eeman on Wed, 09/09/2009 Permalink

    the order of the schema needs to match mine otherwise it wont work. You really should name your table 'cdr' for compatibility with some non-flexible billing solutions out there. I have been using that schema file for 4 years without issue, ever.

    Submitted by raven on Sun, 09/12/2010 Permalink

    I thought I had this all running. However, it seems TL can happily write to the DB, but when I go to look at call history, it can never display anything from the DB. It does appear that it is trying, because if I take away all priviledges from the DB user, TL says the user can't perform the SELECT command on the DB. Is there something I can check to see what is up? Tried enabling errors and warnings in /etc/my.cnf, but the logs never show any attempts for anything.

    Submitted by eeman on Sun, 09/12/2010 Permalink

    i have no clue.. 5.0.46 is no longer supported. I would recommend upgrading. and when you do start a new thread.

    Death to anyone who resurrects 2 yr old posts.

    Submitted by raven on Sun, 09/12/2010 Permalink

    I can always depend on your pithy retorts to focus and redouble my efforts. Anyway, my issue turned out to be the age-old issue of mysql DB cdr table userfield write omission and the various asterisk-addon editing stuff to resolve. Building a log in /etc/my.cnf (log=/tmp/mysql_query.log) helped to figure out what was up. I figured it out.
    finally