[Asterisk-Users] Programs to parse queue_log
Mario.Spoljar at hypo-alpe-adria.com
Mario.Spoljar at hypo-alpe-adria.com
Wed May 25 04:53:56 MST 2005
> > What have other admins done to retrieve detailed call information about
> > the queue system? Anyone develop their own that they don't mind
sharing?
>
You can try this perl script it was useful for me. After parsing I do
reports based on generated queue_statistic.csv in Excel...
----cut here----
#!/usr/bin/perl
#
# Asterisk Queue Analizer
# Uses queue_log to analize call center activities (agents/queues)
# (C) 2005 Mario Spoljar - spoljar at spoljar.averi.hr
#
# This program is free software, distributed under the terms of the
# GNU General Public License
#
# scriptname: qanalize.pl
#
#
# TO DO:
# - switches for:
# epoch/ordinary output date format
# silent mode
# file to analize
# - cron job description
# - writing to postgresql database
# - rotate output to more then one output file, depending on date
# - xls report output
#
# Change log:
#-------------
# 12/02/2005
# * added functionality to write last analized record to temporary file
# now script can be called more then once without duplicate output
#
# 10/02/2005
# * improved abilitiy to determine times in case of multiple queues in
and outs
#
#
#
#
# Output fileds:
#----------------
# $outrecord[0] - CallID
# $outrecord[1] - Date & Time - equal to Event UniqueID timestamp
# $outrecord[2] - Before Queue - time before call enter queue
# $outrecord[3] - Waiting in Queue
# $outrecord[4] - Talking in Queue
# $outrecord[5] - Answerd yes/no
# $outrecord[6] - Called queue - first queue if more then one
# $outrecord[7] - Answerd by - agent or station
# $outrecord[8] - Caller info - CLID
# $outrecord[9] - Start queue position
# $outrecord[10] - End queue position
# $outrecord[11] - End cause: Abandon, CompleteByAgent, CompleteByCaller
# $outrecord[12] - Queue name - last queue (if call is routed through more
then one queue)
# $outrecord[13] - Total call time (before queue + waiting in queue +
talking)
# $outrecord[14] - Connect time - time when call is cannected
# $outrecord[15] - EnterQueue count how many times call enter queues
# $outrecord[16] - EnterQueue flow - how call was routedthrough queues ex:
q201=>all-agents
# $outrecord[17] - Agent session durration - duration in seconds
# $outrecord[18] - Agent Logout time - when agent logout
use Time::Local;
########################################
# Variables #
########################################
#
# File handles
#
# Which file will be parsed - In file
$file = "queue_log";
# Temp file
$tmp_out = "tmp_analize_queue_log.txt";
# Out file
$queue_statistic = "queue_statistic.csv";
# Max Timestamp value - file
$lastUniqueID = ".maxUniqueID";
# Max call duration in seconds (1800 = 30 min)
# in this period of time all call have to be finihed in order to
# parse log correctly (Total call time < then max_call_durration)
$max_call_durration = 1800;
#
# Global temporary vairables
#
$tmpoutrecord = ""; # previous outrecord for
same call
#
# Default strigs in analized log
#
$AnswerString_caseAbandoned = "NO";
$EndCauseString_caseAbandoned = "ABANDONED";
$AnsweredBy_caseAbandoned = "*NOBODY*";
$AnswerString_caseConnected = "YES";
$AnswerString_caseCompleteAgent = "YES";
$EndCauseString_caseCompleteAgent = "COMPLETE_BY_AGENT";
$AnswerString_caseCompleteCaller = "YES";
$EndCauseString_caseCompleteCaller = "COMPLETE_BY_CALLER";
$AnswerString_caseTransfer = "YES";
$EndCauseString_caseTransfer = "TRANSFERED";
$AnswerString_caseAgentCallBackLogoff = "SERVICE";
$EndCauseString_caseAgentCallBackLogoff = "AGENT_SESSION_TERMINATED";
$AnswerString_caseExitWithTimeout = "NO";
$EndCauseString_caseExitWithTimeout = "TIMED_OUT";
$AnswerString_caseExitWithKey = "NO";
$EndCauseString_caseExitWithKey = "PRESSED KEY";
############################################
# Main functions
############################################
sub prepare_tmp_file {
#
# reareange queue_log, skip evens with uniqueid = 'NONE'
#
# from min_uniqueid to max_uniqueid
#
# queue_log - fileds before :
#
TimeStamp|UniqueID|QueueName|AgentName|Event|[Parameter1|Parameter2|Parameter3]
#
# queue_log - fileds after rearanging:
#
UniqueID|TimeStamp|QueueName|AgentName|Event|[Parameter1|Parameter2|Parameter3]
#
($min_uniqueid, $max_uniqueid) = ($_[0], $_[1]);
open(FH_in,$file) || die "cannot open: $!";
open(FH_out,">$tmp_out") || die "cannot open: $!";
while (<FH_in>){
#
# reverse filed 0 and 1 in list because we wolud like to sort on
field UniqueID originaly stored on filed 1
#
@list = split(/\|/,$_);
if ($list[0] != "NONE") {
#
# dont print rows with UniqueID = NONE - these event belongs to
# restart functions and are not analised there
#
@uniqueid = split(/\./,$list[1]);
if($uniqueid[0] > $min_uniqueid && $uniqueid[0] <= $max_uniqueid){
$tmp = $list[1];
$list[1]=$list[0];
$list[0]=$tmp;
print FH_out join("|", at list);
}
}
}
close(FH_in) || die "cannot close: $!";
close(FH_out) || die "cannot close: $!";
return 0;
}
sub check_uniqueid_range(){
#
# if exist .uniqueID file, get set min_unique_id value from file
# otherwise start from 0
#
# return:
# @return = ($min_timestamp, $max_timestamp)
#
# All timstamp greater then $max_timestamp are discarded
$now_time = timelocal(localtime());
$max_timestamp = $now_time - $max_call_durration;
$min_timestamp = 0;
$return = open(F,$lastUniqueID) || 0;
if ($return != 0){
# if file egzist
$min_timestamp = scalar<F>;
}
# write new max_timestamp
close (F);
return ($min_timestamp, $max_timestamp);
}
sub sort_tmp_file {
#
# sort tmp file to have UniqueID's line one after another
#
open(FH_tmp,$tmp_out) || die "cannot open: $!";
@array=<FH_tmp>;
close(FH_tmp) || die "cannot close: $!";
open(FH_tmp,">$tmp_out") || die "cannot open: $!";
@sortedarray=sort(@array);
foreach $line(@sortedarray){
print FH_tmp $line;
}
close(FH_tmp) || die "cannot close: $!";
}
sub analize_queue_events {
# analize event from tmp file and return out values for outrecord fields
# outrecord fieldds definition:
# $outrecord[0] - CallID
# $outrecord[1] - Date & Time
# $outrecord[2] - Before Queue (before queue + waiting in queue + talking)
# $outrecord[3] - Waiting in Queue
# $outrecord[4] - Talking in Queue
# $outrecord[5] - Answerd yes/no
# $outrecord[6] - Called queue
# $outrecord[7] - Answerd by
# $outrecord[8] - Caller info
# $outrecord[9] - Start queue position
# $outrecord[10] - End queue position
# $outrecord[11] - End cause: Abandon, CompleteByAgent, CompleteByCaller
# $outrecord[12] - Queue name
# $outrecord[13] - Total call time (before queue + waiting in queue +
talking)
# $outrecord[14] - Connect time - when is call cannected
# $outrecord[15] - EnterQueue count how many times call enter queues
# $outrecord[16] - EnterQueue flow ex: (q201 x 2)=>(all-agents) x 1
# $outrecord[17] - Agent session durration
# $outrecord[18] - Agent Logout time
# input event
@toAnalize = split(/\|/, $_[0]);
# initialize outrecord
@outrecord=("","","","","","","","","","","","","","","","","","");
# 1, 2, 3, 4, 5, 6, 7, 8, 9,10,11,12,13,14,15,16,17,18
@blank = @outrecord;
# save call ID
$outrecord[0] = $toAnalize[0];
# save Date & Time in Epoch format
@tmp=split(/\./,$toAnalize[0]);
$outrecord[1] = $tmp[0];
#
# Analize events for each call
#
if ($toAnalize[4] eq "ENTERQUEUE") {
# Call enter queue
# 1107285621.246|1107285666|all-agents|NONE|ENTERQUEUE||*201*101
# ENTERQUEUE|url|callerid
#
# Calculate:
# Time Before Queue
@tmp=split(/\./,$toAnalize[0]); # divide UniqueID discribing
date/time form other part
@tmp2 = split(/\|/,$tmpoutrecord);
if ($tmp2[0] eq $outrecord[0]){
# if time befor queue does not egzist calculate it and take into
account if this is not first enterqueue
$outrecord[2]= $toAnalize[1] - $tmp2[1] - $tmp2[2] - $tmp2[3];
} else {
$outrecord[2] = $toAnalize[1] - $tmp[0];
}
# Called queue - queue originaly called just firs in liste
if ($tmp2[0] ne $outrecord[0] || $tmp2[6] eq "" || $tmp2[6] eq
"CalledQueue"){
$outrecord[6]= $toAnalize[2];
}
# QueueName
$outrecord[12] = $toAnalize[2];
# CallerInfo
$outrecord[8] = $toAnalize[6];
# EnterQueue count
$outrecord[15] = 1; # recalc durring write
# EnterQueue flow
$outrecord[16] = $toAnalize[2]; # recalc durring write
}
if ($toAnalize[4] eq "ABANDON") {
# Abandon queue call
#
# Event:
# 1107285621.246|1107285824|all-agents|NONE|ABANDON|1|1|158
#
# ABANDON|position|origposition|waittime
#
# Calculate:
# Waiting in queue
$outrecord[3] = $toAnalize[7];
# Talking in queue
$outrecord[4] = 0;
# Answered = NO
$outrecord[5] = $AnswerString_caseAbandoned;
# Answered By
$outrecord[7] = $AnsweredBy_caseAbandoned;
# Start Queue Position
$outrecord[9] = $toAnalize[6];
# End Queue Position
$outrecord[10] = $toAnalize[5];
# End cause
$outrecord[11] = $EndCauseString_caseAbandoned;
# QueueName
$outrecord[12] = $toAnalize[2];
}
if ($toAnalize[4] eq "CONNECT") {
# Call connected to agent
#
# Event:
# 1107283841.237|1107283858|q201|Agent/101|CONNECT|9
#
# CONNECT|holdtime
#
# Calculate :
# Waiting in queue
$outrecord[3] = $toAnalize[5];
# Answered = YES
$outrecord[5] = $AnswerString_caseConnected;
# Answered by
$outrecord[7] = $toAnalize[3];
# QueueName
$outrecord[12] = $toAnalize[2];
# call conected at
$outrecord[14] = $toAnalize[1];
}
if ($toAnalize[4] eq "COMPLETEAGENT") {
# Agent completed call
#
# Event
# 1107282786.227|1107282835|q201|Agent/101|COMPLETEAGENT|21|20
#
# COMPLETEAGENT | holdtime | calltime | origposition
#
# Calculate:
# Waiting in queue
$outrecord[3] = $toAnalize[5];
# Talking in queue
$outrecord[4] = $toAnalize[6];
# Answered = NO
$outrecord[5] = $AnswerString_caseCompleteAgent;
# Answered By
$outrecord[7] = $toAnalize[3];
# Start Queue Position
$outrecord[9] = $toAnalize[7];
# End cause
$outrecord[11] = $EndCauseString_caseCompleteAgent;
# QueueName
$outrecord[12] = $toAnalize[2];
}
if ($toAnalize[4] eq "COMPLETECALLER") {
# Agent completed call
#
# Event:
# 1107278955.128|1107279033|all-agents|Agent/106|COMPLETECALLER|14|19
#
# COMPLETECALLER | holdtime | calltime | origposition
#
# Calculate:
# Waiting in queue
$outrecord[3] = $toAnalize[5];
# Talking in queue
$outrecord[4] = $toAnalize[6];
# Answered = NO
$outrecord[5] = $AnswerString_caseCompleteCaller;
# Answered By
$outrecord[7] = $toAnalize[3];
# Start Queue Position
$outrecord[9] = $toAnalize[7];
# End cause
$outrecord[11] = $EndCauseString_caseCompleteCaller;
# QueueName
$outrecord[12] = $toAnalize[2];
}
if ($toAnalize[4] eq "TRANSFER"){
# Agent transfered call
#
# Event:
# 1107283841.237|1107283868|q201|Agent/101|TRANSFER|106|default
#
# TRANSFER | extension | context
#
# Calculate:
# Talking in queue
@tmp = split(/\|/,$tmpoutrecord);
$outrecord[4] = $toAnalize[1] - $tmp[14]; # Event transfer time
- event connect time
# Answered = NO
$outrecord[5] = $AnswerString_caseTransfer;
# Answered By
$outrecord[7] = $toAnalize[3];
# End cause
$outrecord[11] = $EndCauseString_caseTransfer;
# QueueName
$outrecord[12] = $toAnalize[2];
}
if ($toAnalize[4] eq "EXITWITHTIMEOUT"){
# Queue TIMOUT value reached
#
# Event:
# 1097512987.0|1097513021|info-lijek-q|NONE|EXITWITHTIMEOUT|1
#
# EXITWITHTIMEOUT | position
#
# Calculate:
# Waiting in queue = queue TIMEOUT (if more then one add all
timouts...)
# this is case if you get from one queueu to another
@tmp = split(/./,$toAnalize[0]);
@tmp2 = split(/\|/,$tmpoutrecord);
if ($tmp2[0] eq $outrecord[0]){
$outrecord[3] = $toAnalize[1] - $tmp2[1] - $tmp2[2] - $tmp2[3];
# Queue timeout = exitWithTimout - time befor queue - time enter queue
} else {
$outrecord[3] = $toAnalize[1] - $tmp[0];
}
# Talking in queue
$outrecord[4] = 0;
# Answered = NO
$outrecord[5] = $AnswerString_caseExitWithTimeout;
# Answered By
$outrecord[7] = $toAnalize[3];
# Start Queue Position
$outrecord[9] = $toAnalize[5];
# End cause
$outrecord[11] = $EndCauseString_caseExitWithTimeout;
# QueueName
$outrecord[12] = $toAnalize[2];
}
if ($toAnalize[4] eq "EXITWITHKEY"){
# Caller dialed some digit durring hold
#
# Event:
# 1107283841.237|1107283868|q201|NONE|EXITWITHKEY||1
#
# EXITWITHKEY | key | position
#
# Calculate:
# Talking in queue
$outrecord[4] = 0;
# Answered = NO
$outrecord[5] = $AnswerString_caseExitWithKey;
# Answered By
$outrecord[7] = $toAnalize[3];
# Start Queue Position
$outrecord[9] = $toAnalize[6];
# End cause
$outrecord[11] = $EndCauseString_caseExitWithKey."-".$toAnalize[5];
# QueueName
$outrecord[12] = $toAnalize[2];
}
if ($toAnalize[4] eq "AGENTCALLBACKLOGOFF"){
# Agent callback loged offl
#
# Event:
#
1107283438|1107283424.235|NONE|Agent/106|AGENTCALLBACKLOGOFF|106 at default|64|
#
# AGENTCALLBACKLOGOFF | context | logintime | reason
#
# Calculate:
# Answered = NO
$outrecord[5] = $AnswerString_caseAgentCallBackLogoff;
# Answered By
$outrecord[7] = $toAnalize[3];
# End cause
$outrecord[11] = $EndCauseString_caseAgentCallBackLogoff;
# Session Duration
if ($toAnalize[6] ne $toAnalize[1]) { # in case that agent twice
entered logout sesion does not count this!
$outrecord[17] = $toAnalize[6];
}
# SessionEnded
$outrecord[18] = $toAnalize[1];
}
# remove \n at the end of all elements in @outrecord
chomp(@outrecord);
#
# not supported events does not return any value..
#
if ($toAnalize[4] eq "QUEUESTART" || $toAnalize[4] eq "CONFIGRELOAD" ||
$toAnalize[4] eq "AGENTCALLBACKLOGIN"){
return (join("|", at blank));
} else {
return (join("|", at outrecord));
}
}
sub format_date{
if ($_[0] ne ""){
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
localtime($_[0]);
$_ = sprintf("%04d/%02d/%02d
%02d:%02d:%02d",$year+1900,$mon+1,$mday,$hour,$min,$sec);
return $_;
}
}
sub write_to_file{
# how to write statistic data to file
# modifivcation according to previous and present values of parameters
# outrecord definition:
# $outrecord[0] - CallID
# $outrecord[1] - Date & Time
# $outrecord[2] - Before Queue
# $outrecord[3] - Waiting in Queue
# $outrecord[4] - Talking in Queue
# $outrecord[5] - Answered yes/no
# $outrecord[6] - Called queue
# $outrecord[7] - Answerd by
# $outrecord[8] - Caller info
# $outrecord[9] - Start queue position
# $outrecord[10] - End queue position
# $outrecord[11] - End cause: Abandon, CompleteByAgent, CompleteByCaller
# $outrecord[12] - Queue name
# $outrecord[13] - Total call time (before queue + waiting in queue +
talking)
# $outrecord[14] - Connect time - when is call cannected
# $outrecord[15] - EnterQueue count how many times call enter queues
# $outrecord[16] - EnterQueue flow ex: (q201 x 2)=>(all-agents x 1)
# $outrecord[17] - Agent session durration
# $outrecord[18] - Agent Logout time
@prev = split(/\|/,$_[0]);
@current = split(/\|/,$_[1]);
if ($prev[0] eq $current[0]){
#
# if UnigueID(@prev) eq UniqueID(current)
# just update @prev fields according to analized current event
#
#
# Procedures for updating @prev fields
#
# if 'QueueName' exist in @current overwrite in @prev
if($prev[12] ne "" && $current[12] ne "" ){
$prev[12] = $current[12];
}
if($current[15] ne "" ){
$prev[15] = $prev[15] + $current[15]; # count
another queue
# create flow record!
$prev[16] = $prev[16]."->".$current[16];
}
# update before queue time
# take in consideration if call exit with timed out form another queue
if($current[2] ne "" ){
$prev[2] += $current[2];
}
# update waiting time
# take in consideration if call exit with timed out form another queue
if($current[3] ne "" ){
$prev[3] += $current[3];
}
# return updated value of @prev
return join("|", at prev);
} else {
#
# procedure to write final record to file
#
if ($prev[0] ne "") {
open (FH_stat,">>$queue_statistic") || die "Could not open:
$!";
# format date & time fields in human readeable format
$prev[1]= format_date($prev[1]);
$prev[14]= format_date($prev[14]);
$prev[18]= format_date($prev[18]);
# calculate total call time
$prev[13] = $prev[2] + $prev[3] + $prev[4];
# write delimited by comma - CSV to file
print FH_stat join(",", at prev)."\n";
close (FH_stat);
# record written
}
return join("|", at current);
}
}
##############################################
#
# MAIN PROCEDURE
#
##############################################
print "Start analizing $file...\n"."-" x 30 . "\n";
print "checking range for UniqueIDs...";
@a=check_uniqueid_range();
print "\t\t<$a[0] till $a[1]>\n";
print "creating tmp file...";
prepare_tmp_file(@a);
print "\t\t\tCREATED\n";
print "sorting tmp file...";
sort_tmp_file();
print "\t\t\tSORTED\n";
print "creating queue & agent log file...";
# print captions
@naslovi=("CallID", "Date-Time", "BeforeQueueDur", "WaitingInQueue",
"TalkingInQueue", "Answered","CalledQueue", "AnswerdBy", "CallerInfo",
"StartPosition", "EndPosition", "EndCause", "QueueName", "TotalCallTime",
"ConnectTime", "EnterQueueCount", "EnterQueueFLow", "AgentSessionDurration"
,"AgentLogoutTime\n");
open (FH,">$queue_statistic") || die "Could not open: $!";
print FH join(",", at naslovi); # write delimited by coma -
CSV
close (FH);
print "\tCaptions created";
open (FH_tmp,$tmp_out) || die "Could not open: $!";
$prvi = scalar<FH_tmp>;
$tmpoutrecord = analize_queue_events($prvi);
print "\n Progres:[";
while (<FH_tmp>) {
$currentoutrecord = analize_queue_events($_);
$tmpoutrecord = write_to_file($tmpoutrecord, $currentoutrecord);
print "#"
}
write_to_file($tmpoutrecord, "Print last record!!");
close (FH_tmp);
print "]\n";
print "Created file:\t\t\t\t$queue_statistic\n";
print "New uniqueid max is:\t\t\t$a[1]\n";
open (F, ">$lastUniqueID") || die "cannot open $lastUniqueID. $!";
print F $a[1];
close (F);
print "deleting $tmp_out...\n";
# unlink ($tmp_out) || die "cannot delete $tmp_out: $!";
print "...finished\n";
----cut here----
More information about the asterisk-users
mailing list