Skip to main content

CDR SQL scripting

Posted by olekaas on Thu, 03/11/2010

The CDR display function is somewhat limited and really doesn't give the customers what they want. Most find it confusing. Also the search fields doesn't make it possible to do some advanced filtering. Instead of cluttering the IF with more fields, selections, alow regex, etc, how about making it possible to make prebuild SQL queries - just like the scripts? Instead of asterisk code, the script would contain SQL code and of course marked as usable only for filtering CDRs. Tailoring reports and stats for each customer would be easy. And now that we are at it - why not add an email option? Report for yesterday will be rigth there in the inbox in the morning - no need to login, click, click, click.

/Ole


Submitted by eeman on Thu, 03/11/2010 Permalink

hmm it sounds like something that can scale into quite a resource drain on the pbx itself.. perhaps a utility that runs on another machine that can do this with the data?

Submitted by olekaas on Thu, 03/11/2010 Permalink

Use a remote SQL server. Then there will be no resource drain on the pbx itself - no matter how ridicolous your SQL code might be. Fireing off a query to another server and waiting for at reply would drain less resources than the current setup with the SQL server inside. Actually, the SQL server on the pbx could be master and you could deploy a slave for querying. Then if you really f*** up, the master will still collect cdr while the slave is grinding to a halt.

I've considered deploying some of those asterisk cdr analyzers out there. They look really cool with all the bells'n'whistles. Create a "view" in mysql for each customer and deploy a separate analyzer for each customer. This way they can only access their own records. So far noone has been interested in the added costs... Most customers just want simple reports. That can be done with the suggested solution. If they need somthing "special" I can write the SQL code in less than 30 minutes and they pay a one time fee. As I dont have any maintenance, theres no direct need for a recurring fee.

/Ole

Submitted by thirdlane on Fri, 03/12/2010 Permalink

Hi Ole and Erik,

Ole, which analyzers did you consider?

I would be happy to discuss options for improving CDR presentation and delivery - on the system, tenant and user levels. While we would not be able to implement any SQL based filtering unless CDR is either written to the database or the CSV text file based CDR is loaded into the database - local or remote, the database would be a prerequisite, so it is all doable.

I am a bit concerned about exposing SQL but hey - we are trying to keep PBX Manager as open as possible, so this is not against the rules :). Perhaps we could provide some queries that users could clone and modify - like in scripts. What would be your typical selection where our basic selection is not sufficient? (thus requiring SQL)

Talking about bells and whistles - would some graphs be useful? And what about breaking down calls as calls to queues, extensions, conferences, etc?

As I started thinking about it, it looks like this may take us on what I considered earlier - call flow analyzer. Call flow (queue) metrics? We could even go further and instrument the dialplan to help the analysis?

Your thoughts?

Submitted by olekaas on Fri, 03/12/2010 Permalink

This one looks nice - but is quite a bit dated:

http://www.areski.net/asterisk-stat-v2/about.php

While it sure looks fancy with all the graphs and stuff it doesn't look userfriendly and easy to use on a daily basis. If you just want a daily report, its a true hassle to fill out the forms with the correct values each day.

While graphs are nice, table reports would be my primary target. Say, a customer wanted a report over how many calls and minutes the employees have been on the phone, I could build this simple script (pseudo code):

SELECT count(*) AS numbers, ceil(sum(seconds)/60) AS minutes FROM $TL-ASTDB WHERE calltime BETWEEN '$ARG1' AND '$ARG2' AND tenant = '$TL-TENANT-VAR'

Add two "datetime" selection fields and save the script. Go to the customers "report page" and add a link to the script.

When clicking on the link, the customer is presented with the two input fields (and maybe a description:). On submission, the page will return a table where the result is dumped into. Much like phpMyAdmin - but without all the handles :)

Customer should not have access to build their own SQL. The report SQL should have its own database user which can do SELECTs only.

To me, all the bells'n'whistles with graphs, queue analyzer are "nice to have". Lets get the table report - then we can expand with the fancy stuff later. And even before the fancy stuff, I would like "schedule email report" and "CSV dump report".

Call flow analyzer would sure be a nice thing when "weird" things happen (like when the end user have made an on-phone forward...).

/Ole

Submitted by eeman on Fri, 03/12/2010 Permalink

that's why i believe its going to have to process on a separate server.. due to the scaling of 1000 tenants all getting 1000 customized daily reports. That load coupled with the load that 1000 tenants would bring to a server seems a bit high for a monolithic approach.