CIDRブロックとASNの関係をデータベースにしてみる

状況説明

ひょんなことから CIDRブロックとccTLDの関係をデータベースにしてみる はめになった第2弾は、CIDR ブロックと AS 番号 (ASN) の対応表である。

ccTLD の場合と同様に ccTLD と ASN の対応表を作るべくやってみたが、 毎回更新時にほぼ同数のレコードが追加されてしまう状態ではあるが、とりあ えずデータは使える状態にはなった。 [1]

データソース

ASN は CIDR ブロックと大体同じで、IANA から RIR・NIR を経て ISP や企 業・大学などに割り当てられる。

ただし、ccTLD の場合とは異なって、ある ASN がどれだけの CIDR ブロック を持っているかについては、通常は BGP の経路情報で見ることになる。

BGP の経路情報に関しては、大手 ISP ならいざ知らず、野良の一個人ではな かなかフルルートを焚べてもらうという訳にはいかないのだが、 ありがたい ことに オレゴン大学 が情報を出してくれている。( routeviews に各種リ ソースあり(あリ過ぎ?)。 RIBs から一日一回ダンプの経路情報ファイルを もらってきて bgpdump で処理すれば良い) 今だとフルルートでおよそ 60 万行くらいなので、ccTLD の時に比べれば ちょっと量がある。

とりあえずデータを眺めたい向き(俺だ)には、例えば IP2LocationLITE IP-ASN DB がお手軽である。 月に一度の更新だが、CSV にしてくれている。(他にバイナリ形式もあるよう だ)

1
2
3
4
5
(IP2LOCATION-LITE-ASN.CSV)
"16778240","16778495","1.0.4.0/24","56203","hoge hoge Group"
"16778496","16778751","1.0.5.0/24","56203","hoge hoge Group"
"16778752","16779007","1.0.6.0/24","56203","hoge hoge Group"
  <snip>

これは、第一カラムから順に次のようなデータである。

  • CIDR ブロック開始点 (32bit 整数表記)
  • CIDR ブロック終了点 (32bit 整数表記)
  • CIDR ブロック (Quad Decimal 表記)
  • ASN
  • AS オーナ名 (上の例では一部を伏せた)

初回データ投入

格納用テーブルはこれ。

CREATE TABLE ip2lcidrbyasn (
  iprange ip4r      NOT NULL,
  asn     BIGINT    NOT NULL,
  trange  TSTZRANGE NOT NULL
);
CREATE INDEX ON ip2lcidrbyasn (iprange);
CREATE INDEX ON ip2lcidrbyasn (asn);
CREATE INDEX ON ip2lcidrbyasn (trange);

COPY 時に使う一時テーブル。

CREATE TABLE ip2lcidrbyasn_tmp (
  ipstart BIGINT NOT NULL,
  ipend   BIGINT NOT NULL,
  iprange ip4r   NOT NULL,
  asn     BIGINT NOT NULL,
  asowner TEXT   NOT NULL
);

ファイルから一時テーブルへの読み込みと本テーブルへの転記。

1
2
3
COPY ip2lcidrbyasn_tmp FROM '/path/to/IP2LOCATION-LITE-ASN.CSV' with csv;

INSERT INTO ip2lcidrbyasn (iprange, asn, trange) SELECT iprange, asn, tstzrange('${DATE}'::timestamp with time zone, null) FROM ip2lcidrbyasn_tmp;"

更新データ投入

ccTLD の時と同様に、ある CIDR ブロックの origin AS が移動するケースは あり得て、かつ、それは単純な移動もあれば CIDR ブロックの分割・統合を必 要とする場合もあるものと思われる。

これに加えて、BGP 古ルートの場合には パンチングホール の問題もあって、 さらに処理が複雑になっている。 すなわち、ある一日のスナップショットを見たとしても、iprange が重なる場 合があるわけで、それを避けるとすれば CIDR ブロックの分割が必要になる。

今のところこれを実現する時間的余裕がないので、データテーブル側の制約を 外して単なるメモ帳のように使っている。 すなわち、更新データは上の初回データ投入と同じ INSERT 文を使うので、N 行のデータであれば N 行が追加される。

データ利用

  • ある時点の CIDR と ASN の関係のリスト
