In desperate need of reporting help

Hi all

I have taken over the support of a freepbx installation at a business that relies heavily on the call reports. They previously used a Samsung Office serv that gave reports that included things like:

Total calls
Total minutes
Total in/out
Calls per extension
Calls per hour.
Average talktime

I have tried asternic etc, and whilst very nice, their support is non existant, which wont work. There also appears to be no way to exclude internal calls.

I have tried the queue reports plugin and its an embarrassment (for the money). Its full of bugs at multiple levels and provides very little additional functionality over the built in CDR - Reports page.

I think I may be able to work with the CDR Reports output. So 2 questions:

Is there anything that can email a report on the hour with the required information?
If no, is there anyway to automate the CDR-Reports output in a cron job, and then email that output as a PDF, to a destination?

I am at my witts end with this, and am at risk of having to terminate the relationship with the customer as I am not able to give a simple call report that they have asked for.

I have an example report from the old system but am not able to post a link up here

Any help would be very much appreciated!

feel free to report bugs and submit feature requests at
http://issues.freepbx.org

1 Like

Happy to do so, but this is a priority, so bug reporting is on the bottom of the list. I am using the 30 day trial of the module which I assume is the same as the paid version.

Everything you want should be in your “cdr” table in the “asteriskcdrdb”. I don’t have time to do it today, but writing a PERL script to grovel through the database on an hourly basis and pulling the data out should be reasonably trivial.

Run the program against the database once an hour and have the output sent to you. Unless you want something tricky like building into a spreadsheet with pages per hour, it really should go really quickly.

If you’d be more comfortable with a commercial product, there are plenty of Asterisk Reporting programs out there that should be able to do what you need.

Remember - FreePBX is mostly a management portal. The data that you want is Asterisk data, and that’s a well-understood and commercially supportable set of systems.

I have tried 4 commercial products that apparently do what I need. After testing them, the reality is different (for example, eliminating internal calls from the reports).

3 of the 4 commercial products I tried would not answer the phone or respond to emails asking for more information which I find unacceptable. I am responsible to my customer if the product fails.

I have lots of experience with freepbx and asterisk, but it would appear that the reporting lags behind other asterisk “distros”

Any ideas of the default credentials for the cdrdb, I was unable to find them in the config files to to run a sql command against.

Many thanks. (Also thank you for the offer of assistance with the PERL script, its much appreciated)

You have to be “on the machine” to access the database. You can use the standard “root” user to access the database.

As a general rule, I don’t post passwords or usernames on here. I know they are trivial to find as it is, but why make it any easier for the bad guys.

I’ve been at this for a while, and I find that most people don’t actually use all of the reporting they ask for. It’s cool for a couple of days, but as the system progresses, they look less and less.

Regardless, the data is out there.

In general being responsible for “when things break” you should do them the right way by using internals. Hardcoding things that you or someone else may change in the future is not a great idea.

#!/usr/bin/env php
<?php
include '/etc/freepbx.conf';
$dbh = FreePBX::Cdr()->cdrdb;
$sql = 'SELECT * FROM cdr LIMIT 1';
$stmt = $dbh->prepare($sql);
$stmt->execute();
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));

Example of using interals. With this if there is a setting change it doesn’t break stuff.

1 Like