【FreeRADIUS講座 第7回】「誰がいつ繋いだ？」ログ管理の極意！アカウンティング機能とSQL分析完全ガイド

「昨日の夜、誰がサーバーにアクセスした？」

こんにちは！「LINUX工房」管理人の「リナックス先生」です。
前回は、Google Authenticatorを使った多要素認証（MFA）で、入り口のセキュリティを鉄壁にしました。

しかし、セキュリティ対策は「入り口を守って終わり」ではありません。
万が一何かあった時、あるいは日々の監査において、「誰が、いつからいつまで、どのくらいのデータをやり取りしたのか」という証跡（ログ）がなければ、管理責任を果たしたことにはなりません。

本記事では、FreeRADIUSのアカウンティング機能を有効化し、MariaDB（MySQL）のテーブルに詳細なログを蓄積・分析する手法を解説します。
さらに、ルーター実機がなくてもログ送信をテストできるツール radclient の使い方も伝授します。

---

第1章：アカウンティング（Accounting）の仕組み

認証（Authentication）とアカウンティング（Accounting）は、全く別のプロセスで動いています。
アカウンティングでは、ルーター（NAS）からRADIUSサーバーに対して、以下の3種類のパケットが送られます。

パケット種類	タイミング	役割
Start	接続開始時	「今から接続します」という合図。セッションIDの発行。
Interim-Update （Alive）	接続中（定期的）	「まだ繋がってますよ」という生存報告。現在の通信量を通知。
Stop	切断時	「切れました」という報告。最終的な接続時間と通信量が確定する。

これらのパケットには、ユーザー名だけでなく、IPアドレス、MACアドレス、送受信バイト数などが含まれています。
これらをデータベースに保存することで、詳細な分析が可能になります。

---

第2章：【復習】MariaDB連携設定の確認

第3回でSQLモジュールを導入した際、アカウンティング用のテーブル radacct も作成されています。
念のため、設定が有効になっているか確認しましょう。

1. 設定ファイルの確認

/etc/raddb/sites-enabled/default を開きます。

sudo nano /etc/raddb/sites-enabled/default

accounting { ... } セクションの中に、sql の記述があり、コメントアウトされていないことを確認します。

accounting {
    detail
    unix
    
    # ↓ここが有効（コメントアウトされていない）であること！
    sql

    # ...
}

もしコメントアウトされていたら # を外して保存し、FreeRADIUSを再起動してください。

2. テーブル構造の確認

MariaDBに入り、radacct テーブルのカラムを見てみましょう。

mysql -u radius -p radius -e "DESCRIBE radacct;"

主なカラムの意味：

• radacctid : レコード固有のID。
• acctsessionid : 通信セッションごとのユニークID。StartとStopを紐付ける重要な鍵。
• username : ユーザー名。
• acctstarttime : 接続開始時刻。
• acctstoptime : 切断時刻（接続中はNULL）。
• acctsessiontime : 接続時間（秒）。
• acctinputoctets : アップロード量（バイト）。
• acctoutputoctets : ダウンロード量（バイト）。

---

第3章：ファイアウォールの穴あけ（UDP 1813）

認証（1812）は通るのにログが残らない…というトラブルの9割は、ファイアウォール設定漏れです。
アカウンティングは別のポート UDP 1813 を使用します。

# 設定確認
sudo firewall-cmd --list-all

services: に radius が含まれていれば、通常は 1812 と 1813 の両方が開放されています。
もし手動でポート指定している場合は、1813/udp も追加してください。

---

第4章：【実践】radclientによるアカウンティングテスト

ここが今回のハイライトです。
Wi-Fiルーターの実機が手元になくても、サーバー自身から擬似的なログデータを送りつけて、正しくDBに保存されるかテストすることができます。

使うツールは radtest ではなく、より強力な radclient です。

ステップ1：Startパケット（接続開始）を送る

「testuserが接続を開始した」という情報を送ります。

echo "User-Name=testuser, Acct-Status-Type=Start, Acct-Session-Id=test-session-001" | radclient -x localhost acct testing123

解説：

• User-Name : ユーザー名。
• Acct-Status-Type=Start : 開始合図。
• Acct-Session-Id : セッションID（任意）。後でStopを送る時に同じIDを使います。
• radclient -x [サーバー] acct [シークレット] : アカウンティング(acct)ポートに送るコマンド。

成功すると Accounting-Response が返ってきます。
この時点でDBを確認してみましょう。

mysql -u radius -p radius -e "SELECT username, acctstarttime, acctstoptime FROM radacct WHERE acctsessionid='test-session-001';"

acctstarttime に日時が入り、acctstoptime が NULL になっていれば、「現在接続中」として正しく記録されています。

ステップ2：Stopパケット（切断）を送る

少し時間をおいてから、「切断した」という情報を送ります。
ついでに「10MB通信した」というダミーデータも付けます。

echo "User-Name=testuser, Acct-Status-Type=Stop, Acct-Session-Id=test-session-001, Acct-Input-Octets=10000000, Acct-Output-Octets=5000000" | radclient -x localhost acct testing123

• Acct-Status-Type=Stop : 終了合図。
• Acct-Input/Output-Octets : 通信量（バイト）。

再度DBを確認します。

