What I Do At Work

This is an archived post
This is an archived post

Asterisk 1.8 queue_log to MySQL

March 27 2012, 2:07 PM by Mike Boylan

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:

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.

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.

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?

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?