SELECT iprange, asn, trange FROM ip2lcidrbyasn
  WHERE trange @> '2016-06-01'::timestamp with time zone;
  • ある時点で ASN が保有(経路を広報)する IP の数のリスト
SELECT asn, sum(@@ iprange) AS ipcount
  FROM ip2lcidrbycc
  WHERE trange @> '2016-06-01'::timestamp with time zone
  GROUP BY asn;
  • ASN 毎の IP 保有数を日付別に集計したテーブル(できれば VIEW にしたい)。各日付の合計は(asn = 0 が実在してデータ内に出現するため private 領域の) asn = 65534 で表現している。
CREATE TABLE ip2lcidrbyasn_ipcount (
  date timestamp with time zone not null
  asn     bigint                not null
  ipcount bigint                not null
);
CREATE INDEX ON ip2lcidrbyasn_ipcount(date)
CREATE INDEX ON ip2lcidrbyasn_ipcount(asn)
CREATE INDEX ON ip2lcidrbyasn_ipcount(ipcount)

INSERT INTO ip2lcidrbyasn_ipcount
  SELECT '2016-06-22'::TIMESTAMP WITH TIME ZONE date, asn, sum(@@ iprange) ipcount
    FROM ip2lcidrbyasn
    WHERE trange @> '2016-06-22'::TIMESTAMP WITH TIME ZONE
    GROUP BY asn;
INSERT INTO ip2lcidrbyasn_ipcount
  SELECT '2016-06-22'::TIMESTAMP WITH TIME ZONE date, 65534 asn, sum(ipcount) ipcount
    FROM ip2lcidrbyasn_ipcount
    WHERE date = '2016-06-22'
  • 保有する IP 数が多い ASN の 13 傑について時系列にその推移を示すグラフ。
_images/ipcountbyasn.png
$ psql mydb pgsql -t -c "SELECT asn, ipcount FROM ip2lcidrbyasn_ipcount WHERE date = (SELECT max(date) FROM ip2lcidrbyasn_ipcount) GROUP BY asn, ipcount ORDER BY ipcount DESC LIMIT 13;" \
| awk ' \
        BEGIN { C[1] =  "111111A0"; \
                C[2] =  "FF0000A0"; \
                C[3] =  "00FF7FA0"; \
                C[4] =  "FF00FFA0"; \
                C[5] =  "7FFF00A0"; \
                C[6] =  "0000FFA0"; \
                C[7] =  "FF7F00A0"; \
                C[8] =  "00FFFFA0"; \
                C[9] =  "FF007FA0"; \
                C[10] = "00FF00A0"; \
                C[11] = "7F00FFA0"; \
                C[12] = "FFFF00A0"; \
                C[13] = "007FFFA0"; \
                print "rrdtool graph ipcountbyasn.png --start=1462970000 --end 1465603200 --step 86400 --title \47Top 12 IP Address Holders by ASN\47 --vertical-label \47ipcount\47 --logarithmic --units=si --width 1200 --height 400 --watermark \47powered by daseinred.\47 \\";} \
        !/^$$/ { print "\"DEF:" $$1 "=sql//pgsql/host=127.0.0.1/dbname=daseinred/username=pgsql/password=//ip2lcidrbyasn_ipcount/extract(epoch from date)/ipcount/asn=\47" $$1 "\47:avg:AVERAGE\" \"LINE1:" $$1 "#" C[NR] ":" $$1 "\" \\";}' \
| /usr/local/bin/bash
  • 各 ASN の保有 IP 数をヒストグラムにしたもの。ただし、IP2Location から頂いた6月度のデータを各日付にコピーしただけなので、当然に変化はない。
_images/ipcountbyasn_histgram.png
$ psql daseinred pgsql -c "select asn, ipcount from ip2lcidrbyasn_ipcount where date = '2016-06-10' order by ipcount desc;" \
| bin/ipcountbyasn_histgram.py
ipcountbyasn_histgram.py
#!/usr/local/bin/python2


#import argparse
#import datetime
from sys import stdin
import re
import numpy as np
import matplotlib.pyplot as plt

#p = argparse.ArgumentParser(description='ipcount to histgram')
#p.add_argument('--date', '-d', help='assign date as YYYYMMDD.', default=datetime.date.today())
#args = p.parse_args()

