FreePBX 2.9 + Asterisk 1.8.8 + Hotdesking (devicesandusers) Auto Rewrite (works)

[Wessel]

Hi,

SETUP: Asterisk 1.8.8 with FreePBX 2.9
using hotdesking ( set FreePBX on devicesandusers mode )
qloader.pl pushing the data into an external database

Problem:
Asterisk 1.8.8+ & FreePBX write out the Usernames to the queue_log and uses Local channels

Solution:
The local channels can be rewritten by qloader.pl, however the full agent names needs a table that contains all teh rewrite rules ( see the qloader manual how to setup this table )

What I've created is a trigger for Queuemetrics that updates the qlog_rewrite table automatically as soon as a full Agent name is added or changed.
So as long as you use exactly the same full name in Quemetrics for your agent as you use in FreePBX you no longer need to add rewrite rules to qloader as they are generated automatically by the two triggers below.
These triggers need to be created inside the Queuemetrics database which should contain the table agenti_noti and qlog_rewrite

It's basic code that does the job. However if you change the full name of your agent 10x it will leave you with 10 rewrite rules. So advise check the rewrite table from time to time if all rewrite rules are still used. However if if time stamps are updated in the table 1000x nothing is added to the table as it checks the uniqueness of the rewrite rules. If you have any improvements, please add them 

Enjoy!
      Wessel de Roode

Code: [Select]

/* Create a trigger that updates the rewrite table every time a new user is added to Queuemetrics */
CREATE TRIGGER self_update_qlog_rewrite_insert AFTER INSERT ON agenti_noti
    FOR EACH ROW
         INSERT INTO qlog_rewrite ( ag_from, ag_rewritten )
               SELECT descr_agente, nome_agente FROM agenti_noti
                    WHERE descr_agente NOT IN (SELECT ag_from FROM qlog_rewrite);

/* Create a trigger that updates the rewrite table every time a user altered in Queuemetrics */
CREATE TRIGGER self_update_qlog_rewrite_update AFTER UPDATE ON agenti_noti
     FOR EACH ROW
         INSERT INTO qlog_rewrite (ag_from, ag_rewritten )
               SELECT descr_agente, nome_agente FROM agenti_noti
                    WHERE descr_agente NOT IN (SELECT ag_from FROM qlog_rewrite);

Qloader.pl rewrite rules switched on:

Code: [Select]

my $rewriteToAgent = 1; # 0 no; 1 yes
my @channelsToAgent = ( 'Local', 'SIP' );
my $dbAgentRewrite = 1; # 0 no; 1 yes - rewrite according to rules in table qlog_rewrite
See the manual of Qloader how to setup the table. After that, apply the triggers. It does not matter if you already had some users in your queuemetrics. But add at least 1 user to trigger the SQL so it wil update your rewrite rules.

[QueueMetrics]

Jeez! this is radical 

[SilvioSantoZ]

This is even more radical!

Code: [Select]

delimiter |
 
DROP TRIGGER `queuemetrics`.`self_update_qlog_rewrite_insert`|
DROP TRIGGER `queuemetrics`.`self_update_qlog_rewrite_update`|
DROP TRIGGER `queuemetrics`.`self_update_qlog_rewrite_delete`|
 
 
CREATE TRIGGER self_update_qlog_rewrite_insert AFTER INSERT ON agenti_noti FOR EACH ROW BEGIN
    DELETE FROM qlog_rewrite;
    INSERT INTO qlog_rewrite ( ag_from, ag_rewritten, last_upd )
    SELECT descr_agente, nome_agente, NOW() FROM agenti_noti;
END;
 
|
 
CREATE TRIGGER self_update_qlog_rewrite_update AFTER UPDATE ON agenti_noti FOR EACH ROW BEGIN
    DELETE FROM qlog_rewrite;
    INSERT INTO qlog_rewrite ( ag_from, ag_rewritten, last_upd )
    SELECT descr_agente, nome_agente, NOW() FROM agenti_noti;
END;
 
|
 
CREATE TRIGGER self_update_qlog_rewrite_delete AFTER DELETE ON agenti_noti FOR EACH ROW BEGIN
    DELETE FROM qlog_rewrite;
    INSERT INTO qlog_rewrite ( ag_from, ag_rewritten, last_upd )
    SELECT descr_agente, nome_agente, NOW() FROM agenti_noti;
END;
 
|


[QueueMetrics]