Information about how to do this has been posted elsewhere on the web, but most of it is significantly outdated. Moving forward with our Asterisk upgrade project we decided that we'd like to see not only CDR data written to MySQL but also the Asterisk queue_log. For those of you unfamiliar with the queue_log (this post is probably meaningless if that's you), it is the logfile in which Asterisk stores call data about calls that enter queues. It's located at /var/log/asterisk/queue_log
First things first: You need a MySQL database to use, and you need the asterisk-addons and asterisk-addons-mysql packages installed. If you use CentOS or RHEL you can use "yum install asterisk-addons asterisk-addons-mysql" to install the packages, assuming you've configured the Digium yum repos.
Once you've got the addons installed, the next thing you'll want to do is create the database table to store the asterisk queue_log data. There are two separate table structures you can choose from. The first defaults to writing the extra data associated with a queue call to one field in the database. Personally, I thought doing it that way was stupid. If you have up to five fields of data, why store it in one field per database entry? Split it up. Thankfully, someone else thought of that too and there's an undocumented option you can use to do it. More on that later. Assuming you want to use the cleaner, separated data form, this was my table structure:
USE asterisk;
CREATE TABLE queue_log (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
time char(26) 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 '',
data1 varchar(100) NOT NULL default '',
data2 varchar(100) NOT NULL default '',
data3 varchar(100) NOT NULL default '',
data4 varchar(100) NOT NULL default '',
data5 varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
);
Once you have the table created, the next steps involve changes to the Asterisk server.
In /etc/asterisk/res_config_mysql.conf, change the [general] context to point to the database. Mine looks like this:
[general]
dbhost = 10.10.10.10
dbname = asterisk
dbuser = asteriskserver
dbpass = supersecretpwd
dbport = 3306
requirements=warn
In /etc/asterisk/logger.conf, change queue_log_to_file = no to yes. This will write the queue_log to the file in /var/log/asterisk regardless of any other destinations. Directly below that line, add queue_adaptive_realtime = yes. The queue_adaptive_realtime option is undocumented in the file but it is what splits the data into multiple fields before writing to the database.
Finally, in /etc/asterisk/extconfig.conf uncomment out queue_log => mysql,general.
After all those changes are made, restart asterisk with "core restart now". Once restarted, if you type "reload", you should see the following on the console:
asterisk*CLI> reload
== Parsing '/etc/asterisk/extconfig.conf': == Found
== Binding queue_log to mysql/general/queue_log
== Parsing '/etc/asterisk/logger.conf': == Found
> Found column 'id' of type 'int(10) unsigned'
> Found column 'time' of type 'char(26)'
> Found column 'callid' of type 'varchar(32)'
> Found column 'queuename' of type 'varchar(32)'
> Found column 'agent' of type 'varchar(32)'
> Found column 'event' of type 'varchar(32)'
> Found column 'data1' of type 'varchar(100)'
> Found column 'data2' of type 'varchar(100)'
> Found column 'data3' of type 'varchar(100)'
> Found column 'data4' of type 'varchar(100)'
> Found column 'data5' of type 'varchar(100)'
Asterisk Queue Logger restarted
If things seem to go awry, check the /var/log/asterisk/messages file for clues. Typically you will see the errors below unless you're also using the res_config_mysql.conf file to define parameters for the Asterisk RealTime Architecture.
[Mar 27 16:45:26] WARNING[3575] res_config_mysql.c: MySQL RealTime: No database user found, using 'asterisk' as default.
[Mar 27 16:45:26] WARNING[3575] res_config_mysql.c: MySQL RealTime: No database password found, using 'asterisk' as default.
[Mar 27 16:45:26] WARNING[3575] res_config_mysql.c: MySQL RealTime: No database host found, using localhost via socket.
[Mar 27 16:45:26] WARNING[3575] res_config_mysql.c: MySQL RealTime: No database name found, using 'asterisk' as default.
[Mar 27 16:45:26] WARNING[3575] res_config_mysql.c: MySQL RealTime: No database port found, using 3306 as default.
[Mar 27 16:45:26] WARNING[3575] res_config_mysql.c: MySQL RealTime: No database socket found (and unable to detect a suitable path).
Selecting from the database should show output similar to this:
That's it! Hopefully this was useful. Feel free to post any questions or comments.