Asterisk Queues Log In to Database

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 ;