Asterisk queue_log on MySQL system

Converting to a more readable format for Asterisk 13

added `agent` field in `call_status` table (by linvinus)
`callid` field moved to varchar(32) in all tables (same as in `cdr` table)
modified `update_processed` and `bi_queueEvents` trigges for data1,data2,data3 fields

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` varchar(32) DEFAULT NULL,
`queue` varchar(20) DEFAULT NULL,
PRIMARY KEY (`agentId`),
KEY `agentName` (`agentName`),
KEY `agentStatus` (`agentStatus`,`timestamp`,`callid`),
KEY `queue` (`queue`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `call_status` (
`callId` varchar(32) DEFAULT NULL,
`callerId` varchar(13) NOT NULL,
`status` varchar(30) NOT NULL,
`timestamp` timestamp NULL DEFAULT NULL,
`queue` varchar(25) NOT NULL,
`agent` varchar(32) NOT NULL DEFAULT '',
`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`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `queue_log_processed` (
`recid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`origid` int(10) unsigned NOT NULL,
`callid` varchar(32) NOT NULL DEFAULT '',
`queuename` varchar(32) NOT NULL DEFAULT '',
`agentdev` varchar(32) NOT NULL,
`event` varchar(32) NOT NULL DEFAULT '',
`data1` varchar(128) NOT NULL,
`data2` varchar(128) NOT NULL,
`data3` varchar(128) NOT NULL,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`recid`),
KEY `data1` (`data1`),
KEY `data2` (`data2`),
KEY `data3` (`data3`),
KEY `event` (`event`),
KEY `queuename` (`queuename`),
KEY `callid` (`callid`),
KEY `datetime` (`datetime`),
KEY `agentdev` (`agentdev`),
KEY `origid` (`origid`)
) DEFAULT CHARSET=utf8;

