выполнить по ssh(скопировать и вставить в консоль/putty)
. /etc/amportal.conf
mysql asterisk -u $AMPDBUSER -p$AMPDBPASS
дальше на sql пишите запрос вот такого вида
select ids.id,
(select data from sip where keyword='callerid' and id=ids.id) as callerid,
(select data from sip where keyword='secret' and id=ids.id) as secret,
(select data from sip where keyword='callgroup' and id=ids.id) as callgroup
from (select distinct id from sip) ids;
ну добавляете колонки чего вам надо.
получаете вот такое
mysql> select ids.id,(select data from sip where keyword='callerid' and id=ids.id) as callerid,(select data from sip where keyword='secret' and id=ids.id) as secret,(select data from sip where keyword='callgroup' and id=ids.id) as callgroup from (select distinct id from sip) ids;
+-----+--------------+-------------+-----------+
| id | callerid | secret | callgroup |
+-----+--------------+-------------+-----------+
| 199 | device <199> | mtest23test | |
+-----+--------------+-------------+-----------+
1 row in set (0.00 sec)
можно сразу експортнуть в csv. смотрите
select ids.id,
(select data from sip where keyword='callerid' and id=ids.id) as callerid,
(select data from sip where keyword='secret' and id=ids.id) as secret,
(select data from sip where keyword='callgroup' and id=ids.id) as callgroup
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
from (select distinct id from sip) ids;
