Queue_log to MySQL in 10.13.66 distro?

[noleo]

Continuing the discussion from Queue log into MySQL database:

@jimmyuk

Hi,

I am trying to develop a wallboard system and I’m struggling to work out how to get detailed Queue data,

What I need is a way to get details on abandoned calls, and then cross linking it with the uniqueid with the CDR database to extract how long the caller was waiting before hanging up and also who it was. Alot of the queue_log guides out there seem to refer to files that I cant find on my distro installed FreePBX.

My question is; is there a robust way of getting the queue log data into a MySQL database in real time?

thanks

Jimmy

Continuing the discussion from Registering queue log in database:

@Bass850

Does anybody have an answer to this yet, I really need this for integration to call center system (real time queue data in mysql). We are on latest FreePBX with Asterisk 11.2)

Any help would be much appreciated

Continuing the discussion from Registering queue log in database:

@Aragorn

Bumping an old thread here, but i have the same issue…

I’ve looked at this guide:

http://work.mikeboylan.com/asterisk-queuelog-to-mysql

But it doesnt seem to work on FreePBX. Any ideas how i can configure queue_log to dump into a database?

Cheers
Kevin

I am trying to get this working with the latest distro 10.13.66 and hopefully someone can steer me in the right direction. I have tried the above mentioned guide and scoured the internet.

In logger_general_custom.conf I have:
queue_adaptive_realtime = yes
queue_log_to_file = yes

I did not have the extconfig.conf file or the res_config_mysql.conf files, so I created them:

In extconfig.conf I have:

[settings]
queue_log => mysql,general

In res_config_mysql.conf I have:

[general]
dbhost = 127.0.0.1
dbname = asteriskcdrdb
dbuser = mydbusername
dbpass = mydbuserpassword
dbport = 3306
requirements=warn

Here is the structure of my queue_log table which I got from this link: Asterisk 11 queue log to mysql - Stack Overflow

CREATE TABLE tablename (
id bigint(255) unsigned NOT NULL AUTO_INCREMENT,
time varchar(26) NOT NULL DEFAULT ‘’,
callid varchar(40) NOT NULL DEFAULT ‘’,
queuename varchar(20) NOT NULL DEFAULT ‘’,
agent varchar(20) NOT NULL DEFAULT ‘’,
event varchar(20) NOT NULL DEFAULT ‘’,
data varchar(100) NOT NULL DEFAULT ‘’,
data1 varchar(40) NOT NULL DEFAULT ‘’,
data2 varchar(40) NOT NULL DEFAULT ‘’,
data3 varchar(40) NOT NULL DEFAULT ‘’,
data4 varchar(40) NOT NULL DEFAULT ‘’,
data5 varchar(40) NOT NULL DEFAULT ‘’,
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY queue (queuename),
KEY event (event)
) DEFAULT CHARSET=utf8;

Nothing seems to work. I also tried using the dsn,tablename instead of the [general] context for the extconfig and res_config_mysql files. I restarted MySQL and reloaded Asterisk afterward.
Does anyone know the trick to getting the queue_log into MySQL with the FreePBX distro? It would be really useful for queue status boards and reports.

Thanks,
Nowell

[stuartalt (Rodrigo Ramos)]

Can you make updates on mysql when logged in with the asterisk user?

The only thing I didn’t see in your post was the permissions.

GRANT INSERT,SELECT,UPDATE
ON asteriskcdrdb.*
TO asterisk@localhost
IDENTIFIED BY ‘PASSWORD’; <------= Change the password.

Then:
flush privileges;

Also, in the FreePBX GUI, goes to: “Setting” > “Asterisk Logfile Settings”, search for “Queue Log” and make sure it is set to “YES”.

[noleo]

Thank you stuartalt.
When I checked my MySQL users, I didn’t see an “asterisk” user, but I did see a “freepbxuser” user, so that is the user account that is writing asterisk data to the DB on my system.

You got me on the right track, because I was using the wrong account in my res_config_mysql.conf file. Once I put the freepbxuser account in there, the queuelog started writing into the MySQL db. Thank you so much!

For others using the official distro and are struggling with this, use the freepbxuser account instead of asterisk in the res_config_mysql.conf file. The password for the freepbxuser account can be found in the etc/amportal.conf file under AMPDBPASS.

[madman91 (Mister K)]

Worked for me on Asterisk 13 and FPBX 10.13.66-14
Thank you @noleo for this guide.

Needed to track ABANDONED calls to count them as unanswered, because unanswered=yes in cdr.conf didn’t gave me the result that i searched.

