Asterisk – Getting Results from CDR Database!

So in my previous article, I said I would provide some sample scripts to view information from our CDR database. My script is written in PHP, and we’re just going to do a quick query of the database for answered calls.

To make things neat, I like to write the mysql connection string and variables in seperate files. So my config.php will maintain the database connection information:

<?php
// This is an example of config.php
$dbhost = 'localhost';
$dbuser = 'asterisk';
$dbpass = 'yourpassword';
$dbname = 'asterisk';
?>

Now the mysql connection strings, first to open the connection, the second to close the connection:

<?php
// This is an example opendb.php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die
('Error connecting to mysql');
mysql_select_db($dbname);
?>
<?php
// an example of closedb.php
// it does nothing but closing
// a mysql database connection
mysql_close($conn);
?>

The above files will be included or used in any script we use to query the database. Now let’s get to the queries…
To gather the information of what I’m querying for, I put together a simple HTML form. You can view the form here. And download it’s code here. The form will POST to our process.php which will be discussed in a moment. You will notice several fields on the html form. The only fields that are required are the start date/time and the end date/time.



The PHP that queries the database and outputs the results can be found here.

And the results are something like this:

CDR Results

That’s it! You can download the files mentioned in this article here.

This entry was posted on Tuesday, February 5th, 2008 at 12:32 amand is filed under Asterisk PBX. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

6 Responses to “Asterisk – Getting Results from CDR Database!”

  • Kapila Says:
    August 13th, 2009 at 3:42 pm

    This is very useful.But I run my asterisk@home in non GUI and I want access this AAH’s CDR from other networked computer and display the details there.What are the things I should change?in Config.php

    Are there more additional changes

  • kapila Says:
    August 18th, 2009 at 5:02 am

    (1) How to download and install Cent OS 4.0.is it alpha/i386/ia64 or what

    (2)does your artical supports cent OS 5.1 or later

    (3)How to setup soft phones(like X-lite) with this asterisk system I want know about creating extentions for soft phones as well

    thanks

    regards

    kapila-from Srilanka

  • Kapila Says:
    August 19th, 2009 at 3:03 pm

    please give help on following

    (1)How to create extentions for soft phones like X-lite with your system

    (2)where should I create following table

    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 ”,
    `userfield` varchar(255) NOT NULL default ”,
    `uniqueid` varchar(100) NOT NULL default ”
    );

    ALTER TABLE `cdr` ADD INDEX ( `calldate` );
    ALTER TABLE `cdr` ADD INDEX ( `dst` );
    ALTER TABLE `cdr` ADD INDEX ( `accountcode` );

    Now just reload Asterisk from the Asterisk Cli:

    reload

  • Author Says:
    October 21st, 2009 at 4:53 pm

    Hi Kapila,

    1) The version of CentOS that you use is entirely up to you. Your hardware can help you determine which version to use. If you are using 64bit hardware, for example, you might consider i686 version of CentOS. However, from my experience, Asterisk will periodically crash out on i686 version of CentOS. You can download the ISO from CentOS’s website: http://centos.org/
    Use your fav. burning software to burn the image. Make sure it has the option to ‘burn a disc image’ or ‘burn an iso’…

    2) At the time I wrote this article, I was using CentOS 4 because they had a server ISO, which had min. packages bundled within. However, I have since deployed several Asterisk boxes with CentOS 5.x and never had any problems.

    3) Xlite uses SIP. So you are going to create your SIP ‘extensions’ in the sip.conf. Use those settings within Xlite, and the IP of your Asterisk server to connect. I don’t have an article written on how to do this just yet. But I’m sure you can find out how to write your sip.conf using the sample file, or on voip-info.org

    Thanks for the questions. Let me know if you have any other questions.

  • Author Says:
    October 21st, 2009 at 4:53 pm

    Kapila,

    Unfortunately, I don’t know the AAH System that well. Maybe someone else can comment on this? Thanks!

  • Alfred Says:
    June 15th, 2010 at 6:12 pm

    You are a very smart person!

Leave a Reply