IPアドレスについて下記の情報を取得する方法を説明したいと思います。
・プロバイダ・ISP・ASN情報
まず、この情報を持っているデータを取得しましょう。
IP2Location™ LITE IP-ASN Database
(IP2LOCATION-LITE-ASN.CSV)
上記のリンク先から「IPV4 CSV」と言うファイルをダウンロードします。
今回はCSVファイルを利用する方法を説明したいと思います。
このCSVファイルは月1回更新されます。
毎月初旬頃更新されますので、毎月手動でデータを更新する必要があります。
その理由はIPアドレスの情報が日々変わるからです。
昨日は日本だったIPが今日はアメリカになったりします。
今回利用するCSVファイルは無料バージョンなので、月1回の更新まで待つしかありません。
それではCSVファイルのデータを登録するテーブルとINDEXを作成しましょう。
-- #### テーブル作成
CREATE TABLE dbo.tGeoIpAsn
(
StartNo bigint
,EndNo bigint
,BandWidth varchar(20)
,Asn varchar(10)
,AsnName varchar(1000)
)
GO
-- #### INDEX 作成
CREATE NONCLUSTERED INDEX [IX_tGeoIpAsn_StartNo_EndNo] ON [dbo].[tGeoIpAsn]
(
[StartNo] ASC,
[EndNo] ASC
)
GO
次はデータを登録しましょう。
ダウンロードした「IP2LOCATION-LITE-ASN.CSV」ファイルを BULK INSERT を利用して登録したいと思います。
BULK INSERT する際はXML形式のFORMATFILEを指定して登録したいと思います。
メモ帳などを利用して「IP2LOCATION-LITE-ASN_Bulk_Insert.xml」ファイル新しく作成して下記の内容を保存してください。
<?xml version="1.0"?>
<BCPFORMAT
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="""/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="",""/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="",""/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="",""/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="",""/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR=""\r\n"/>
</RECORD>
<ROW>
<COLUMN SOURCE="2" NAME="StartNo" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="3" NAME="EndNo" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="4" NAME="NationName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="5" NAME="RegionName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="6" NAME="CityName" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
準備ができましたら、下記の BULK INSERT のSQL文を実行してデータを登録しましょう。
ファイルのパースは皆さんの環境に合わせて変更して使ってください。
登録時間は環境によって異なりますが、約15秒くらいかかります。
-- #### BULK INSERTを利用してデータを一括登録
BULK INSERT dbo.tGeoIpAsn
FROM 'C:\innoya\IP2LOCATION-LITE-ASN.CSV'
WITH
(
FORMATFILE = 'C:\innoya\IP2LOCATION-LITE-ASN_Bulk_Insert.xml'
)
GO
次はIPアドレスに紐付く各情報を取得する関数を作成しましょう。
■ ロバイダ・ISP・ASN情報 ：fnGetGeoIPAsn
CREATE FUNCTION dbo.fnGetGeoIPAsn (
@IpAddr varchar(39)
)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @RtnVal varchar(1000)
SET @RtnVal = ''
-- #### IP情報にPORT情報がある場合、IPのみ取得 #### START ####
DECLARE @CheckPort int
SET @CheckPort = CHARINDEX(':', @IpAddr)
IF (@CheckPort > 0)
BEGIN
SET @IpAddr = LEFT(@IpAddr,@CheckPort-1)
END
-- #### IP情報にPORT情報がある場合、IPのみ取得 #### END ####
-- #### IPを数字に変更 #### START ####
DECLARE @IpNum bigint --100件検索時に12秒
--DECLARE @IpNum numeric(18,0) --100件検索時に12秒
--DECLARE @IpNum decimal(21,0) --速度遅い：100件検索時に25秒
DECLARE @ip1 bigint, @ip2 bigint, @ip3 bigint, @ip4 bigint
SET @ip1 = PARSENAME(@IpAddr, 4)
SET @ip2 = PARSENAME(@IpAddr, 3)
SET @ip3 = PARSENAME(@IpAddr, 2)
SET @ip4 = PARSENAME(@IpAddr, 1)
--SET @IpNum = @ip1*(256*256*256)+@ip2*(256*256)+@ip3*(256)+@ip4
SET @IpNum = @ip1*(16777216)+@ip2*(65536)+@ip3*(256)+@ip4
-- #### IPを数字に変更 #### END ####
SELECT TOP 1 @RtnVal = AsnName
FROM dbo.tGeoIPAsn WITH (NOLOCK)
--WHERE StartNo <= @IpNum AND EndNo >= @IpNum -- 検索速度遅い
WHERE @IpNum BETWEEN StartNo AND EndNo -- 検索速度速い
RETURN @RtnVal
END
GO
これで作業が全部終わりました。
それではテストをしてみましょう。
DECLARE @IpAddr varchar(39)
SET @IpAddr = '126.131.110.29'
SELECT dbo.fnGetGeoIPAsn(@IpAddr) AS [ASN情報]
実行結果