利用SQL区分网站域名IP地址归属的方法

摘 要

在IP网络的日常维护中，常常需要对网站域名以及IP地址的归属地进行区分，本文介绍了如何利用SQL数据库对网站域名以及IP地址的归属进行查询区分的方法。

【关键词】SQL 网站域名 IP地址 归属 查询

1 前言

IP网维护人员在日常网络维护工作中，经常会遇到大量网站域名或IP地址需要按市分公司区分归属地，然后由对应市分公司对所属域名或IP进行相关处理。在数量少的时候，可以人工通过查询资料进行区分，可当查询量达到成千上万时，人工查询几乎是个不可能完成的工作。建立一个IP地址归属查询数据库，通过SQL（结构化查询语言）对需要查询的域名或IP地址按市分公司区分，可以大大提高工作效率和查询的准确性。

2 查询原理及准备工作

IP网络维护人员一般都有自己维护管理的IP地址归属的详细资料，但是把要查询的IP地址与IP地址归属资料进行查询关联是个难点，这个难点难在IP地址的记录方式是点分十进制的，四段数字被三个点分隔开，每段的十进制数是0至255之间的整数，每段数字前面的0可写也可不写，难以进行查询关联。

为了使IP地址便于查询，可将点分十进制的IP地址换算成十进制的整数，这样就可以比较大小进行查询。一个IP地址段的开始和结束IP地址分别换算成十进制整数，将一个待查询的IP地址也换算成十进制地址数，利用SQL的查询语句，待查的IP地址跟SQL数据库中的IP地址段的开始和结束IP的十进制数比较，如果大于等于开始IP数，并且小于等于结束IP数，那么说明这个IP地址是在这个IP地址段内的IP，否则这个IP地址不属于这个IP段。如果IP网络已经按地域进行过分域，某一个IP地址段在骨干路由器中可以查寻到路由对应的AS号，AS号对应的地域，就是IP所属的地域。

2.1 点分十进制IP换算成十进制整数的方法

点分十进制IP换算成十进制整数可以利用EXCEL进行，EXCEL可以双击表格右下角的点，使公式自动下拉计算。假如EXCEL表的A1单元格是一个点分十进制的IP，将A1单元格IP的十进制数以点为分界分别拆分到B1至E1四个格内，F1是拆分了最左边数的IP，G1是拆分了F1最左边数的IP，H1是最终该IP换算后的十进制数，可以复制H列，在EXCEL文件中选择性粘贴，粘贴数值，得到每个IP对应的十进制数值。A1之后每个单元格对应的计算公式如下：

B1= LEFT（A1，SEARCH（"."，A1，1）-1）

C1= LEFT（F1，SEARCH（"."，F1，1）-1）

D1= LEFT（G1，SEARCH（"."，G1，1）-1）

E1= RIGHT（G1，LEN（G1）-SEARCH（"."，G1，1））

F1= RIGHT（A1，LEN（A1）-SEARCH（"."，A1，1））

G1= RIGHT（F1，LEN（F1）-SEARCH（"."，F1，1））

H1= （（B1*256+C1）*256+D1）*256+E1

2.2 SQL查询使用前的准备工作

（1）建立一个Microsoft SQL Servers服务器。

（2）在SQL Servers上建立一个数据库表。

（3）上传到数据库中的表都用EXCEL保存为CSV（逗号分隔）格式，在SQL企业管理器中建立一个自己的数据库表，在数据库表里导入上传IP归属的相关数据表，导入上传表时数据源选“文本文件”，第一行含列名称打钩，后面提到的上传导入数据库表方法都一样。

（4）在数据库的表中上传属于本省的IP，表名“ip_henan”，列名可以包含net（IP段的网络IP即起始IP）、mask（网络的掩码）、start_ip（IP段的起始IP的十进制数）、end_ip（IP段的结束IP的十进制数）、area（IP所属省的名字）等。

（5）上传全省的IP路由表，表名“ip_route_table”，这个表是一个比较关键的表，该表可以从核心骨干路由器上得到，包含的列名可以有net（某段网络IP地址的起始IP）、mask（掩码）、from_router（从那台路由器学习来的）、as_no （AS号）、start_ip（IP段的起始IP的十进制数）、end_ip（IP段的结束IP的十进制数）。

