APLawrence.com -  Resources for Unix and Linux Systems, Bloggers and the self-employed

Operator CDR records


2013/02/24

The script I'm going to present here is probably not necessary.   I'm not at all sure that there aren't better ways to accomplish what it does.  It is most definitely a kludge in every sense.

However, it may still have value to some, so let's dive in.

Call Data Records

Operator, like any PBX system, keeps track of calls.  What it (and most other systems) does not do is provide any way to audit those calls.  If you want to know if your salespeople really are making the cold calls they are supposed to be making or whether your sales manager really did return the call of that irate customer as quickly as he claimed, you don't have any easy way to do that.

There is other software that can provide auditing.  Of course it needs the raw call data to slice and dice, so PBX systems usually provide some way of getting that data.  That might be through a database or through a network port that will stream out the records on demand.

Kerio Operator doesn't directly provide that as of version  2.0.3 (February 2013, if you are reading this years later).

They do have an API that can query the records.  However, that seems to be incomplete and, in my testing, seems not to get the most current data.  It also seems rather clumsy to involve a third machine to get data from Operator to pass to the auditing computer.

With that in mind, we (the customer who needed this and me) starting looking for ways to get what we wanted.  Of course we first asked Kerio support and were told to use the API - we didn't like that answer, so we kept digging.

Asterisk Call Data

Asterisk is the base of Kerio Operator.  Kerio has mucked with that code extensively, tearing out whole chunks and replacing with their own code, but Asterisk documentation can often be helpful in understanding Kerio operator.   With the help of that documentation, we were able to locate what seemed to be a call data database at /var/log/asterisk/cdr.db

I brought that down to my local machine and "file cdr.db" told me that it was indeed a Sqlite2 database.  I didn't have anything on my machine that could read Sqlite2 (My Mac has Sqlite3), but "strings cdr.db" confirmed that this was indeed call data and looked to be current.

Oh, frabjous day!  Well, except:

Assuming that the auditing machine understood ssh and Sqlite2, we could give it access to that database through ssh.  That would, however, involve surrendering the root password and neither I nor the customer particularly liked that idea.  Aside from security concerns, he might want to change it now and then and that would require updating the auditing machine also.  

Or, we could use a third machine as we could for the API.  We didn't like that idea either.

Asterisk Modules

We learned that there are Asterisk modules that can stream call data to a port.  Could we add such a module to Operator?

Umm, probably not a good idea.  Kerio's Operator box (which is what my customer has) is designed to be tamper proof.  The root file system is mounted read only to start with.  There's also the small matter that Kerio support nixed the idea outright.  I could certainly remount the filesystem and add a module, but I have no way of knowing if it would work or if it would interfere with Operator in other ways.  Aside from that, this would have to be redone after every upgrade.. that didn't seem to be the right path.

Adventures in Operator

So, we decided that we'd put a script right into Operator.  That's fraught with its own issues, as we shall see, but the first challenge was to read the database with Perl.  Perl can read various databases with its DBI module, but when I looked into /usr/lib/perl on Operator, DBI was not installed.

Oops.   On any other machine, I'd just use cpan to install it, but again, Operator has that read only file system and all that implies.  Adding a Perl module probably wouldn't interfere with anything, but still..

So, I decided we'd just make do.  I could read the database with an sqlite command nested in backtics and send the data out a port.  Wait, does Operator have the Perl modules I'd need for a network listener?  Why, yes, it does.  Big sigh of relief.

But where can the script live and what will start it?  

Oh, my, there are a hundred ways to do that (see Unix and Linux startup scripts ) but most of them would involve mucking with Operator's security again.  I therefore decided that the script would live in /var/log where it can survive reboots and (I assume) upgrades.  But how to start it?

Cron immediately comes to mind and Operator does allow you to add cron jobs.  The problem is that the script is a network listener - we want to start it and just leave it running, so cron would seem to be a bad choice.

Well, not so fast: if our script checks that another copy of itself isn't already running, we can let cron try to run it at regular intervals.  That's the approach I decided on.  A kludge, yes, but the whole thing is a kludge, so let it be.

The Script

So, with all that in mind, here is the kludgy script that we shouldn't need to write.  I hope and assume that someday Kerio will add this ability directly into Operator, but for now, this works.  The usual caveats apply: understand this before you deploy it or hire somebody who does understand it to help you.  The script may very well break with a future Operator upgrade and if I'm not around to re-write it, you or someone else will have to.

#!/usr/bin/perl
use IO::Socket::INET;
$last_output=time();
$now=time();
open(LOG,">>/var/log/cdr_relay.ourlog");

open (L, "/tmp/cdr_pid.lock"); 
$pid=<L>;
 close L;
if ($pid) { 
 $stat=kill 0, $pid ; 
}
 chomp $stat;
 chomp $pid;
 if ($stat and $pid) { 
  print LOG "\nExiting because $pid exists\n";
   exit 0;
 } 
