CDR Reports AKA PHP programming 101

So you want to create a customised report from your sysadminman VPS, to email you some call stats maybe once per week? You may find this info invaluable.

Setup: Asterisk 1.8, FreePBX 2.9, sysadminman VPS

Goal: some call stats via email

Originally I had wanted to install something like CDRstats or asternic but this is now a critical VPS and my backups aren’t what they should be… so yes I was scared partly and also when I installed CDRstats on a test system I couldn’t really get it to work properly. So I wrote my own, following on from the very basic database querying I started with in this post.

So I will assume some basic knowledge of programming or at least the ability to copy/paste/edit. Familiarity with HTML would be useful.

I like Notepad++ because I use Windows, and so using WinSCP to connect to the box and then right-click to edit my PHP script was very handy rather than editing in an SSH console via nano. Also the MySQL Workbench allows connection via an SSH tunnel to your MySQL database, so you can check that your MySQL queries are going to work.

Create your script in /var/www/html/admin
Mine is called Weekly-call-stats.php


0.5. PHP requires a semicolon (;) at the end of each line of code – remember this when trying to work out why your script doesn’t work. PHP is used to dynamically create HTML, so you can be quite clever with CSS, images etc to make things look nice. For now I’m just creating a down-and-dirty script.

1. Something I learned part way through and would have been better to know at the start – to get debugging on your PHP script, add the following lines to the top of the script:

&lt;!--?php <br> ini_set('display_errors', 'On');
error_reporting(E_ALL);

You’ll notice the script starts with <!–?php
It must end with ?>

2. Useful reading on PHP and MySQL can be found on www.w3schools.com

3. The likelyhood is that most scripts will connect to MySQL database, so define the connection parameters thus (we are connecting to the Asterisk CDR database):

$username = "yourusername";
$password = "yourpassword";
$hostname = "localhost";
$dbasename = "asteriskcdrdb";

4. These lines connect you to your database:

$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
$selected = mysql_select_db($dbasename,$dbhandle)
or die("Could not select database");

5. Now we need a query to run, which we’ll define here:

$doquery = "Select dst, count( case when clid like '%BHAM:%' then 0 else NULL end ) as Bhamcalls from asteriskcdrdb.cdr where src not regexp '^1[0-9][0-9][0-9]$' and disposition = 'answered' and dst in (".$includedexts.") and yearweek(calldate) = yearweek(current_date) group by dst order by dst asc";

5.5 There’s a lot to take in there, as this is quite an advanced query. Let me explain.

select dst <== this means choose this colum in our results
count(case… <== this section means to count the number of calls where the clid includes BHAM: in it somewhere. This is a prefix I have added to the incoming CLID so my operators know which phone number was called.
from asterisk… <== the database and table name to pick from
where src not regexp… <== this means to only pick results where the src column does not match a Regular Expression, in this case the expression is 1000 – 1999
disposition = <== we are only interested in calls that were answered this time around
dst in (“.$includedexts.”) <== ok so this is sneaky – I defined a variable earlier in the PHP script thus:
$includedexts=”‘1001′,’1002’,’1003′”
They are surrounded by single speech marks so that the MySQL query works right. It says we only want records where the destination ended up being our 3 chosen extensions
yearweek(calldate) = yearweek(current_date) <== this means that the date of the call is this week. Weeks start on Sunday 00:00 and run through to Saturday 23:59 and my script runs on a Friday late afternoon.
group by… <== tells MySQL that we just want a summary not a whole list of items
order by… <== tells MySQL to sort the results by destination, in ascending order

6. So the query is defined – the next line tells PHP to run the query

$result = mysql_query($doquery);

7. Now we have a variable with a load of data, we need to get that into a HTML file. Start with the basic HTML stuff. We are using a variable named $outmessage to contain the whole of our output HTML page.

$outmessage = "Summary of calls for some extensions inbound calls";

7.5 to get the data out, we need to enumerate it somehow. This line creates a html table with a header row:

$outmessage.= "
";

By the way – in PHP “.=” means ‘add this to the end of the string variable’7.6 So we’ve created a table, now how about getting some data into it?

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$outmessage = $outmessage. "
" . "{$row['bhamcalls']}";
}
$outmessage.="
<table border="1">
<tbody>
<tr>
<th>Extension</th>
<th>Calls answered (BHam)</th>
</tr>
<tr>
<td>Ext{$row['dst']}</td>
</tr>
</tbody>
</table>
";

What this does is enumerates the results line-by-line, and creates a new HTML table row per MySQL-line, grab the data into atag and then close the row. Then we close the table as it’s finished.

7.7 Close the HTML and tell the script to output your HTML with the following:

$outmessage.="

";
echo $outmessage; ?&gt;

7.8 Now save your script and go run it in a web browser:
https://your.vps.ip/admin/admin/callstats.php
You should see a table with your call stats in!

 

8. Emailing is the next bit. Define these variables (somewhere before the ?> – remember this is what signals the end of our script):

$mailto = "[email protected]";
$subject = "Weekly Call Stats for VPS";
$from = "PBX &lt;[email protected]&gt;";

8.1 Define the email headers:

$headers = 'MIME-Version: 1.0' . "\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\n";
$headers .= 'From:'.$from . "\n";

8.2 We already have the HTML in a variable, so the last bit is just to tell the script to email it:

mail($mailto, $subject, $outmessage, $headers);

8.3 hit Refresh on your browser, you’ll see the html page again and you should now get the same details via email.

9. Schedule the script to run weekly using cron. For this you have to use an SSH client to command your VPS, and I like to use Nano as a ‘normal’ text editor instead of Vi which is definately not normal.

<code>EDITOR=nano crontab -e</code>

9.1 Check out how Cron works via Wikipedia, then add a line like so:

30 16 * * 5 /usr/bin/php /var/www/html/admin/callstats.php

What this does is at 30 minutes past the 16th hour, every year, every month, on the 5th day of the week (Friday) we want to run the program named PHP with the parameter of /var/www…callstats.php

9.2 control-x to exit nano, y to save, and you’re done.

10. add // to the beginning of the “mail($mailto…” line to stop it sending you emails whilst you are further fiddling with additional tables etc. Our script creates another table based on the outbound call records too. It’s a bit complicated but essentially we have an array of arrays, where area codes are defined against a name (eg 0121 is Birmingham). Then we build the MySQL query with a number of ‘and dst like ‘ commands through a series of while loops, then execute the query and build the HTML table headers based on the array’s key name.

It looks like this:

Outbound calls per extension, by destination:

Extension Birmingham Derby Manchester Chester Brighton Milton Keynes
Ext1001 2 32 16 16 1 5
Ext1002 10 7 16 0 0 0
Ext1003 1 0 8 60 46 1

Then we enumerate the array to show which STD codes are included in each area, and query the database again to show all area codes which are not included in the count (so we can decide if calls were made that should have been included in one area).

The whole thing took about 6 hours to conceive, create, test and debug, requiring 2 good sessions of 3 hours each. I’m open to commissions should you need anything similar.

Happy reporting!

UPDATE:
Section 10 has been updated to use a get variable, so you can open the report in a browser without it emailing you every time you refresh. It now looks for a variable doemail to be set to ‘yes’ (weekly-call-stats.php?doemail=yes). Because of this, section 9 needed to change so that the cron job also specified the parameter, but you can’t just add the parameter to a PHP command line. The way around this is to setup the cron job to do the following:

wget https://your.ip/path/weekly-call-stats.php?doemail=yes --no-check-certificate

The above code hasn’t been updated to the latest version yet – when I have time I intend to re-do sections of it to make it more scaleable.