Asterisk: SQL запросы, время ожидания в очереди, переменные в dialplan из файла

К моим клиентам в call центр иногда поступает большой поток звонков. Одни их них придумали свой нестандартный способ переключения режима фоновой музыки (автоинформатора) в очереди астериска в зависимости от нагрузки на операторов, который не реализуется штатными средствами.  Мы будем анализировать логи очередей и по заданным условиям менять настройки диалплана. Условия были озвучены следующие:
1. Если за последний час в очереди было не менее 4х клиентов ожидающих ответа более 4 минут, то менять режим сообщений информатора очереди на усиленный (3852-hard).
2. Если за последний час в очереди было менее 4х клиентов ожидающих ответа более 6 минут, то возвращаем режим сообщений информатора очереди на обычный (3852-soft).
В общем, разобьем работу на 5 частей и начнем колхозить:

1. Научим asterisk писать логи очередей в базу mysql.
2. Напишем SQL запросы для извлечения нужной статистики.
3. Напишем скрипт, который по результатам запросов будет выбирать режим работы фоновой музыки в очереди. Запускать скрипт будет раз в 1 час из крона.
4. Зададим новый класс фоновой музыки и научим asterisk брать имя класса из файла.

В нашем случае вся разница между режимами состоит в различных сообщениях голосового информатора, которые проигрываются клиенту в очереди. Поэтому, очередь у нас остается одна, а вот для голосовых сообщений мы зададим дополнительный класс. Далее в диалплане астериска будем использовать переменную, которая будет считывать имя нужного класса из текстового файла. Скрипт будет запускаться 1 раз в час и в случае совпадения заданных условий менять значение этого текстового файла.

Научим asterisk писать логи очередей в базу mysql

Сначала научим писать логи очередей в MySql (самостоятельно установить mysql-server трудностей не вызовет). Нам нужно будет создать базу asterisk и таблицу queue_log с таким содержанием:

