Asterisk 1.8 queue_log to MySQL

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.

5123 views and 14 responses

  • Jul 21 2012, 5:08 AM
    ast newbie responded:
    Great post! Was difficult going through the undocumented approach without also reading your article. Thanks.
  • Jul 21 2012, 10:44 AM
    Mike Boylan responded:
    No problem! Glad it was helpful!

    -Mike

  • Sep 1 2012, 3:09 PM
    Emilian responded:
    Cool article! Is it possible to store or save the Asterisk calls into a Mysql database and have the possibility to playback it in a later moment?
    Thanks!
  • Sep 1 2012, 3:25 PM
    Mike Boylan responded:
    Record the calls themselves into a database? You can record calls using the dialplan application MixMonitor but note that it's against the law to record calls without willingly informing the other party they are being recorded. The call data can also be stored in MySQL similar to the queue data.
  • Sep 2 2012, 2:48 PM
    Emilian responded:
    yes, record the call themselves into a Mysql database for example. Can you reference me any document or link to solve this problem please?
  • Sep 2 2012, 3:10 PM
    Mike Boylan responded:
    Google "store files in mysql" or "store files in sql" for how to store data files directly into a SQL database. In terms of the recording, see https://wiki.asterisk.org/wiki/display/AST/Application_MixMonitor

    You'd have to write some sort of transport to get them from the filesystem to the database. Storing files directly into a database, as far as I know, isn't something Asterisk natively supports.

  • Feb 4 2013, 5:39 AM
    Sean Funtanares responded:
    can i fetch or get data from the msql database of asterisks and use it in PHP?
  • Feb 4 2013, 1:15 PM
    Mike Boylan responded:
    Yes but that would not happen from Asterisk. You would simply add a new user with select privileges to the Asterisk DB and use that user in your connection string in the PHP page. Hope that helps.
  • Feb 18 2013, 7:01 AM
    Newman responded:
    Great post! I was wondering how to put queue logs to PostreSQL dbase, but thanks to you I got it working in 3 minutes. Thank you!!!
  • Feb 18 2013, 8:24 AM
    Mike Boylan responded:
    Glad it was helpful! 

    -Mike

  • Feb 19 2013, 6:14 AM
    Junz responded:
    Thanx Mike!! a real helpfull post. I just have one problem though. If i have an influx of calls. Mysql seems to hold the calls in waiting table and count down the waiting seconds. and after a few days it just returns to normal. Not sure why it does that. Would you be abel to assist.
  • Feb 19 2013, 9:42 PM
    Mike Boylan responded:
    I've never heard of this. Googling didn't turn up much, either. When we have campus alerts sent out, we get over 1600 calls almost at one time and it doesn't even hiccup. Are you running the latest version of MySQL and the asterisk add-ons?

  • Mar 22 2013, 1:54 AM
    Arcdevil responded:
    Thank U!
  • Apr 16 2013, 12:56 AM
    trung.hac responded:
    it is helpful for me. thanks you very much!