The time a call enter a queue

Asterisk Asterisk Support
1

I want to have a report about how much time each caller wait in call queues to be answered by agents. I want to have this report from database so I need a way to query this waiting time.

is there a way to do this?

2

You could add Asterisk’s queue log, but it writes a record for each entry. The backend reporting is messier than the following.

Create a queue_log table:

CREATE TABLE IF NOT EXISTS `queue_log` (
  `queue_id` int(11) NOT NULL AUTO_INCREMENT,
  `queue_clid` varchar(64) NOT NULL,
  `queue_name` varchar(128) DEFAULT NULL,
  `queue_start` datetime DEFAULT NULL,
  `queue_stop` datetime DEFAULT NULL,
  `queue_stop_type` varchar(1) DEFAULT NULL,
  `queue_agent_phone` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`queue_id`),
  KEY `queue_clid` (`queue_clid`,`queue_start`,`queue_stop`),
  KEY `queue_stop_type` (`queue_stop_type`),
  KEY `queue_name` (`queue_name`),
  KEY `queue_agent_phone` (`queue_agent_phone`)
) ENGINE=MyISAM

Insert a record just before the caller enters the queue and have an AGI run when the call exits the queue (queue_log.php) (you could also allow the db to create the queue_start time):

same    => n,Set(currtime=${STRFTIME(${EPOCH},,%Y-%m-%d %H:%M:%S)})
same    => n(agentexists),Set(ODBC_WRITESQL()=insert into queue_log (queue_clid\,queue_name\,queue_start) values('${CALLERID(num)}'\,'${queueid}'\,'${currtime}'))
same    => n,Queue(${ARG1},t,,,,queue_log.php)

queue_log.php:

#!/usr/bin/php -q
<?php

 $agivars = array();
 while (!feof(STDIN)) {
     $agivar = trim(fgets(STDIN));
     if ($agivar === '') {
         break;
     }
     $agivar = explode(':', $agivar);
     $agivars[$agivar[0]] = trim($agivar[1]);
 }
 extract($agivars);

$stdin = fopen('php://stdin', 'r');
$stdout = fopen( 'php://stdout', 'w' );

$currtime = date('Y-m-d H:i:s');

mysql_connect("localhost", "db_username", "db_password");
mysql_select_db("asterisk") or die( "Unable to select database");

$memberinterface = execute_agi("GET VARIABLE MEMBERINTERFACE");

$sql = "UPDATE queue_log set queue_stop = '$currtime', queue_stop_type = 'a', queue_agent_phone='$memberinterface' where queue_clid='$agi_callerid' and queue_stop IS NULL";
mysql_query($sql); 
mysql_close();

exit;

function execute_agi($command) {
	GLOBAL $stdin, $stdout;
	fputs( $stdout, $command . "\n" );
	fflush( $stdout );
	$resp = trim(fgets( $stdin, 4096 ));

	$exp = explode('(', $resp);
	$ext = str_replace(')','', $exp[1]);
	
	return $ext;
}
?>
2 Likes
4

thanks a lot… thats great… but is there a simpler way to do this? I don’t have enough permission to do these steps.

5

What permissions are you missing? At minimum, you’ll need to create a table and the ability to modify the config files. With what I listed, you’ll also need write permissions somewhere on the server.

6

Waiting time it not provided in a variable, so you need to tweak your system in order to get this information, you can use Asterisk QueueCallerJoin manager event plus some scripting in order to get this information, if you want a report you need to be able to write and read a DB with such information