（6）上传全省AS号对应的市分公司名，表名“ip_as”，列名包括city（城市名）、as_no（城市对应的AS号）。

3 查询域名对应IP的方法

如果待查域名没有IP地址，需要先查询域名对应的IP地址，将域名对应的IP在UNIX主机上用到dig命令查出来，将含A或CNAME记录的IP地址记录到一个临时文本文件中，然后将域名与IP地址用SQL查询联系到一起，再根据IP地址查询归属地域。如果待查域名已有IP地址，可以直接查询IP地址对应的地市归属。

3.1 步骤一：将原始查询资料整理成标准格式

（1）将需查询的域名在excel中整理成统一的格式，另存成CSV（逗号分隔）格式，文本名“name_ip_search_excel”；列名按id（序号），web（网站名），name（域名）起，如果原始列表里列名有其他项，可以根据情况自定义，但域名一列一定要输入name；

（2）在SQL查询分析器中，先清除以前的表记录TRUNCATE table name_ip_search_excel；

（3）导入到SQL server 数据库的表中。

3.2 步骤二：将需查询的域名单独整理出来

（1）将所查域名name这列单独保存，存成文本文件“name_to_ip_search”；

（2）先清除以前的表记录TRUNCATE table name_to_ip_search；

（3）导入到SQL server数据库的表中。

3.3 步骤三：利用dig命令查询所有域名对应的IP

（1）用excel 编辑name_to_ip_search表，前后各加一列；前一列均填充 “dig “ ，后一列填充：“ | egrep "-------A-------|-------CNAME-------" >>/tmp/get_ip_result.txt”（引号中内容）；

（2）将3列拷贝至UltraEdit文本编辑软件中，首先将“ ”（tab键）替换为空格，然后，将“-------”替换为“ ”（tab键），tab键无法输入，可以先在记事本里输入，然后粘贴过去；

（3）另存为getip.sh文件；

（4）上传getip.sh到可以使用dig命令的UNIX主机中，设置允许执行权限：chmod 755 getip.sh；

（5）删除以前的文件，执行rm f /tmp/get_ip_result.txt；

（6）用 ./getip.sh &执行查询命令；

（7）根据内容多少，查询时间长短不一定，可以用ls -l 查看get_ip_result.txt文件大小是否不断增长，来判断是否查询完毕。

3.4 步骤四：将域名查询出来的IP整理成标准格式

（1）将执行后的/tmp/get_ip_result.txt文件导出；

（2）用excel打开修改，列名包含name（域名）、num（dig查出来的num数）、Inhao（dig查出来的IN号）、type（域名记录类型）、ip（域名对应的IP），另存成CSV格式文件“get_ip_result”；

（3）先清除以前的表记录 TRUNCATE table get_ip_result；

（4）导入到SQL server 数据库的表中。

3.5 步骤五：将需查询的域名与查询出来的IP对应起来

（1）将每个需要查询的域名name_to_ip_search，从结果get_ip_result中提取出来；有些域名采用CNAME方式，要查询到域名最终对应的IP，需要进行嵌套查询。本次案例进行了5次嵌套，部分域名可能需要增加更多级嵌套。

（2）在SQL查询分析器中执行如下脚本：

TRUNCATE table name_ip_result_tmp；

insert into name_ip_result_tmp select distinct a.name，b.ip --第一次直接查询A记录

from name_to_ip_search a ，get_ip_result b

where a.name+'.'=b.name

and b.type='A' ；

insert into name_ip_result_tmp select distinct a.name，c.ip --第二次嵌套查询

from name_to_ip_search a ，get_ip_result b，get_ip_result c

where a.name+'.'=b.name

and b.type='CNAME'

and b.ip=c.name

and c.type='A' ；

insert into name_ip_result_tmp select distinct a.name，d.ip --第三次嵌套查询

from name_to_ip_search a ，get_ip_result b，get_ip_result c，get_ip_result d

where a.name+'.'=b.name

and b.type='CNAME'

and b.ip=c.name

and c.type='CNAME'

and c.ip=d.name

and d.type='A'；

