SQLserver2000上にIPアドレスが記載されたテーブルがある。
このテーブル上に存在しないIPアドレス一覧を「サブネットマスク」と「開始IPアドレス指定」で取得するツールを作りたいという話があった。
いろいろ考えたが、クエリで対応する。方針としては、
- 開始IPアドレスをインクリメントする。インクリメントした値を一時テーブルにinsertする。
- インクリメントしたIPアドレスをサブネットマスクでmaskする。
- mask後の値が前回と変わっていたら終了する。前回と同じ場合は1に戻る。
- 一時テーブルとチェックしたいテーブルをleft joinし、チェックしたいテーブル側のIPアドレスがnullになったものを抽出する。
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempTool')) drop table #tempTool set nocount on declare @ip1 bigint; declare @ip2 bigint; declare @ip3 bigint; declare @ip4 bigint declare @subnet1 bigint; declare @subnet2 bigint; declare @subnet3 bigint; declare @subnet4 bigint declare @terminateip1 bigint;declare @terminateip2 bigint;declare @terminateip3 bigint;declare @terminateip4 bigint --@ip1.@ip2.@ip3.@ip4 --IPアドレスを指定します select @ip1=192 select @ip2=168 select @ip3=1 select @ip4=100 --@subnet1.@subnet2.@subnet3.@subnet4 --サブネットマスクを指定します select @subnet1=255 select @subnet2=255 select @subnet3=255 select @subnet4=0 --@terminateip1.@terminateip2.@terminateip3.@terminateip4 --終端IPアドレスを指定します(開始IP以下の値を指定すると、サブネットの終端まで実行) select @terminateip1=0 select @terminateip2=0 select @terminateip3=0 select @terminateip4=0 --オクテット計算用定数 declare @div1 bigint declare @div2 bigint declare @div3 bigint select @div1=16777216 select @div2=65536 select @div3=256 declare @ipaddress bigint declare @ipaddressplus bigint declare @terminateipaddress bigint declare @subnetmask bigint declare @maskedipaddress bigint declare @maskedipaddressplus bigint --IPアドレスを数値にするよ select @ipaddress = @ip1*@div1 + @ip2*@div2 + @ip3*@div3 + @ip4 --サブネットマスクを数値にするよ select @subnetmask = @subnet1*@div1 + @subnet2*@div2 + @subnet3*@div3 + @subnet4 --終端IPアドレスを数値にするよ select @terminateipaddress = @terminateip1*@div1 + @terminateip2*@div2 + @terminateip3*@div3 + @terminateip4 + 1 --IPアドレスをマスクするよ select @maskedipaddress = @ipaddress & @subnetmask -- 戻りデータ格納テーブル変数 create Table #tempTool ( ip1 int, ip2 int, ip3 int, ip4 int ); --IPアドレス(加算用)を初期化するよ select @ipaddressplus=@ipaddress --マスクされたIPアドレス(加算用)を初期化するよ select @maskedipaddressplus=@maskedipaddress --IPアドレスのマスク値が変わるまで繰り返します While @maskedipaddress=@maskedipaddressplus and (@ipaddressplus<>@ rminateipaddress) Begin --加算されたIPアドレスをワークに入れるよ insert into #tempTool values ( @ipaddressplus / @div1, (@ipaddressplus % @div1) / @div2, (@ipaddressplus % @div2) / @div3, @ipaddressplus % @div3 ) --IPアドレスを加算するよ select @ipaddressplus=@ipaddressplus+1 --マスク値を再計算するよ select @maskedipaddressplus = @ipaddressplus & @subnetmask end --ここで#tempToolに既存のテーブルをleft joinして、既存テーブル側がnullのものを取得する drop table #tempTool set nocount off