ipcount = {}
r = re.compile(r'^[ 	]+([0-9]+).*[ 	]+([0-9]+)')

for line in stdin.readlines():
  m = r.search(line.rstrip())
  if m is not None:
    ipcount[m.group(1).decode('utf-8')] = int(m.group(2).decode('utf-8'))
ipcount.pop('65534', None)

ipcv       = ipcount.values()
ipc_min    = np.min(ipcv)
ipc_max    = np.max(ipcv)
ipc_median = np.median(ipcv)
ipc_mean   = np.mean(ipcv)

fig = plt.figure()
ax1 = fig.add_subplot(211)

ax1.set_title("Distribution of IP Addresses assigned to ASN")
ax1.set_ylabel("frequency")
ax1.set_yscale('log')
ax1.set_xlim([0.95 * ipc_min, ipc_max / 0.95])
(n, bins, patches) = ax1.hist(ipcv, bins=48)
ax1.set_xticks((2*10**7, 4*10**7, 6*10**7, 8*10**7, 10*10**7, 12*10**7, 14*10**7))
ax1.set_xticklabels(('2', '4', '6', '8', '10', '12', '$14*10^7$'))
ax1.axvline(x=ipc_min, color='darkorange', label='min: {}'.format(ipc_min))
ax1.annotate('min', [ipc_min, 0.95 * np.max(n)], color='darkorange', rotation=90)
ax1.axvline(x=ipc_median, color='darkorange', label='median: {}'.format(int(ipc_median)))
ax1.annotate('median', [ipc_median, 0.95 * np.max(n)], color='darkorange', rotation=90)
ax1.axvline(x=ipc_mean, color='darkorange', label='mean: {}'.format(int(ipc_mean)))
ax1.annotate('mean', [ipc_mean, 0.95 * np.max(n)], color='darkorange', rotation=90)
ax1.axvline(x=ipc_max, color='darkorange', label='max: {}'.format(ipc_max))
ax1.annotate('max', [ipc_max, 0.95 * np.max(n)], color='darkorange', rotation=90)
for k, v in sorted(ipcount.items(), key=lambda x:x[1], reverse=True)[:3]:
  ax1.annotate(k, [v, 0.1 * np.max(n)], color='darkorange', rotation=90)
ax1.annotate('N={}\n2016/Jun/10'.format(len(ipcv)), [0.8 * ipc_max, 0.8 * np.max(n)], color='darkorange')

ax2 = fig.add_subplot(212)
ax2.set_xlabel("# of IP addresses assigned")
ax2.set_ylabel("frequency")
ax2.set_xscale("log")
ax2.set_xlim([0.95 * ipc_min, 3 * ipc_max])
(n, bins, patches) = ax2.hist(ipcv, bins=10**np.linspace(0.1, (np.log10(ipc_max) + 0.1), 48))
ax2.axvline(x=ipc_min, color='darkorange', label='min: {}'.format(ipc_min))
ax2.annotate('min', [ipc_min, 0.95 * np.max(n)], color='darkorange', rotation=90)
ax2.axvline(x=ipc_median, color='darkorange', label='median: {}'.format(int(ipc_median)))
ax2.annotate('median', [ipc_median, 0.95 * np.max(n)], color='darkorange', rotation=90)
ax2.axvline(x=ipc_mean, color='darkorange', label='mean: {}'.format(int(ipc_mean)))
ax2.annotate('mean', [ipc_mean, 0.95 * np.max(n)], color='darkorange', rotation=90)
ax2.axvline(x=ipc_max, color='darkorange', label='max: {}'.format(ipc_max))
ax2.annotate('max', [ipc_max, 0.95 * np.max(n)], color='darkorange', rotation=90)
for k, v in sorted(ipcount.items(), key=lambda x:x[1], reverse=True)[:3]:
  ax2.annotate(k, [v, 0.1 * np.max(n)], color='darkorange', rotation=90)

fig.tight_layout()
fig.savefig('ipcountbyasn_histgram.png', format='png')
#plt.show()

備考

  • 2016/Jul/11 ごろから 2016/Jul/16 ごろまで書いた。
[1]パンチングホール が悪いのよ。い、いぇっ、私がいたらなくて...( 大井っち風 )