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.

Sample queue_log table for MySQL:

CREATE TABLE `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 '',
  PRIMARY KEY (`id`)
);

queue_log message consists of 2 parts: constant and variable. Constant part is splited 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

More details: http://bugs.digium.com/view.php?id=11625

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.


And then we run the following SQL:

---

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


You can also upload your queue_log file to partition “P02� as it is being written by issuing:

(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.

See also:

• Asterisk log queue_log
• QueueMetrics, a call center monitor that can read data from MySQL