Backlinks...Asterisk cdr mysqlMysqlQueueMetricsThe 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
Current SVN trunk (Revision 94782 from 12-26-07 09:54), supports storage of queue_log in your RT engine.
More details: http://bugs.digium.com/view.php?id=11625
PLEASE 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 `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
to your extconfig.conf
PLEASE 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.
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=MyISAM 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;
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=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Triggers `queue_log`
--
DROP TRIGGER IF EXISTS `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 `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 `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=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;
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 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.
