Zenn
📈

トラフィック計測ツール "pmacct" の結果をSQLiteに保存する

2021/05/09に公開
Linux
pmacct
tech

3分後には計測を開始できます。

環境: Raspberry Pi OS (March 4th 2021 / kernel 5.10)

準備

sudo apt install -y pmacct sqlite3
cat /usr/share/pmacct/sql/pmacct-create-table_v1.sqlite3 | sed "s/'0000-00-00 00:00:00'/(DATETIME('now', 'localtime'))/" | sudo sqlite3 /tmp/pmacct.db

計測

例: wlan0 インターフェイスのトラフィックを計測

sudo pmacctd -P sqlite3 -i wlan0 -c src_mac,dst_mac,src_host,src_port,dst_host,dst_port,proto

結果を見る

$ sudo sqlite3 /tmp/pmacct.db "SELECT * FROM ACCT"
AA:AA:AA:BB:BB:BB|YY:YY:YY:XX:XX:XX|192.168.255.103|192.168.255.126|22|65230|tcp|1|136|2021-05-09 21:20:05|
YY:YY:YY:XX:XX:XX|AA:AA:AA:BB:BB:BB|192.168.255.126|192.168.255.103|65230|22|tcp|1|52|2021-05-09 21:20:05|

pmacctd を動かしっぱなしでも開くことができます。

おまけ: CSVに出力する

$ sudo sqlite3 /tmp/pmacct.db -header -csv "SELECT * FROM ACCT"
mac_src,mac_dst,ip_src,ip_dst,src_port,dst_port,ip_proto,packets,bytes,stamp_inserted,stamp_updated
AA:AA:AA:BB:BB:BB,YY:YY:YY:XX:XX:XX,192.168.255.103,192.168.255.126,22,65230,tcp,1,136,2021-05-09 21:20:05,
YY:YY:YY:XX:XX:XX,AA:AA:AA:BB:BB:BB,192.168.255.126,192.168.255.103,65230,22,tcp,1,52,2021-05-09 21:20:05,

ref: Export SQLite Database To a CSV File

解説

トラフィック計測ツール pmacct の結果は、標準では標準出力にテキストで表示・保存できますが、プラグインによってDB等に保存したり、AMQPやKafkaを通じて他のシステム連携ができるようになっています。

後々の集計を考えるとDBは便利ですが準備が大変です。
そこでSQLiteです。SQLiteはファイルベースのDBで、準備の手間が無いのですぐに始めることができます。

pmacctからSQLiteを使う

本来の設定の仕方は /etc/pmacct/pmacctd.conf をSQLite用に調整して pmacctd の実行時に指定しますが、正直面倒です。 (conf例はこちら)

Raspberry Pi OSのリポジトリで配布されている pmacctd は -P sqlite3 を指定されたうえで、conf内でのSQLite DBファイルの指定がなかった場合、デフォルトが /tmp/pmacct.db を参照します。よって、ここにファイルを作ればconfの指定は不要です。
※ただし、あらゆる情報を取ることができる "v9" で動作させるためには conf での指定が必須となります。ここで紹介している方法では、基本的 "v1" となります。

pmacctd の実行

pmacctd の実行時に -c src_mac,dst_mac,src_host,src_port,dst_host,dst_port,proto とすれば、日時以外のすべての列を埋めることができます。

日付については、この後のCREATE TABLE文の調整をご覧ください。

CREATE TABLE文の調整

pmacctについてくる pmacct-create-table_v1.sqlite3 では日付は stamp_inserted 列となりますが、標準では 0000-00-00 00:00:00 となります。これでは役に立たないのですが、pmacctd の -c には stamp_inserted 列に対応する情報がありません。

そのため、INSERT時にlocaltimeが入るようにしたのが、準備で通している sed です。
調整後のスキーマです。

$ sudo sqlite3 /tmp/pmacct.db ".schema acct"
CREATE TABLE acct (
        mac_src CHAR(17) NOT NULL DEFAULT '0:0:0:0:0:0',
        mac_dst CHAR(17) NOT NULL DEFAULT '0:0:0:0:0:0',
        ip_src CHAR(45) NOT NULL DEFAULT '0.0.0.0',
        ip_dst CHAR(45) NOT NULL DEFAULT '0.0.0.0',
        src_port INT(4) NOT NULL DEFAULT 0,
        dst_port INT(4) NOT NULL DEFAULT 0,
        ip_proto CHAR(6) NOT NULL DEFAULT 0,
        packets INT NOT NULL,
        bytes BIGINT NOT NULL,
        stamp_inserted DATETIME NOT NULL DEFAULT (DATETIME('now', 'localtime')),
        stamp_updated DATETIME,
        PRIMARY KEY (mac_src, mac_dst, ip_src, ip_dst, src_port, dst_port, ip_proto, stamp_inserted)
);

ちゃんと使うなら、confを作りましょう

データベース名、テーブル名、取りたい情報などを指定したい場合は、confを作りましょう。
例は https://github.com/pmacct/pmacct/blob/master/examples/pmacctd-sqlite3.conf.example にあります。

Discussion