CREATE TABLE IF NOT EXISTS `queue_log` (
`time` datetime DEFAULT NULL,
`callid` char(64) DEFAULT NULL,
`queuename` char(64) DEFAULT NULL,
`agent` char(64) DEFAULT NULL,
`event` char(32) DEFAULT NULL,
`data` char(64) DEFAULT NULL,
`data1` char(64) DEFAULT NULL,
`data2` char(64) DEFAULT NULL,
`data3` char(64) DEFAULT NULL,
`data4` char(64) DEFAULT NULL,
`data5` char(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Сразу оговорюсь про поле time. Мануалы в сети рекомендуют использовать типа varchar, но в этом случае сложно будет задавать условия по этому полю относительно времени, например: в течение последнего часа. Поэтому мы будем использовать тип datetime.

Далее отключим запись логов очереди в файл, добавив в секции [general] файла logger.conf строку:

nano /etc/asterisk/logger.conf
queue_log_to_file = no

Вторым шагом зададим запись аргументов событий не в строчку c разделителем |, а отдельными полями data1-5

nano /etc/asterisk/asterisk.conf
queue_adaptive_realtime = no

Далее для сохранения логов в базу данных по ODBC создаем (если нет) или открываем файл extconfig.conf и пишем в секцию [settings]:

nano /etc/asterisk/extconfig.conf
queue_log => odbc,asterisk,queue_log

Теперь нам нужно настроить это самое odbc подключение:

nano /etc/asterisk/res_odbc.conf
[asterisk]
enabled => yes
dsn => qs-asterisk
username => db-user
password => db-password
pre-connect => yes

Где dsn, настройки из файла /etc/odbc.ini

nano /etc/odbc.ini
[qs-asterisk]
Description=MySQL connection to 'asterisk' database
driver=MySQL
server=localhost
database=asterisk
Port=3306
Socket=/var/lib/mysql/mysql.sock

Напишем SQL запросы для извлечения нужной статистики

Теперь перейдем к SQL запросу. Вот кусок данных из базы, чтобы понимать какие события происходят при поступлении звонка в очередь:

Нас интересуют 2 события: когда клиенту ответил оператор и когда клиент не дождавшись ответа положил трубку. События называются CONNECT и ABANDON соответственно. В поле data1 события CONNECT и в поле data3 события ABANDON как раз указывается время ожидания клиента в секундах до этого события. В итоге мы получим время нахождения клиента в очереди вне зависимости от того, ответили ему или он положил трубку не дождавшись ответа. Более подробно про события в очереди можно прочитать здесь.

Нам нужно выбрать все значения ожидания в данных событиях, отсортировать их по убыванию и ограничить вывод 4 записями. Там мы получим 4 наибольших значения. Далее мы зададим условия для отображения записей длиннее чем 240 (360 во втором запросе) секунд и подсчитаем количество этих записей. Если записей получится меньше чем 4, значит условие не выполнено.

nano /etc/asterisk/scripts/query1.sql
SELECT COUNT(T.waitdata) FROM
(SELECT (CASE WHEN event = 'CONNECT' THEN data1 ELSE data3 END) AS waitdata FROM queue_log WHERE ( (event = 'CONNECT') OR (event = 'ABANDON'))
AND `time` >= date_sub(now(), INTERVAL 1 HOUR)
AND (CASE WHEN event = 'CONNECT' THEN data1 ELSE data3 END) >= 240
ORDER BY waitdata+0 DESC LIMIT 4
) AS T

nano /etc/asterisk/scripts/query2.sql
SELECT COUNT(T.waitdata) FROM
(SELECT (CASE WHEN event = 'CONNECT' THEN data1 ELSE data3 END) AS waitdata FROM queue_log WHERE ( (event = 'CONNECT') OR (event = 'ABANDON'))
AND `time` >= date_sub(now(), INTERVAL 1 HOUR)
AND (CASE WHEN event = 'CONNECT' THEN data1 ELSE data3 END) >= 360
ORDER BY waitdata+0 DESC LIMIT 4
) AS T

Напишем скрипт

Написать скрипт можно было на php/perl/python, что удобнее. Либо вообще не использовать внешний скрипт: mysql запросы и условия прописать в диалплане напрямую. Но я решил использовать bash, что более "лампово" и универсально:

touch /etc/asterisk/scripts/queue.sh
chmod +x /etc/asterisk/scripts/queue.sh
nano /etc/asterisk/scripts/queue.sh

#!/bin/bash
user=root
password=pass
status="$(cat /etc/asterisk/scripts/musichold.txt)"

# Делаем запрос времени ожидания клиента в очереди за последний час. Выводим только 4 наибольших значения, при условии, что время ожидания составило более 4 минут (240 с), и считаем количество полученных строк.
query1="/etc/asterisk/scripts/query1.sql"
# То же самое, но время ожидания более 6 минут (360 с)
query2="/etc/asterisk/scripts/query2.sql"

# Пишем в переменные результаты запроса, ключ -N для того, чтобы не отображать название столбца
waittime1=$(mysql -u$user -p$password asterisk -N < $query1)
waittime2=$(mysql -u$user -p$password asterisk -N < $query2)

# Включаем второй набор музыки если был включен первый набор и 4 клиента ожидало более 4 минут
if [[ "$status" = 3852-soft && "$waittime1" = 4 ]]
  then
# Пишем в файл имя секции Musiconhold удаляя символ переноса строки в конце (иначе астериск при чтении переведет строку)
    echo -n "3852-hard" > /etc/asterisk/scripts/musichold.txt

# Включаем первый набор музыки, если менее 4 клиентов ожидало более 6 минут и включен второй набор
elif [[ "$status" = 3852-hard && "$waittime2" < 4 ]]
  then
    echo -n "3852-soft" > /etc/asterisk/scripts/musichold.txt
# В остальных случаях ничего не делаем
else
  echo Очередь не изменилась: $status
  exit 0
fi

exit

Создадим файл для сохранения имения класса музыки:

touch /etc/asterisk/scripts/musichold.txt

Добавим скрипт в крон, чтобы запускался каждый час:

nano /etc/crontab
# запускаем скрипт проверки времени ожидания клиентов и выбора режима очереди раз в час
0 *     * * *   root    /etc/asterisk/scripts/queue.sh

Зададим класс фоновой музыки

Теперь добавим новый класс для фоновой музыки. В итоге у нас их будет два: 3852-soft и 3852-hard. Как-то так:

nano /etc/asterisk/musiconhold.conf
[3852-soft]
mode=files
directory=/usr/share/asterisk/sounds/ru/3852-soft
sort=alpha ; Sort the files in alphabetical order

[3852-hard]
mode=files
directory=/usr/share/asterisk/sounds/ru/3852-hard
sort=alpha ; Sort the files in alphabetical order

В папки /usr/share/asterisk/sounds/ru/3852-soft и /usr/share/asterisk/sounds/ru/3852-hard мы положим подготовленные файлы. Проигрываться они будут в алфавитном порядке.

Далее в настройках астериска находим контекст из которого клиент попадает в очередь. Добавляем у него экстеншн задающий класс фоновой музыки, имя которого мы возьмем из файла /etc/asterisk/scripts/musichold.txt.

nano /etc/asterisk/extensions.conf
...
; задаем класс для музыки в очереди переменной из файла
;это работает только в старых версиях, например 1.8. В 13 версии нет readfile, вместо него мы будем использоваться FILE
;same => n,ReadFile(QUEUENAME=/etc/asterisk/scripts/queue.txt,9);
same => n,Set(QUEUENAME=${FILE(/etc/asterisk/scripts/musichold.txt)})
same => n,Set(CHANNEL(musicclass)=${QUEUENAME})
same => n,Queue(3852,TtxX,,,1800,,logagentnumber); тут ваша очередь (у меня это 3852, на 30 минут с запуском макроса logagentnumber)
...