[Asterisk-Users] Queue_log stats

[William Lloyd]

I'm in search of useful ACD type statistics from the queues.  Ie talk  
time, ratio's, dropped calls etc.

The flat file queue_log is nice, but more useful would be the data in  
Postgres or Mysql.  Unfortunately the queue module does not yet  
support ODBC DB logging (yet).  In the meantime this quick and dirty  
hack gets the job done.

Replace the flat file with a unix named pipe.  Works with all  
versions of asterisk. Removes the need for daily cron job to parse  
and move the log file to SQL for later processing.

This script could be easily adapted for other flat file type logging.

If anybody has some PHP, perl or whatever code that does asterisk  
queue stats or has a question abut this script please contact me.

-bill
wlloyd at slap.net

#!/usr/bin/perl -w
#
# wlloyd at slap.net

# The asterisk version indpendant way to get queue stats into Mysql,  
Postgres
# or whatever is supported by Perl DBI

# It's all about named pipes

# to setup this software
# stop asterisk
# rm /var/log/asterisk/queue_log
# mkfifo /var/log/asterisk/queue_log

# make sure permissions are setup
# chmod 777 /var/log/asterisk/queue_log

# run this program as root or under another user as you see fit.
# should start BEFORE asterisk.  Add to /etc/rc.d/rc.local or whatever

# restart asterisk

# requires a DB table like the following..
# CREATE TABLE csr_queue (
#  qname varchar(30) default NULL,
#  agent varchar(30) default NULL,
#  action text,
#  info1 text,
#  info2 text,
#  info3 text,
#  timestamp int(11) NOT NULL default '0',
#  id tinytext NOT NULL
#) TYPE=MyISAM;

use DBI;
use IO::File;

my $opt_debug = 0;

# if you want postgres change this to "Pg"
my $db_type = "mysql";
my $db_host = "127.0.0.1";
my $db_user_name = 'username';
my $db_password = 'password';
my $db_database = 'asteriskstat';

my $dbh = DBI->connect("DBI:$db_type:dbname=$db_database;host= 
$db_host;", $db_user_name, $db_password);

open(FIFO, "< /var/log/asterisk/queue_log")        or die "Can't open  
queue_log : $!\n";

while (1) {

     $message = <FIFO>;
     next unless defined $message;   # interrupted or nothing logged
     chomp $message;

     # remove chars that will cause DB problems
     $message =~ s/\"\'//g;

     @data = split(/\|/,$message);

     # these messages are almost useless for my purposes
     next if ($data[4] eq "QUEUESTART" );
     next if ($data[4] eq "CONFIGRELOAD" );

     if (!defined($data[5])) {
       $data[5] = '';
     }
     if (!defined($data[6])) {
       $data[6] = '';
     }
     if (!defined($data[7])) {
       $data[7] = '';
     }

     my $sql = "INSERT INTO csr_queue (timestamp, id, qname, agent,  
action, info1, info2, info3) VALUES ('$data[0]', '$data[1]', '$data 
[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]', '$data[7]')";

     print "$sql \n\n" if ($opt_debug);

     $dbh->do($sql);

# if you want an actual logfile you might want to uncomment this
#        if ( open(LOG, ">> /var/log/asterisk/queue_log_real") ) {
#            print LOG "$message\n";
#            close(LOG);
#        } else {
#            warn "Couldn't log to /var/log/asterisk_queue_log: $!\n";
#        }
#
}

$dbh->disconnect();

exit 0;