![[Note]](../images/icons/note.png){kind=icon} | Note |
|---|---|
this is not a tutorial on how to use MySQL storage in QueueMetrics. Such a topic is covered in detail in QueueMetrics’ User Manual |
Qloaderd is a small scripts that uploads queue_log data into a MySQL database for further analysis by http://queuemetrics.com . It is designed to be used instead of the older queueLoader.pl script, and offers a number of advantages over the older version:
- qloaderd is designed to be a one-stop solution to upload data. No need for tail or custom startup scripts.
- qloaderd is able to upload only data that is missing from the given partition, by checking the timestamp and the row’s contents in order to prevent duplicate rows. This way you every time it is started, it will update the MySQL table with all data that is missing from it and then will start tailing the queue_log file for variations.
- In the case of a database problem or a disconnection, qloaderd will keep retrying to connect to the database until it succeeds. No data is lost if the database goes offline for a while.
- qloaderd will automatically detect invalid queue_log lines and will skip them immediately
- qloaderd keeps a detailed log of all its activity
- qloaderd can be installed as a service using a simple startup script
- In order to avoid service disruptions due to a lost database connection and to confirm QueueMetrics the connection is alive, qloader will periodically insert heartbeat information in the queue_log database.
To install qloaderd, copy the file qloader.pl to a location of your choice (we suggest /usr/local/qloader). Run the following commands to make sure the file is executable:
dos2unix qloader.pl chmod a+x qloader.pl
If you run a yum-based Linux distro, you can also have qloaderd installed automatically by issuing:
wget -P /etc/yum.repos.d http://yum.loway.ch/loway.repo yum install qloaderd
You will still have to update it manually in order to configure it correctly.
You now have to edit the qloader.pl file in order to set up your database connection, by changing the following parameters:
my $mysql_host = "myserver.mylan"; my $mysql_db = "queuemetrics"; my $mysql_user = "username"; my $mysql_pass = "password";
The qloader script expects three parameters to run:
- The first parameter is the full path to the queue_log file it has to upload
- The second parameter is the partition into which it will upload data
- The third parameter is the log file it will create when running
To test that the qloader script is working, try the following command:
./qloader.pl /var/log/asterisk/queue_log P02 /var/log/asterisk/qloader.log
This command will try uploading the contents of the file /var/log/asterisk/queue_log to partition P02 of the chosen database, writing its own log on /var/log/asterisk/qloader.log. When the program is running no output is written on stdout; if you want to know how the loading is going you have to consult its own log file.
Optionally, you can pass along one or more of the following command line parameters before the mandatory parameters:
- -h hostname: the MySQL hostname to connect to
- -d database: the MySQL database to connect to
- -u user: the MySQL username to use
- -p pass: the MySQL password to use
The parameters above override the settings edited in qloader.pl. Please note that it’s not advisable to pass the MySQL password on the command line.
We provide a script (qloaderd) that can be put into /etc/init.d in order to start and stop qloader as a service and have it start automatically when the machine boots. You will find it in the Redhat-style-initscripts directory. The file is called qloaderd and must be put into /etc/init.d on the Asterisk server. It was made for RHEL/CentOS/AAH/Trixbox, but it will likely work on most similar versions of Linux.
Run the following commands to make sure the file is executable:
dos2unix /etc/init.d/qloaderd chmod +x /etc/init.d/qloaderd
You then need to create a configuration file under /etc/sysconfig/qloaderd that will lok lik ethe following one:
PARTITION=P001 QUEUELOG=/var/log/asterisk/queue_log LOGFILE=/var/log/asterisk/qloaderd.log LOCKFILE=/var/lock/subsys/qloaderd PIDFILE=/var/run/qloaderd.pid MYSQLHOST=localhost MYSQLDB=queuemetrics MYSQLUSER=queuemetrics MYSQLPASS=javadude
You usually only need to modify the PARTITION and maybe QUEUELOG entries.
At this point, you can start the qloader simply by typing /etc/init.d/qloaderd start and stop it by typing /etc/init.d/qloaderd stop. As qloader is able to upload only missing data, no data is lost if you stop it.
In order to have the service started on boot, use the command:
chkconfig --add qloaderd
As a default, it starts automatically in runlevels 2,3,4 and 5. You can easily change that with chkconfig.
We also provide a plain-vanilla startup script that can be installed in your local startup directory in order to start Qloader automatically. It may require a bit of tweaking, but it will run on all Linux distributions. You will find it in the Other-initscripts directory. Follow the same instructions as for a RedHat based system to make the script executable and configure it.
The following fetaures of Qloaderd can be enabled or disabled as needed. Most people will not need them.
Qloaderd can insert a heartbeat log entry in order to make sure that:
- The MySQL channel stays open all of the time, even when there is no activity on the Asterisk side. This is useful because MySQL disconnects the DB connection after a given channel inactivity (usually 8 hours) and sometimes reconnecting a dead channel will not work.
- On the QueueMetrics side, it is possible to know immediately if one or more members of a cluster have been disconnected, as the heartbeat information will be present even if Asterisk is idle
This option is turned on by default, and is controlled by the configuration option:
my $heartbeat_delay = 15 * 60;
This means the heartbeat is sent every 15 minutes after the queue_log is idle. If you would like to turn this off, set it to 0.
If you work in an environment where a single queue is used to service a number of different agents, it might be of interest to you to enable subqueues; i.e. to have the queue name rewritten appending a client code at the end, so that you can query them from QueueMetrics as if each client was serviced by a dedicated queue.
In odrer to activate this, you must:
Set the line:
my $use_subqueue = 1;
If you optionally add a comment to the client code after the "/" symbol, you should set
my $split_subq_name = 0;
to have the comment clipped off. This is useful if for debug reasons you would like the client code to be "1234/Red", where "1234" is the client code and "Red" is a human-readable comment.
Create a table called qlog_opencalls in the QueueMetrics database, using the following schema:
CREATE TABLE ‘qlog_opencalls‘ ( ‘rowId‘ int(10) unsigned NOT NULL auto_increment, ‘partition‘ varchar(20) NOT NULL default ’’, ‘callId‘ varchar(45) NOT NULL default ’’, ‘queue‘ varchar(45) NOT NULL default ’’, ‘subqueue‘ varchar(45) NOT NULL default ’’, ‘lastVerb‘ varchar(45) NOT NULL default ’’, ‘lastTst‘ int(10) unsigned NOT NULL default ’0’, ‘lastUpd‘ datetime NOT NULL default ’0000-00-00 00:00:00’, PRIMARY KEY (‘rowId‘), KEY ‘accIdx1‘ (‘callId‘,‘partition‘) ) TYPE=MyISAM;
Whenever you pipe a call to the Queue() command in Asterisk, set the client code in the URL parameter of the command, like for example in:
exten => s,8,Queue(queue-service|t|1234||180)
Here the queue queue-service is called with a client code of 1234; the resulting queue activity will be seen in QueueMetrics as if it was done on queue queue-service.1234.
Please note that you should mantain the qlog_opencalls table yourselves, deleting periodically rows with a ’lastUpd’ timestamp over one day old and optimizing it as needed (this will be done automatically in future revisions of Qloaderd).
To achieve this result, just run the following queries through a cron job in a moment when the system is idle or is experiencing a low load:
DELETE FROM qlog_opencalls WHERE lastUpd < DATE_SUB( NOW(), INTERVAL 48 HOUR); OPTIMIZE TABLE qlog_opencalls;
As we delete a potentially large number of rows at once, running the OPTIMIZE saves space and improves index performance as well. The downside is that it could lock the table for a few seconds.
It is possible to perform advanced channel rewriting to the Agent/XXX format. Thi is done on data loading and does not require QueueMetrics to do it again and again every time it loads data.
To enable this feature, set:
my $rewriteToAgent = 1; # 0 no; 1 yes my @channelsToAgent = ( ’Local’, ’SIP’ );
The configuration above will mean that channels like SIP/1234 will be rewritten to Agent/1234, while Local/2345@agents will be Agent/2345.
It is possible to use a database table to store channel rewriting rules for maximum flexibility. This is often used e.g. when using Asterisk Realtime and still wanting Agent/xxx and not SIP/yyy, where you keep track of agent xxx being dynalmically logged on to SIP/yyy.
To enable this feature, set:
my $dbAgentRewrite = 1;
You should also create a table in the QueueMetrics database with the following definition:
CREATE TABLE ‘qlog_rewrite‘ ( ‘ag_from‘ varchar(50) NOT NULL, ‘ag_rewritten‘ varchar(50) NOT NULL, ‘last_upd‘ datetime NOT NULL, PRIMARY KEY (‘ag_from‘) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
An enter the rewrite rules as tuples ( ag_from, ag_rewritten ). The last_upd key is not used and only given as an hint of very old entries that might need deletion. Before enabling this mode, please consider that a query will be performed for nearly every line of the queue_log that is being processed. Results are not cached, as the ability to change the underlying table while qloaderd is running is considered a feature.
If you suspect problems with qloaderd:
- First of all, have a look at its own log file.
Try running a queue and check that lines get loaded into MySQL in real-time. The following query will often be helpful to count all elements in your queue_log table:
select partition, queue, count(*) as n_records from queue_log group by partition, queue order by partition, queue
| Note |
|---|---|
this query runs a full table scan, so it may take a long time to complete if you have million of lines loaded. |
- Run the real-time page in QueueMetrics
As qloader keeps reading the queue_log file, it must be notified when the queue_log file is being rotated. Sending it a /etc/init.d/qloaderd restart after the rotation has taken place will make sure that qloader starts loading data from the new queue_log file. It would be a nice idea to rotate its own log file too.
You could modify the /etc/logrotate.d/asterisk file as follows:
/var/log/asterisk/*log {
missingok
rotate 5
weekly
create 0640 asterisk asterisk
postrotate
/usr/sbin/asterisk -rx ’logger reload’ > /dev/null 2> /dev/null
/etc/init.d/qloaderd restart > /dev/null 2> /dev/null
endscript
}If you need further help in setting up qloaderd….
- First of all, have a look at the QueueMetrics FAQ
- Then try the QueueMetrics forum
- Feel free to contact us.