open (L, ">/tmp/cdr_pid.lock"); 
print L "$$\n"; 
close L;


# flush after every write
$| = 1;
my ($socket,$client_socket);
my ($peeraddress,$peerport);

$socket = new IO::Socket::INET (
LocalPort => '3036',
Proto => 'tcp',
Listen => 1,
Reuse => 1
) or die "ERROR in Socket Creation : $!\n";

open(I,"/var/log/cdr_relay.dat") or die " no cannot $!";;
$last=<I>;close I;
@s=split /\|/, $last;
$last_time=$s[0];
#print "Last is $last_time\n";

while(1)
{
  print LOG "\n $now waiting for client\n";
# waiting for new client connection.
$client_socket = $socket->accept();

# get the host and port number of newly connected client.
$peer_address = $client_socket->peerhost();
$peer_port = $client_socket->peerport();


# write operation on the newly accepted client.
$ok=1;

while($ok) {
open(LOG,">>/var/log/cdr_relay.ourlog");
print LOG "$now select * from cdr where Guid >= $last_time\n";
@records=`sqlite /var/log/asterisk/cdr.db 'select * from cdr where Guid >= $last_time'`;
 output() if $records[$#records];
 $last=$records[$#records] if $records[$#records];;
 @s=split /\|/, $last;
 $last_time=$s[0];
 open(O,">/var/log/cdr_relay.dat") or die "Cannot $!";
 #print "Debug $last";
 print O $last;
 close O;
 if (($now - $last_output) > (3600 * 12) ) {
  print LOG "$now No recent output $last_output\n";
  
 }
 close LOG;
 sleep 60;
}
}

sub output {
  $now = localtime;
  $last_output=$now;
  print LOG "$now New loop starting at $last $last_time\n\n";
  foreach(@records) {
   next if $_ eq $last; 
   print LOG;
   print $client_socket $_ or $ok=0;
   if (not $ok) {
    print LOG "$now Failed to write to socket\n";
    exit 1;
   }
}
}


If the script dies or the machine is rebooted, cron will restart it within 5 minutes. When cron does start it, the script itself determines if it should stay running or exit immediately. It then sits patiently waiting for a connection, which you can simulate with "telnet operator_ip 3036" if you like.   That will start spitting call data at you as the script loops.

Speaking of loops, most users could sleep much longer than the 60 seconds I used here.  I did that primarily for testing so that I wouldn't have to wait very long to see new data, but even a fifteen minute delay (sleep 900) is not necessarily unreasonable.  The frequency of cron checks could also be lengthened.

What happens at the auditing machine when we are not listening (just after a reboot, for example) ? Nothing horrible:  it complains and keeps trying.  When our script does start, the auditing software recovers and begins collecting again.

In our case, the auditing folks said the data we were sending was fine, they'd parse it out for their needs.  Other software might require you to reformat the data before writing it out - that shouldn't be difficult.

Update: see CRM integration using the AMI. This has been added since the article above was written. Apparently things like OrderlyStats will work with that.



Got something to add? Send me email.





(OLDER)    <- More Stuff -> (NEWER)    (NEWEST)   

Printer Friendly Version

-> -> Operator CDR records


2 comments



Increase ad revenue 50-250% with Ezoic


More Articles by

Find me on Google+

© Anthony Lawrence







Mon Feb 25 19:50:45 2013: 11910   alexit

gravatar


Just a quick question.

In your script you prevent duplicate copies of the script from running by comparing PIDs in a lock file:

##################################
# Check if script is already running
open (L, "/tmp/cdr_pid.lock");
$pid=<L>;
close L;
if ($pid) {
$stat=kill 0, $pid ;
}
chomp $stat;
chomp $pid;
if ($stat and $pid) {
print "\nExiting because $pid exists\n" if $DEBUG;
exit 0;
}

# It's not, so we can continue

open (L, ">/tmp/cdr_pid.lock");
print L "$$\n";
close L;
##################################

Is there any particular reason you would not just lock the script file itself?

##################################
use Fcntl ':flock';
...
flock DATA, LOCK_EX | LOCK_NB or exit 0;
...
__DATA__
##################################






Mon Feb 25 19:53:51 2013: 11911   TonyLawrence

gravatar


No particular reason.. I like to have the pid available in case I want to kill it, but lockf works.

(link)

------------------------
Kerio Connect Mailserver

Kerio Samepage

Kerio Control Firewall

Have you tried Searching this site?

Unix/Linux/Mac OS X support by phone, email or on-site: Support Rates

This is a Unix/Linux resource website. It contains technical articles about Unix, Linux and general computing related subjects, opinion, news, help files, how-to's, tutorials and more.

Contact us





When someone says: "I want a programming language in which I need only say what I wish done", give him a lollipop. (Alan J. Perlis)

Basic happened to be on a GE timesharing system that was done by Dartmouth, and when GE decided to franchise that, it started spreading Basic around just because it was there, not because it had any intrinsic merits whatsoever. (Alan Kay)












This post tagged: