Asterisk キューのログを MySql へ格納

Asterisk 呼情報をODBCでMySqlへ格納する方法を以前やりました。

Asterisk CDR(呼情報)をODBCでMySqlへ格納
Asterisk CDR(呼情報)をODBCでMySqlへ格納(後記)

今回はAsterisk のキュー(待ち呼)のログをODBCでMySqlへ格納する方法を紹介します。

1. キューログ用のテーブルを作成

まずは、キューログ用に queue_log テーブルを作成します。

CREATE TABLE `queue_log` (
    `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 AUTO_INCREMENT=1;

2. extconfig.conf

/etc/asterisk/extconfig.conf にキューのログを格納する情報を記述します。

queue_log => odbc,asterisk,queue_log

第1パラメータ: ドライバ
第2パラメータ: res_odbc.conf のデータベースの設定情報 (DSN)
第3パラメータ: テーブル名

3. AsteriskとODBC接続の構成

AsteriskとODBC接続の構成は /etc/asterisk/res_odbc.conf で行います。

[asterisk]
enabled => yes
dsn => asterisk-connector
username => asterisk
password => asterpass
pre-connect => yes

4. ODBC設定の追加

Asteriskデータベースへの接続情報を /etc/odbc.ini へ追加します。

[asterisk-connector]
Description           = MySQL connection to 'asterisk' database
Driver                = MySQL
Database              = asterisk
Server                = localhost
UserName              = asterisk
Password              = asterpass
Port                  = 3306
Socket                = /var/lib/mysql/mysql.sock

5. 動作確認

Asterisk 再起動して動作確認します。

# service asterisk restart

キュー情報がMySqlへ格納されるか動作確認してみます。
ここでは、SIP #2501 がキューに入って #2502 が受けた結果を表示しています。

# mysql -u asterisk -p
Enter password: 

mysql> use asterisk;
mysql> select * from queue_log;

+----+----------------------------+--------------+------------+----------+----------------+------+-------+--------------+-------+-------+-------+---------------------+
| id | time                       | callid       | queuename  | agent    | event          | data | data1 | data2        | data3 | data4 | data5 | created             |
+----+----------------------------+--------------+------------+----------+----------------+------+-------+--------------+-------+-------+-------+---------------------+
| 18 | 2016-09-19 12:33:53.249160 | 1474256033.2 | queue_test | NONE     | ENTERQUEUE     |      |       | 2501         | 1     |       |       | 2016-09-19 12:33:53 |
| 19 | 2016-09-19 12:33:56.479286 | 1474256033.2 | queue_test | SIP/2502 | CONNECT        |      | 3     | 1474256033.3 | 3     |       |       | 2016-09-19 12:33:56 |
| 20 | 2016-09-19 12:34:06.721414 | 1474256033.2 | queue_test | SIP/2502 | COMPLETECALLER |      | 3     | 10           | 1     |       |       | 2016-09-19 12:34:06 |
+----+----------------------------+--------------+------------+----------+----------------+------+-------+--------------+-------+-------+-------+---------------------+
3 rows in set (0.01 sec)

参考:
http://stackoverflow.com/questions/30161384/asterisk-11-queue-log-to-mysql
http://work.mikeboylan.com/posts/2012/03/asterisk-queuelog-to-mysql.html
http://lists.digium.com/pipermail/asterisk-users/2009-September/237448.html

以上です。

Asterisk TLSとSRTPでより安全なコール (パート2: SRTP編)

前回はTLS設定で呼制御の暗号化を試みました。今回は通話の暗号化を試みます。

1. sip.conf の変更
/etc/asterisk/sip.conf のSIPクライアント(例 2001 )の定義に “encryption=yes” を追加します。

[2001]
type=peer
secret=password    ; this is NOT a secure password
host=dynamic
...(省略)...
transport=tls
encryption=yes

2. クライアントの設定
Zoiper で 暗号化 を有効にして確認します。

Preferences -> Accounts -> Advanced -> Use rport media をチェック
Preferences -> Accounts -> Advanced -> Use TLS with SDES SRTP を選択

以上です。

通話を開始すると Zoiper 上にカギのアイコンが表示されます。

また、今回の設定は Zoiper 以外にも AGEphone Business の評価版での試用をしました。
サポートの方に教えて頂きました AGEphone Business の設定方法は下記のとおりです。

Windows - AGEphone Biz3
TLS
- アカウント設定 > 接続種別 = TLS
- アカウント設定 > 全般の設定 > トランスポートにTLSを使う = ON
SRTP
- アカウント設定 > 全般の設定 > メディア暗号化(SRTP) = ON

Asterisk TLSとSRTPでより安全なコール (パート1: TLS編)

Asterisk には TLS(Transport Layer Security)SRTP(Secure Real-time Transport Protocol) により通信を安全に行う方法があります。

TLS は Asterisk と SIPクライアントの SIP によるやりとりを暗号化し、SRTP は電話の音声ストリームを暗号化します。
ここでは CentOS6.x 上で Asterisk 13 をソースをビルドしてインストール方法は省略します。ただし、Asterisk 13 のビルドは下記の必要なパッケージをインストールしてから行ってください。

1. 必要なパッケージのインストール
まず、暗号化にに必要な OpenSSL と LibSRTP をインストールします。

# yum install openssl openssl-devel
# yum install libsrtp libsrtp-devel

2. 自己署名証明書の作成
Asterisk で利用する自己署名証明書を生成するために Asterisk のソースディレクトリ下の contrib/scripts/に ast_tls_cert という便利なツールが付属しています。これで サーバー証明書とクライアント証明書を生成します。

# mkdir /etc/asterisk/keys
# ./ast_tls_cert -C foo.com -O "Foo Company" -d /etc/asterisk/keys
# ./ast_tls_cert -m client -c /etc/asterisk/keys/ca.crt -k /etc/asterisk/keys/ca.key -C foo.com -O "Foo Company" -d /etc/asterisk/keys -o sipclient

※ foo.com や "Foo Company" は実際のサーバーのFQDN またはIPアドレス、社名に置き換えてください。また、コマンドの途中でパスフレーズを数回入力するように促されますので適切なものを入力します。

コマンドを実行すると以下のファイルが /etc/asterisk/keys ディレクトリに生成されます。

# ll /etc/asterisk/keys 

asterisk.crt
asterisk.csr
asterisk.key
asterisk.pem
sipclient.crt
sipclient.csr
sipclient.key
sipclient.pem
ca.cfg
ca.crt
ca.key
tmp.cfg

3. sip.conf の変更
/etc/asterisk/sip.conf に以下を追加します。

tlsenable=yes
tlsbindaddr=0.0.0.0:5061
tlscertfile=/etc/asterisk/keys/asterisk.pem
tlscafile=/etc/asterisk/keys/ca.crt
tlscipher=ALL
tlsclientmethod=tlsv1

続けて、SIPクライアント(例 2001 )の定義に “transport=tls” を追加します。

[2001]
type=peer
secret=password    ; this is NOT a secure password
host=dynamic
...(省略)...
transport=tls

4. クライアントの設定
Zoiper で TLS を有効にして確認します。

Preferences -> Accounts -> Advanced -> Use rport をチェック
Preferences -> Accounts -> Advanced -> Use TLS transport を選択
Preferences -> Advanced -> Security -> Extra CA certificate に sipclient.pem ファイルを選択
Preferences -> Advanced -> Security -> Protocol suite: から TLS v1 を選択
Preferences -> Advanced -> Security -> Disable certificate verification をチェック ※1

※1 このチェックボックスは DANGEROUS DO NOT USE! と表記されていますが、これをチェックしないと
Certificatge: Issuer untrusted/not found/not valid/wrong purpose というエラーになります。
自己署名の証明書のためと思われます。また、再起動するとチェックが解除されていますので、起動のたびにチェックをする必要があります。
これが、フリー版の制限なのか、自己署名証明書のせいなのかはわかりません。;)

Asterisk CDR(呼情報)をODBCでMySqlへ格納(後記)

Asterisk CDR(呼情報)をODBCでMySqlへ格納で紹介したCDR格納用のテーブルは cdr_mysql や cdr_odbc アドオンと互換性を維持した構成となっています。

Adaptive CDR ODBCでは start, answer, end というフィールドに通話開始、応答、終了日時を格納します。 calldate は使用しません。

そのため、Asterisk CDR(呼情報)をODBCでMySqlへ格納では、/etc/asterisk/cdr_adaptive_odbc.conf に start フィールドを calldate へ別名として格納するように設定をしています。(以下参照)

[adaptive_connection]
connection=asterisk
table=cdr
alias start => calldate

そこで、今後推奨とされている cdr_adaptive_odbc 用のCDRテーブル定義と、それに対応するように cdr_adaptive_odbc.conf を変更します。

Adaptive CDR ODBC用のCDRテーブル定義

新たに cdr_odbc テーブルを用意します。

CREATE TABLE cdr_odbc (
        `accountcode` VARCHAR(20) NULL , 
        `src`         VARCHAR(80) NULL , 
        `dst`         VARCHAR(80) NULL , 
        `dcontext`    VARCHAR(80) NULL , 
        `clid`        VARCHAR(80) NULL , 
        `channel`     VARCHAR(80) NULL , 
        `dstchannel`  VARCHAR(80) NULL , 
        `lastapp`     VARCHAR(80) NULL , 
        `lastdata`    VARCHAR(80) NULL , 
        `start`       datetime NULL , 
        `answer`      datetime NULL , 
        `end`         datetime NULL , 
        `duration`    int NULL , 
        `billsec`     int NULL , 
        `disposition` VARCHAR(20) NULL , 
        `amaflags`    VARCHAR(16) NULL , 
        `uniqueid`    VARCHAR(150) NULL , 
        `userfield`   VARCHAR(256) NULL ,
        `linkedid`    VARCHAR(32) NOT NULL default '' ,
        `sequence`    VARCHAR(32) NOT NULL default '' ,
        `peeraccount` VARCHAR(32) NOT NULL default ''
    );

/etc/asterisk/cdr_adaptive_odbc.conf の変更

テーブルを cdr_odbc とし alias をやめます。

[adaptive_connection]
connection=asterisk
table=cdr_odbc
;以下不要
;table=cdr
;alias start => calldate

動作確認

mysql> select * from cdr_odbc;                                                                                    
+-------------+------+------+----------+---------------+-------------------+-------------------+---------+---------------+---------------------+---------------------+---------------------+----------+---------+-------------+---------------+--------------+-----------+--------------+----------+-------------+                                    
| accountcode | src  | dst  | dcontext | clid          | channel           | dstchannel        | lastapp | lastdata      | start               | answer              | end                 | duration | billsec | disposition | amaflags      | uniqueid     | userfield | linkedid     | sequence | peeraccount |                                    
+-------------+------+------+----------+---------------+-------------------+-------------------+---------+---------------+---------------------+---------------------+---------------------+----------+---------+-------------+---------------+--------------+-----------+--------------+----------+-------------+
| NULL        | 3001 | 2001 | default  | "3001" <3001> | SIP/3001-00000000 | SIP/2001-00000001 | Dial    | SIP/2001,,rtT | 2015-02-05 18:18:34 | 2015-02-05 18:18:39 | 2015-02-05 18:18:44 |       10 |       5 | ANSWERED    | DOCUMENTATION | 1423127914.0 | NULL      | 1423127914.0 | 0        |             |
| NULL        | 2001 | 3001 | default  | "2001" <2001> | SIP/2001-00000002 | SIP/3001-00000003 | Dial    | SIP/3001,,rtT | 2015-02-05 18:19:11 | 2015-02-05 18:19:13 | 2015-02-05 18:19:19 |        8 |       6 | ANSWERED    | DOCUMENTATION | 1423127951.2 | NULL      | 1423127951.2 | 3        |             |
+-------------+------+------+----------+---------------+-------------------+-------------------+---------+---------------+---------------------+---------------------+---------------------+----------+---------+-------------+---------------+--------------+-----------+--------------+----------+-------------+

start, answer, end に日時が格納され、amaflags の値は以前とは異なり文字列となっています。

参考:
http://www.voip-info.org/wiki/view/Asterisk+cdr+odbc
http://www.voip-info.org/wiki/view/Asterisk+billing

以上です。

Asterisk CDR(呼情報)をODBCでMySqlへ格納

Asterisk 設定を MySQL に格納してリアルタイム変更では、asterisk-addons モジュールで呼情報をMySqlへ格納する方法を紹介しましたが、Asterisk 1.8 以降ではODBCを使用する方法が推奨となったのでその方法を紹介します。

CentOS 6.6 でMySql 5.1.73 および Asterisk 1.8.25 がインストールされている前提です。

Asterisk CDR(呼情報)をODBCでMySqlへ格納するための関連するAsterisk の構成ファイルとODBC/Database の関係です。

Asterisk 側                                   
cdr_adaptive_odbc.conf -> res_odbc.conf -> /etc/odbc.ini

Linux 側
/etc/odbc.ini -> MySql database

1. Asterisk 用のデータベースを作成

mysql コマンドで root でログインします。

# mysql -u root -p
Enter password:

次に、ユーザーを作成します。ここではユーザーを asterisk 、パスワードを asterpassとします。

mysql> CREATE USER 'asterisk'@'localhost' IDENTIFIED BY 'asterpass';

続いて、データベースを作成します。ここではデータベース名を asterisk とします。

mysql> CREATE DATABASE asterisk;

さらに、作成したユーザーにデータベースへの権限を付与します。

mysql> GRANT ALL PRIVILEGES ON asterisk.* TO 'asterisk'@'localhost';

以上でデータベース作成は終了。一旦、MySql コンソールから抜けます。

mysql> exit

次に、呼情報を格納するテーブルを作成します。先ほど作成したユーザーとパスワードでmysqlコンソールへログインします。

# mysql -u asterisk -p
Enter password:

データベース一覧には asterisk が表示されます。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| asterisk           |
+--------------------+

asterisk データベースを指定して cdr テーブルを作成します。

mysql> use asterisk;
Database changed

続けて、以下の CREATE TABLE 文を実行

CREATE TABLE `cdr` (
	 `calldate` datetime NOT NULL default '0000-00-00 00:00:00',
	 `clid` varchar(80) NOT NULL default '',
	 `src` varchar(80) NOT NULL default '',
	 `dst` varchar(80) NOT NULL default '',
	 `dcontext` varchar(80) NOT NULL default '', 
	 `channel` varchar(80) NOT NULL default '',
	 `dstchannel` varchar(80) NOT NULL default '',
	 `lastapp` varchar(80) NOT NULL default '',
	 `lastdata` varchar(80) NOT NULL default '',
	 `duration` int(11) NOT NULL default '0',
	 `billsec` int(11) NOT NULL default '0',
	 `disposition` varchar(45) NOT NULL default '', 
	 `amaflags` int(11) NOT NULL default '0',
	 `accountcode` varchar(20) NOT NULL default '',
	 `userfield` varchar(255) NOT NULL default '',
	 `uniqueid` VARCHAR(32) NOT NULL default '',
	 `linkedid` VARCHAR(32) NOT NULL default '',
	 `sequence` VARCHAR(32) NOT NULL default '',
	 `peeraccount` VARCHAR(32) NOT NULL default ''
	 );

ALTER TABLE `cdr` ADD INDEX ( `calldate` );
ALTER TABLE `cdr` ADD INDEX ( `dst` );
ALTER TABLE `cdr` ADD INDEX ( `accountcode` );

参考:
https://wiki.asterisk.org/wiki/display/AST/CDR+Fields
https://wiki.asterisk.org/wiki/display/AST/MySQL+CDR+Backend

2. ODBC ドライバーのインストール

# yum install unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel

3. MySql 接続用のODBCドライバーのインストール

# yum install mysql-connector-odbc

上記コマンドが完了したら、下記コマンドでドライバーの確認。[MySQL] が表示されればOKです。

# odbcinst -q -d
[PostgreSQL]
[MySQL]

4. ODBC設定の追加

/etc/odbc.ini へAsteriskデータベースへの接続情報を記述追加します。

[asterisk-connector]
Description           = MySQL connection to 'asterisk' database
Driver                = MySQL
Database              = asterisk
Server                = localhost
UserName              = asterisk
Password              = asterpass
Port                  = 3306
Socket                = /var/lib/mysql/mysql.sock

5. AsteriskのODBCモジュールのインストール

Asterisk のビルド時にODBCドライバーがシステムにインストールされていない場合、AsteriskのODBCモジュールはデフォルトで入っていないはずなので追加し、再ビルドします。

Asterisk のソースツリー(例: /usr/local/src/asterisk-1.8.25/) へ移動して下記コマンドを実行します。

# cd /usr/local/src/asterisk-1.8.25/
# ./configure
# make menuselect
# make install

6. AsteriskとODBC接続の構成

最後に、Asterisk からODBCでMySqlデータベースへ接続するために設定を /etc/asterisk/res_odbc.conf へ追加します。

[asterisk]
enabled => yes
dsn => asterisk-connector
username => asterisk
password => asterpass
pooling => no
limit => 1
pre-connect => yes

7. 構成の確認

Asterisk を再スタートし、Asterisk コンソールからODBCの構成を確認

localhost*CLI> odbc show

ODBC DSN Settings
-----------------
  Name:   asterisk
  DSN:    asterisk-connector
    Last connection attempt: 1970-01-01 09:00:00
  Pooled: No
  Connected: Yes

8. 呼情報テーブル (cdr) の設定

Asterisk 呼情報を cdr テーブルへ格納するように設定するために /etc/asterisk/cdr_adaptive_odbc.conf を変更します。

[adaptive_connection]
connection=asterisk
table=cdr
alias start => calldate

参考:
http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/database_storing-cdr.html

Asteriskを再起動します。

# /sbin/service asterisk restart

Asterisk コンソールから cdr 設定の確認をします。Registered Backends に cdr-custom, Adaptive ODBC と csv が確認できます。

# /usr/sbin/asterisk -rvvvvvvvvvvvvvvvvvv

localhost*CLI> cdr show status

Call Detail Record (CDR) settings
----------------------------------
  Logging:                    Enabled
  Mode:                       Simple
  Log unanswered calls:       No

* Registered Backends
  -------------------
    cdr-custom
    Adaptive ODBC
    csv

8. 動作確認

呼情報がMySqlへ格納されるか動作確認してみます。
ここでは、SIP #3001 と #2001 へ双方へ電話した後の cdr テーブルの情報を見ます。

# mysql -u asterisk -p
Enter password: 

mysql> use asterisk;
mysql> select * from cdr;
+---------------------+---------------+------+------+----------+-------------------+-------------------+---------+---------------+----------+---------+-------------+----------+-------------+-----------+--------------+--------------+----------+-------------+
| calldate            | clid          | src  | dst  | dcontext | channel           | dstchannel        | lastapp | lastdata      | duration | billsec | disposition | amaflags | accountcode | userfield | uniqueid     | linkedid     | sequence | peeraccount |
+---------------------+---------------+------+------+----------+-------------------+-------------------+---------+---------------+----------+---------+-------------+----------+-------------+-----------+--------------+--------------+----------+-------------+
| 2015-02-05 17:40:31 | "3001" <3001> | 3001 | 2001 | default  | SIP/3001-00000000 | SIP/2001-00000001 | Dial    | SIP/2001,,rtT |        7 |       2 | ANSWERED    |        3 |             |           | 1423125631.0 | 1423125631.0 | 0        |             |
| 2015-02-05 17:40:50 | "2001" <2001> | 2001 | 3001 | default  | SIP/2001-00000002 | SIP/3001-00000003 | Dial    | SIP/3001,,rtT |        5 |       4 | ANSWERED    |        3 |             |           | 1423125650.2 | 1423125650.2 | 3        |             |
+---------------------+---------------+------+------+----------+-------------------+-------------------+---------+---------------+----------+---------+-------------+----------+-------------+-----------+--------------+--------------+----------+-------------+
2 rows in set (0.00 sec)

以上です。