DROP TRIGGER IF EXISTS `asteriskcdrdb`.update_processed;
DELIMITER //
CREATE TRIGGER `asteriskcdrdb`.`update_processed` AFTER INSERT ON `asteriskcdrdb`.`queue_log`
FOR EACH ROW BEGIN
INSERT INTO queue_log_processed (callid,queuename,agentdev,event,data1,data2,data3,datetime)
VALUES (NEW.callid,NEW.queuename,NEW.agent,NEW.event,NEW.data1,NEW.data2,NEW.data3,NEW.time);
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `asteriskcdrdb`.`bi_queueEvents`;
DELIMITER //
CREATE TRIGGER `asteriskcdrdb`.`bi_queueEvents` BEFORE INSERT ON `asteriskcdrdb`.`queue_log`
FOR EACH ROW BEGIN
IF NEW.event = 'ADDMEMBER' THEN
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid,queue) VALUES (NEW.agent,'READY',NEW.time,NULL,NEW.queuename) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = NEW.time, callid = NULL, queue = NEW.queuename;
ELSEIF NEW.event = 'REMOVEMEMBER' THEN
INSERT INTO `agent_status` (agentId,agentStatus,timestamp,callid,queue) VALUES (NEW.agent,'LOGGEDOUT',NEW.time,NULL,NEW.queuename) ON DUPLICATE KEY UPDATE agentStatus = "LOGGEDOUT", timestamp = NEW.time, callid = NULL, queue = NEW.queuename;
ELSEIF NEW.event = 'AGENTLOGIN' THEN
INSERT INTO `agent_status` (agentId,agentStatus,timestamp,callid,queue) VALUES (NEW.agent,'LOGGEDIN',NEW.time,NULL,NEW.queuename) ON DUPLICATE KEY UPDATE agentStatus = "LOGGEDIN", timestamp = NEW.time, callid = NULL, queue = NEW.queuename;
ELSEIF NEW.event = 'AGENTLOGOFF' THEN
INSERT INTO `agent_status` (agentId,agentStatus,timestamp,callid,queue) VALUES (NEW.agent,'LOGGEDOUT',NEW.time,NULL,NEW.queuename) ON DUPLICATE KEY UPDATE agentStatus = "LOGGEDOUT", timestamp = NEW.time, callid = NULL, queue = NEW.queuename;
ELSEIF NEW.event = 'PAUSE' THEN
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid,queue) VALUES (NEW.agent,'PAUSE',NEW.time,NULL,NEW.queuename) ON DUPLICATE KEY UPDATE agentStatus = "PAUSE", timestamp = NEW.time, callid = NULL, queue = NEW.queuename;
ELSEIF NEW.event = 'UNPAUSE' THEN
INSERT INTO `agent_status` (agentId,agentStatus,timestamp,callid,queue) VALUES (NEW.agent,'READY',NEW.time,NULL,NEW.queuename) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = NEW.time, callid = NULL, queue = NEW.queuename;
ELSEIF NEW.event = 'ENTERQUEUE' THEN
REPLACE INTO `call_status` VALUES
(NEW.callid,NEW.data2,
'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 = NEW.data1,
agent = NEW.agent
where callid = NEW.callid;
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid,queue) VALUES
(NEW.agent,NEW.event,
NEW.time,
NEW.callid,
NEW.queuename)
ON DUPLICATE KEY UPDATE
agentStatus = NEW.event,
timestamp = NEW.time,
callid = NEW.callid,
queue = NEW.queuename;
ELSEIF NEW.event in ('COMPLETECALLER','COMPLETEAGENT') THEN
UPDATE `call_status` SET
callid = NEW.callid,
status = NEW.event,
timestamp = NEW.time,
queue = NEW.queuename,
originalPosition = NEW.data3,
holdtime = NEW.data1,
callduration = NEW.data2,
agent = NEW.agent
where callid = NEW.callid;
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid,queue) VALUES (NEW.agent,NEW.event,NEW.time,NULL,NEW.queuename) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = NEW.time, callid = NULL, queue = NEW.queuename;
ELSEIF NEW.event in ('TRANSFER') THEN
UPDATE `call_status` SET
callid = NEW.callid,
status = NEW.event,
timestamp = NEW.time,
queue = NEW.queuename,
holdtime = NEW.data1,
callduration = NEW.data3,
agent = NEW.agent
where callid = NEW.callid;
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid,queue) VALUES
(NEW.agent,'READY',NEW.time,NULL,NEW.queuename)
ON DUPLICATE KEY UPDATE
agentStatus = "READY",
timestamp = NEW.time,
callid = NULL,
queue = NEW.queuename;
ELSEIF NEW.event in ('ABANDON','EXITEMPTY') THEN
UPDATE `call_status` SET
callid = NEW.callid,
status = NEW.event,
timestamp = NEW.time,
queue = NEW.queuename,
position = NEW.data1,
originalPosition = NEW.data2,
holdtime = NEW.data3,
agent = NEW.agent
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 = NEW.data2,
keyPressed = NEW.data1,
agent = NEW.agent
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 = NEW.data1,
agent = NEW.agent
where callid = NEW.callid;
END IF;
END
//
DELIMITER ;

Asterisk 1.8

The table format and behavior of realtime queue_log storage has changed for Asterisk 1.8.

More details: https://issues.asterisk.org/view.php?id=17082

SVN Trunk and Asterisk 1.6.x

Current SVN trunk (Revision 94782 from 12-26-07 09:54), supports storage of queue_log in your RT engine.

More details: https://issues.asterisk.org/jira/browse/ASTERISK-11103

Note: The table structure referenced in the above link is no longer accurate (use the structure below). The time column must be char(10), or you will receive an error like this:

WARNING[25801] res_config_mysql.c: Realtime table general@queue_log: column 'time' cannot be type 'int(10) unsigned' (need char)

Sample queue_log table for MySQL:

CREATE TABLE `bit_queue_log` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `time` char(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 '',
  PRIMARY KEY (`id`)
);