mysql -u radius -p radius -e "SELECT username, acctstarttime, acctstoptime, acctsessiontime FROM radacct WHERE acctsessionid='test-session-001';"

acctstoptime に時刻が入り、acctsessiontime に接続時間（秒）が計算されて入っているはずです。
これで、アカウンティング機能が正常動作していることが証明されました！

---

第5章：SQLによるログ分析テクニック

データが溜まってきたら、SQLを使って自由に分析できます。
実務でよく使うクエリ（SQL文）を紹介します。

1. 現在接続中のユーザー一覧（オンラインユーザー）

acctstoptime が NULL のレコードを抽出します。

SELECT username, acctstarttime, callingstationid 
FROM radacct 
WHERE acctstoptime IS NULL;

※ callingstationid には通常、端末のMACアドレスが入ります。

2. ユーザーごとの月間接続時間ランキング

「今月、誰が一番長く繋いでいたか？」を集計します。

SELECT username, 
       SUM(acctsessiontime) / 3600 AS total_hours, 
       COUNT(*) as login_count 
FROM radacct 
WHERE acctstarttime >= '2026-01-01 00:00:00' 
GROUP BY username 
ORDER BY total_hours DESC 
LIMIT 10;

3. 通信量（ギガ）の多いユーザー特定

アップロードとダウンロードを合計して、GB単位で表示します。

SELECT username, 
       ROUND(SUM(acctinputoctets + acctoutputoctets) / 1024 / 1024 / 1024, 2) AS total_gb 
FROM radacct 
GROUP BY username 
ORDER BY total_gb DESC;

---

第6章：運用トラブル「ゴーストセッション」問題

RADIUS運用で最も頭を悩ませるのが「ゴーストセッション（Stale Session）」です。

現象：
ユーザーはもう帰宅して切断しているはずなのに、DB上では acctstoptime が NULL のまま（接続中扱い）になっている。

原因：
Wi-Fiルーターが突然の停電や再起動でダウンした際、「Stopパケット」を送信できずに死んでしまうことで発生します。
RADIUSサーバーはずっと「まだ繋がっている」と思い込みます。

対策：Stale Sessionのクリーニング

FreeRADIUSには、このゾンビレコードを掃除するクエリが用意されています。
「NAS（ルーター）からの最終更新（Interim-Update）が一定時間ないものは、切断されたとみなす」という処理です。

/etc/raddb/mods-config/sql/main/mysql/queries.conf を見てみましょう。
post-auth セクションなどにクエリが定義されていますが、手動またはcronで以下のSQLを定期実行するのが確実です。

-- 最終更新から1時間以上経過している接続中レコードを強制クローズする
UPDATE radacct 
SET acctstoptime = acctstarttime + acctsessiontime, 
    acctterminatecause = 'Admin-Reset' 
WHERE acctstoptime IS NULL 
  AND (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(acctstarttime)) > 3600;

---

第7章：ログローテーションとアーカイブ

radacct テーブルは、ユーザー数が多いと爆発的な勢いでレコードが増えます。
1年後には数百万行になり、DBの動作が重くなります。

運用戦略

1. アーカイブテーブルの作成:
   radacct_2025 のような別テーブルを作成し、昨年のデータを移動（INSERT SELECT & DELETE）する。
2. CSVへの書き出し:
   mysqldump でCSVに書き出してS3などに保存し、テーブルからは削除する。
3. 自動削除:
   「ログは3ヶ月分でいい」というポリシーなら、cronで定期的に DELETE する。

-- 90日以上前のログを削除する
DELETE FROM radacct WHERE acctstarttime < DATE_SUB(NOW(), INTERVAL 90 DAY);

---

第8章：NAS（Wi-Fi AP）側の設定注意点

最後に、サーバー側の設定が完璧でも、ルーター（NAS）側が対応していないと意味がない点に触れておきます。

必須設定：

• アカウンティングサーバー設定:
  認証サーバー（1812）だけでなく、アカウンティングサーバー（1813）のIPとポートも必ず設定してください。
• インターバル（Interim Interval）:
  「何分おきに通信量を報告するか」の設定です。短すぎるとサーバー負荷になり、長すぎるとゴーストセッションが増えます。
  推奨は 10分〜30分（600〜1800秒） です。

---

まとめ：ログはシステムの「通信簿」

お疲れ様でした！
これで、あなたのFreeRADIUSサーバーは、単なる「鍵」から、利用状況を詳細に記録・分析できる「管理システム」へと進化しました。

今回の重要ポイント：

• アカウンティングは UDP 1813 ポートを使う。
• ログは radacct テーブルに保存される。
• radclient を使えば、ルーターなしでもStart/Stopのテストが可能。
• ゴーストセッション対策と、定期的なデータ削除（ローテーション）が運用の鍵。

さて、ここまででFreeRADIUSの機能面はほぼマスターしました。
しかし、もしこのサーバーが故障して止まったら？ 社内の全Wi-Fiが止まり、大惨事になります。

いよいよ次回は最終回。
「トラブルシューティングと冗長化（Failover）構成」です。
サーバーを2台用意して、片方が死んでも認証を止めない「最強の可用性」を手に入れる構成ガイドです。
最後まで走り抜けましょう！