[jeetzz (Jeet)]

Hi…

Is it still working? I did exact things mentioned here, no luck.

First created table…

USE asteriskcdrdb;

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`)
 );

In /etc/asterisk/res_config_mysql.conf, change the [general] context to point to the database.
[general]
dbhost = 127.0.0.1
dbname = asteriskcdrdb
dbuser = freepbxuser
dbpass = xxxxxxxxxx
dbport = 3306
requirements=warn

In /etc/asterisk/logger.conf and logger_logfiles_custom, add at bottom
queue_log_to_file = yes
queue_adaptive_realtime = yes

Finally, in /etc/asterisk/extconfig.conf I added this section
queue_log => mysql,general.

No luck? Can someone tell me what step am i missing? I added freepbxuser and also root, no luck…

 MariaDB [asteriskcdrdb]> SELECT * FROM queue_log;
 Empty set (0.01 sec)

[jeetzz (Jeet)]

i get this, its not connecting to mysql… any ideas?

**No category context for line 1 of /etc/asterisk/extconfig_custom.conf **
– Reloading module ‘res_odbc.so’ (ODBC resource)

 [2019-08-13 06:50:36] ERROR[6244]: config_options.c:708 aco_process_config: Unable to load 
 config file 'acl.conf'
[2019-08-13 06:50:36] WARNING[6244]: named_acl.c:413 ast_named_acl_reload: Could not reload 
ACL config
[2019-08-13 06:50:36] NOTICE[6244]: cdr.c:4459 cdr_toggle_runtime_options: CDR simple logging 
enabled.
-- CEL logging enabled.
 [2019-08-13 06:50:36] WARNING[6244]: config.c:1950 process_text_line: parse error: No category 
context for line 1 of /etc/asterisk/extconfig_custom.conf
Bound HTTP server to address [::]:0
== TLS/SSL ECDH initialized (automatic), faster PFS ciphers enabled
== TLS/SSL certificate ok
-- Setting default indication country to 'uk'
Asterisk Queue Logger restarted
-- Reloading module 'res_odbc.so' (ODBC resource)
[2019-08-13 06:50:36] NOTICE[6244]: res_odbc.c:616 load_odbc_config: Registered ODBC class 'asteriskcdrdb' dsn->[MySQL-asteriskcdrdb]
-- Reloading module 'res_config_odbc.so' (Realtime ODBC configuration)
-- Reloading module 'cdr_adaptive_odbc.so' (Adaptive ODBC CDR backend)
-- Found adaptive CDR table cdr@asteriskcdrdb.
-- Found alias start for column calldate in cdr@asteriskcdrdb
   > Found calldate column with type 93 with len 19, octetlen 19, and numlen (0,10)
   > Found clid column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found src column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found dst column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found dcontext column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found channel column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found dstchannel column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found lastapp column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found lastdata column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found duration column with type 4 with len 10, octetlen 10, and numlen (0,10)
   > Found billsec column with type 4 with len 10, octetlen 10, and numlen (0,10)
   > Found disposition column with type -9 with len 45, octetlen 540, and numlen (0,0)
   > Found amaflags column with type 4 with len 10, octetlen 10, and numlen (0,10)
   > Found accountcode column with type -9 with len 20, octetlen 240, and numlen (0,0)
   > Found uniqueid column with type -9 with len 32, octetlen 384, and numlen (0,0)
   > Found userfield column with type -9 with len 255, octetlen 3060, and numlen (0,0)
   > Found did column with type -9 with len 50, octetlen 600, and numlen (0,0)
   > Found recordingfile column with type -9 with len 255, octetlen 3060, and numlen (0,0)
   > Found cnum column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found cnam column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found outbound_cnum column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found outbound_cnam column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found dst_cnam column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found linkedid column with type -9 with len 32, octetlen 384, and numlen (0,0)
   > Found peeraccount column with type -9 with len 80, octetlen 960, and numlen (0,0)
   > Found sequence column with type 4 with len 10, octetlen 10, and numlen (0,10)

[jeetzz (Jeet)]

i was missing this the word “[settings]”

[settings]
queue_log => mysql,general

@jeetzz

**No category context for line 1 of /etc/asterisk/extconfig_custom.conf **

[Fraser]

Have a look at: https://www.voip-info.org/asterisk-queuelog-on-mysql/ to convert the queue_log table into a more readable format. This creates some additional tables such as call_status, agent_status and queue_log_processed and the data is populated by sql triggers

I have this working with Asterisk 16 without any problems.

Give me a shout if you get stuck!

Fraser.