insert into name_ip_result_tmp select distinct a.name，e.ip --第四次嵌套查询

from name_to_ip_search a ，get_ip_result b，get_ip_result c，get_ip_result d，get_ip_result e

where a.name+'.'=b.name

and b.type='CNAME'

and b.ip=c.name

and c.type='CNAME'

and c.ip=d.name

and d.type='CNAME'

and d.ip=e.name

and e.type='A'；

insert into name_ip_result_tmp select distinct a.name，f.ip --第五次嵌套查询

from name_to_ip_search a ，get_ip_result b，get_ip_result c，get_ip_result d，get_ip_result e，get_ip_result f

where a.name+'.'=b.name

and b.type='CNAME'

and b.ip=c.name

and c.type='CNAME'

and c.ip=d.name

and d.type='CNAME'

and d.ip=e.name

and e.type='CNAME'

and e.ip=f.name；

3.6 步骤六：将原始查询资料与IP对应起来

（1）将name_ip_search_excel 和name_ip_result_tmp进行关联处理，取最终结果；

（2）脚本：

select * from name_ip_search_excel aa， name_ip_result_tmp bb

where aa.name=bb.name

union all

select * from name_ip_search_excel aa， name_ip_result_tmp bb

where aa.namebb.name and aa.name like '%'+bb.name+'%'

执行完后数分钟会得到原始查询资料后面加查询的域名及IP地址的列表结果。以上六步将域名对应的IP地址查询了出来，下面可以根据IP地址定位所属市分公司。

4 根据IP地址定位其所属市分公司的方法

如果有其他已知IP地址需要定位归属地的工作，可以直接按此方法进行查询。

4.1 步骤一：将域名与IP地址资料整理成标准格式

（1）将带IP地址的结果拷贝到excel中，将IP地址按前面介绍的方法换算成十进制数放在后面一列，列名称为ip_num，此列名很重要，不要输错，存成CSV（逗号分隔）格式，文件名“ip_search_table”。

（2）菜单“工具”中选择“SQl查询分析器”，在分析器打开后，清除以前的记录，执行：Delete from ip_search_table；

（3）将ip_search_table表导入到SQL server 数据库的表中

4.2 步骤二：将IP与市分公司对应起来

（1）在SQL查询分析器中：执行

select distinct bb.*，，aa.mask，aa.city from

（select distinct a.*，b.area

from ip_search_table a left join ip_henan b

on a.ip_num >=b.start_ip and a.ip_num

） bb

left join

（select a.*，b.city

from ip_route_table a，ip_as b

where a.as_no=b.as_no

） aa

on bb.ip_num >= aa.start_ip_num

and bb.ip_num

（2）一般数分钟后查询可以结束，将查询结果拷贝到Excel中，删除一些域名重复的行，和不用的参考列，在IP地址后面可以得到所属省、市分公司的区分结果。如果所属省结果内容为NULL，可能该IP不属于自己的省，如果是属于自己省的IP，但是市分公司结果内容为NULL，可能全省的IP路由表不准确，需要在资料和设备上核对后更新数据库中的全省IP路由表ip_route_table。

5 小结

以往人工查询IP地址归属时，非常费时费力，利用SQL查询，数千条的IP地址定位只要几分钟就可以完成。而且利用SQL查询比较灵活，对需查询的IP地址列表的原始格式没有严格要求，如果能进一步完善IP归属资料数据库的话，利用SQL查询可以查询到更多的信息。利用SQL还可以对全省各市分公司的IP地址使用情况进行分析，分析各市分公司的拨号地址池和专线地址池使用情况，找到未使用的IP地址，提高市分公司的IP地址利用率。建立一个IP地址归属查询数据库后，通过SQL可以开发出更多的功能。

参考文献

[1][美]斯蒂芬森，[美]晋劳，[美]琼斯著，井中月，郝记生译. SQL入门经典（第5版）[M].北京：人民邮电出版社，2011-11-1.

[2][美]Ben Forta著，钟鸣，刘晓霞译. SQL必知必会（第4版）[M].北京：人民邮电出版社，2013-5-1.

作者单位

中国联合网络通信有限公司河南省分公司网络管理中心 河南省郑州市 450000