queue_log message consists of 2 parts: constant and variable. Constant part is split among corresponding fields (time, callid, queuename, agent, event). Variable data is stored in `data` field as is, so you’ll meet ‘|’ there. Example:

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 |
+----+------------+--------------+------------------+-------+------------+-------+

to activate RT logging add a line like

queue_log => mysql,asterisk

to your extconfig.conf

Note: Asterisk 1.6 changed the way that the extconfig.conf file references the MySQL database. You now specify the context name in extconfig.conf, NOT the database name.

Converting to a more readable format

So, you’ve got queue_log created from realtime, but it doesn’t make easy parsing. Here’s a new table definition and some SQL code to pretty it up for you. Note that locking etc. aren’t done – we run this job at night when we know there aren’t any calls anyway, but your situation may differ.

CREATE TABLE IF NOT EXISTS `queue_log_processed` (
`recid` int(10) unsigned NOT NULL auto_increment,
`origid` int(10) unsigned NOT NULL,
`callid` varchar(32) NOT NULL default '',
`queuename` varchar(32) NOT NULL default '',
`agentdev` varchar(32) NOT NULL,
`event` varchar(32) NOT NULL default '',
`data1` varchar(128) NOT NULL,
`data2` varchar(128) NOT NULL,
`data3` varchar(128) NOT NULL,
`datetime` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`recid`),
KEY `data1` (`data1`),
KEY `data2` (`data2`),
KEY `data3` (`data3`),
KEY `event` (`event`),
KEY `queuename` (`queuename`),
KEY `callid` (`callid`),
KEY `datetime` (`datetime`),
KEY `agentdev` (`agentdev`),
KEY `origid` (`origid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

And then we run the following SQL:

INSERT INTO queue_log_processed (origid,callid,queuename,agentdev,event,data1,data2,data3,datetime)
SELECT id,callid,queue_log.queuename,agent,event,
replace(substring(substring_index(`data`, '|', 1), length(substring_index(`data`, '|', 1 - 1)) + 1), '|', ''),
replace(substring(substring_index(`data`, '|', 2), length(substring_index(`data`, '|', 2 - 1)) + 1), '|', ''),
replace(substring(substring_index(`data`, '|', 3), length(substring_index(`data`, '|', 3 - 1)) + 1), '|', ''),
FROM_UNIXTIME(time) FROM queue_log;
DELETE FROM queue_log;

Utilizing MYSQL triggers to update realtime tables

I put the above example into a MYSQL trigger to allow an 2 realtime stats tables to be generated. If you are utilizing a version of MYSQL that supports triggers, I would suggest utilizing this for creating realtime tables. I then created web pages to monitor the queues.

CREATE TABLE IF NOT EXISTS `queue_log` (
`id` int(10) unsigned NOT NULL auto_increment,
`time` varchar(40) 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 '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Triggers `queue_log`
--
DROP TRIGGER IF EXISTS `bit_aasterisk`.`bi_queueEvents`;
DELIMITER //
CREATE TRIGGER `aasterisk`.`bi_queueEvents` BEFORE INSERT ON `aasterisk`.`queue_log`
FOR EACH ROW BEGIN
IF NEW.event = 'ADDMEMBER' THEN
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'READY',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
ELSEIF NEW.event = 'REMOVEMEMBER' THEN
INSERT INTO `bit_agent_status` (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'LOGGEDOUT',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "LOGGEDOUT", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
ELSEIF NEW.event = 'PAUSE' THEN
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'PAUSE',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "PAUSE", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
ELSEIF NEW.event = 'UNPAUSE' THEN
INSERT INTO `bit_agent_status` (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'READY',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = FROM_UNIXTIME(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',
FROM_UNIXTIME(NEW.time),
NEW.queuename,
'',
'',
'',
'',
0);
ELSEIF NEW.event = 'CONNECT' THEN
UPDATE `call_status` SET
callid = NEW.callid,
status = NEW.event,
timestamp = FROM_UNIXTIME(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,
FROM_UNIXTIME(NEW.time),
NEW.callid)
ON DUPLICATE KEY UPDATE
agentStatus = NEW.event,
timestamp = FROM_UNIXTIME(NEW.time),
callid = NEW.callid;
ELSEIF NEW.event in ('COMPLETECALLER','COMPLETEAGENT') THEN
UPDATE `call_status` SET
callid = NEW.callid,
status = NEW.event,
timestamp = FROM_UNIXTIME(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,FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
ELSEIF NEW.event in ('TRANSFER') THEN
UPDATE `call_status` SET
callid = NEW.callid,
status = NEW.event,
timestamp = FROM_UNIXTIME(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',FROM_UNIXTIME(NEW.time),NULL)
ON DUPLICATE KEY UPDATE
agentStatus = "READY",
timestamp = FROM_UNIXTIME(NEW.time),
callid = NULL;
ELSEIF NEW.event in ('ABANDON','EXITEMPTY') THEN
UPDATE `call_status` SET
callid = NEW.callid,
status = NEW.event,
timestamp = FROM_UNIXTIME(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 = FROM_UNIXTIME(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 = FROM_UNIXTIME(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 ;

And adding the 2 realtime tables ‘call_status’ and ‘agent_status’

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=InnoDB 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=InnoDB DEFAULT CHARSET=latin1;

Asterisk 1.4.x and lower

Asterisk does not currently support dumping queue_log data straight to a MySQL table.

There is the alternative of changing ast_queue_log function in logger.c to log via mysql. The following link describes a patch that does exactly this: http://forums.digium.com/viewtopic.php?t=4073

An alternative is to use QueueInsight which uploads seamlessly, or the commercial QueueMetrics version:
Within the package QueueMetrics there is a script called queueLoader.pl that can upload queueu_log data to MySQL.

There are a number of ways for data to be uploaded into MySQL. If we plan to use the real-time monitoring features, we must upload data to MySQL as events happen; if we don’t, uploading data in batches will suffice.

The script can be found under WEB-INF/mysql-utils and is called queueLoader.pl (a working installation of Perl5 with MySQL DBI is required to run this script).

To use the script, edit it with a text editor in order to set the MySQL server, database, user and password, like in the following example:

my $mysql_host = "10.10.3.5";
my $mysql_db = "log_code";
my $mysql_user = "queuemetrics";
my $mysql_pass = "javadude";

After the database is set, you can upload a whole file (in this case, /my/queue_log) to a partition named “P01à¢€? by running: perl queueLoader.pl /my/queue_log P01

You can also upload your queue_log file to partition “P02à¢€? as it is being written by issuing: tail -n 0 -f /var/log/asterisk/queue_log | queueLoader.pl – P02 (do not forget the minus sign, separated by spaces, before the partition name!).

A number of other techniques are available to upload a queue_log file as it is being written to a MySQL table, you may also want to consider the Unix named pipe method reported here: http://lists.digium.com/pipermail/asterisk-users/2005-July/109892.html

In the future, we expect Asterisk to be able to write queue_log data straight to a database via ODBC, so these tricks will not be necessary anymore.

Asterisk queue_log on MySQL

Converting to a more readable format for Asterisk 13

Asterisk 1.8

SVN Trunk and Asterisk 1.6.x

Converting to a more readable format

Utilizing MYSQL triggers to update realtime tables

Asterisk 1.4.x and lower

See also:

Article Reviews

Write a Review Cancel reply

Converting to a more readable format for Asterisk 13

Asterisk 1.8

SVN Trunk and Asterisk 1.6.x

Converting to a more readable format

Utilizing MYSQL triggers to update realtime tables

Asterisk 1.4.x and lower

See also:

Article Reviews

Write a Review Cancel reply

Related Posts: