In order to properly manage ACD queues, it is important to be able to keep track of details of call setups and teardowns in much greater detail than traditional call detail records provide. In order to support this,
extensive and detailed tracing of every queued call is stored in the queue log, located (by default) in /var/log/asterisk/queue_log.
For asterisk 1.6.1Above
Create a table queue_log in MYSQL DB
CREATE TABLE `asterisk.queue_log` (
`id` int(10) unsigned NOT NULL auto_increment,
`time` int(10) unsigned default NULL,
`callid` varchar(32) NOT NULL default '',
`queuename` varchar(32) NOT NULL default '',
`agent` varchar(32) NOT NULL default '',
`event` varchar(32) NOT NULL default '',
`data` varchar(255) NOT NULL default '',
`data1` varchar(255) NOT NULL default '',
`data2` varchar(255) NOT NULL default '',
`data3` varchar(255) NOT NULL default '',
`data4` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
);
mysql> select * from queue_log;
+----+------------+--------------+------------------+-------+------------+-------+
| id | time | callid | queuename | agent | event | data |
+----+------------+--------------+------------------+-------+------------+-------+
| 1 | 1198356717 | 1198356717.0 | voipsolutions.ru | NONE | ENTERQUEUE | |serg |
| 2 | 1198356719 | 1198356717.0 | voipsolutions.ru | NONE | ABANDON | 1|1|2 |
+----+------------+--------------+------------------+-------+------------+-------+
Data field values is separated by Pipe(|) symbols
CREATE TABLE IF NOT EXISTS `agent_status` (
`agentId` varchar(40) NOT NULL default '',
`agentName` varchar(40) default NULL,
`agentStatus` varchar(30) default NULL,
`timestamp` timestamp NULL default NULL,
`callid` double(18,6) unsigned default '0.000000',
`queue` varchar(20) default NULL,
PRIMARY KEY (`agentId`),
KEY `agentName` (`agentName`),
KEY `agentStatus` (`agentStatus`,`timestamp`,`callid`),
KEY `queue` (`queue`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `call_status` (
`callId` double(18,6) NOT NULL,
`callerId` varchar(13) NOT NULL,
`status` varchar(30) NOT NULL,
`timestamp` timestamp NULL default NULL,
`queue` varchar(25) NOT NULL,
`position` varchar(11) NOT NULL,
`originalPosition` varchar(11) NOT NULL,
`holdtime` varchar(11) NOT NULL,
`keyPressed` varchar(11) NOT NULL,
`callduration` int(11) NOT NULL,
PRIMARY KEY (`callId`),
KEY `callerId` (`callerId`),
KEY `status` (`status`),
KEY `timestamp` (`timestamp`),
KEY `queue` (`queue`),
KEY `position` (`position`,`originalPosition`,`holdtime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
And then Created a trigger on the DB asterisk
DROP TRIGGER IF EXISTS `asterisk`.`queueEvents`;
DELIMITER //
CREATE TRIGGER `asterisk`.`queueEvents` BEFORE INSERT ON `asterisk`.`queue_log`
FOR EACH ROW BEGIN
IF NEW.event = 'ADDMEMBER' THEN
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES
(NEW.agent,'READY',NEW.time,NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = NEW.time, callid = NULL;
ELSEIF NEW.event = 'REMOVEMEMBER' THEN
INSERT INTO `agent_status` (agentId,agentStatus,timestamp,callid) VALUES
(NEW.agent,'LOGGEDOUT',NEW.time,NULL) ON DUPLICATE KEY UPDATE agentStatus = "LOGGEDOUT", timestamp = NEW.time, callid = NULL;
ELSEIF NEW.event = 'PAUSE' THEN
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES
(NEW.agent,'PAUSE',NEW.time,NULL) ON DUPLICATE KEY UPDATE agentStatus = "PAUSE", timestamp = NEW.time, callid = NULL;
ELSEIF NEW.event = 'UNPAUSE' THEN
INSERT INTO `agent_status` (agentId,agentStatus,timestamp,callid) VALUES
(NEW.agent,'READY',NEW.time,NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = NEW.time, callid = NULL;
ELSEIF NEW.event = 'ENTERQUEUE' THEN
REPLACE INTO `call_status` VALUES (NEW.callid,replace(replace(substring(substring_index(NEW.data, '|', 2), length(substring_index(New.data, '|', 2 - 1)) + 1), '|', ''), '|', ''),'inQue',NEW.time,NEW.queuename,'','','','',0);
ELSEIF NEW.event = 'CONNECT' THEN
UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,holdtime =
replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', '') where callid = NEW.callid;
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES(NEW.agent,NEW.event,NEW.time,NEW.callid)ON DUPLICATE KEY UPDATEagentStatus = NEW.event,timestamp = NEW.time,callid = NEW.callid;
ELSEIF NEW.event in ('COMPLETECALLER','COMPLETEAGENT') THEN
UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,originalPosition =
replace(substring(substring_index(NEW.data, '|', 3), length(substring_index(NEW.data, '|', 3 - 1)) + 1), '|', ''),holdtime =
replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', ''),callduration = replace(substring(substring_index(NEW.data, '|', 2), length(substring_index(NEW.data, '|', 2 - 1)) + 1), '|', '')
where callid = NEW.callid;
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,NEW.event,NEW.time,NULL) ON DUPLICATE KEY
UPDATE agentStatus = "READY", timestamp = NEW.time, callid = NULL;
ELSEIF NEW.event in ('TRANSFER') THEN
UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,holdtime = replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', ''),callduration =
replace(substring(substring_index(NEW.data, '|', 3), length(substring_index(NEW.data, '|', 3 - 1)) + 1), '|', '') where callid = NEW.callid;
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES(NEW.agent,'READY',NEW.time,NULL)ON DUPLICATE KEY
UPDATE agentStatus = "READY",timestamp = NEW.time,callid = NULL;
ELSEIF NEW.event in ('ABANDON','EXITEMPTY') THEN
UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,position = replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', ''),originalPosition =
replace(substring(substring_index(NEW.data, '|', 2), length(substring_index(NEW.data, '|', 2 - 1)) + 1), '|', ''),holdtime = replace(substring(substring_index(NEW.data, '|', 3), length(substring_index(NEW.data, '|', 3 - 1)) + 1), '|', '')
where callid = NEW.callid;
ELSEIF NEW.event = 'EXITWITHKEY'THEN
UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,position = replace(substring(substring_index(NEW.data, '|', 2), length(substring_index(NEW.data, '|', 2 - 1)) + 1), '|', ''),keyPressed =
replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', '') where callid = NEW.callid;
ELSEIF NEW.event = 'EXITWITHTIMEOUT' THEN
UPDATE `call_status` SET callid = NEW.callid,status = NEW.event,timestamp = NEW.time,queue = NEW.queuename,position =
replace(substring(substring_index(NEW.data, '|', 1), length(substring_index(NEW.data, '|', 1 - 1)) + 1), '|', '')
where callid = NEW.callid;
END IF;
END
//
DELIMITER ;
