宽带端口自动稽核系统的设计及应用(一)-通信工(4)
2013-05-22 01:30
导读：measure_name varchar(150),--测量室名称 measure_code varchar(100),--测量室编码 vlan int,--用户对应的brasvlan vpi int,--用户对应的brasvpi vci int,--用户对应的brasvci svlan int,-
measure_name varchar(150),--测量室名称
measure_code varchar(100),--测量室编码
vlan int,--用户对应的brasvlan
vpi int,--用户对应的brasvpi
vci int,--用户对应的brasvci
svlan int,--用户对应的switchdata-vlan
svpi int,--用户对应的switchdata-vpi
svci int,--用户对应的switchdata-vci
te int,--Dslam用户对应的端口摸板
brasip varchar(50), --宽带接入服务器ip
brasslot int, --宽带接入服务器的槽位
brasport int,
brassubslot int, --宽带接入服务器的端口
flag int, --处理标志,初始为0
gettime datetime --brasdata的采集时间
)
③brasdata往临时表accmac插入设备资料
insert into accmac(ipacc,brasip,brasslot,brasport,brassubslot,vlan,vpi,vci,usermac,gettime) select ipacc,brasip,brasslot,brasport,brassubslot,vlan,vpi,vci,usermac,gettime from brasdata
④更新临时表accmac内的资源资料与switchdata表内纪录匹配
update a set a.switchip=s.switchip,a.port=s.port,a.svlan=s.vlan,a.svpi=s.vpi,a.svci=s.vci,a.flag=1 from accmac a,(select * from switchdata where porttype not in('trunk','trunking','tagged'))s where a.usermac=s.usermac
⑤更新brasdata内的资源标志，将处理后的东西变成1
update brasdata set flag=1 where usermac in(select usermac from accmac where flag=1)
⑥删除accmac中没有匹配的数据记录
delete accmac where flag is null
⑦匹配facility中节点名称，设备类型
update a set a.nodename=f.nodename,a.tswitchtype=f.tswitchtype from accmac a,facility f where a.switchip=f.switchip
⑧匹配营帐用户资料kduser的用户数据
（科教作文网http://zw.ΝsΕac.cOM编辑）
update a set a.cust_name=k.cust_name,a.prod_no1=k.product_no1,a.prod_addr=k.prod_addr from accmac a,kduser k where a.ipacc=k.product_no and a.ipacc not like
update accmac set ipacc1=substring(ipacc,1,charindex() where ipacc like
update a set a.cust_name=k.cust_name,a.prod_no1=k.product_no1,a.prod_addr=k.prod_addr from accmac a,kduser k where a.ipacc1=k.product_no and a.ipacc1 is not null
⑨匹配rd_dsl_port的测量室相关信息
update a set a.hcol_code=r.hcol_code,a.dshcol_seq=r.dshcol_seq,a.hcol_code1=r.hcol_code1,a.vhcol_seq=r.vhcol_seq,a.measure_name=r.measure_name,a.measure_code=r.measure_code from accmac a,rd_dsl_port r where a.switchip=r.switchip and a.port=r.port
⑩匹配pvc内的资料
update a set a.switchip=r.switchip,a.port=r.port,a.svpi=r.source_vpi,a.svci=r.source_vci,a.hcol_code=r.hcol_code,a.dshcol_seq=r.dshcol_seq,a.hcol_code1=r.hcol_code1,a.vhcol_seq=r.vhcol_seq,a.measure_name=r.measure_name,a.measure_code=r.measure_code from accmac a,(select * from pvc where switchip is not null) r where a.brasslot=r.brasslot and a.brasport=r.brasport and a.brassubslot=r.brassubslot and a.vpi=r.sink_vpi and a.vci=r.sink_vci and a.vlan=-1 and a.brasip='222.217.183.6'
GO
动态更新表
CREATE PROCEDURE pro_accmac1
AS
①brasdata往临时表accmac插入设备资料
insert into accmac(ipacc,brasip,brasslot,brasport,brassubslot,vlan,vpi,vci,usermac,gettime) select ipacc,brasip,brasslot,brasport,brassubslot,vlan,vpi,vci,usermac,gettime from brasdata where flag=0
②更新临时表accmac内的资源资料与switchdata表内纪录匹配
update a set a.switchip=s.switchip,a.port=s.port,a.svlan=s.vlan,a.svpi=s.vpi,a.svci=s.vci,a.flag=1 from accmac a,(select * from switchdata where porttype not in('trunk','trunking','tagged'))s where a.usermac=s.usermac and a.flag is null
③更新临时表内纯ATM上行的用户资料与pvc表信息匹配
（科教范文网 lw.nSeAc.com编辑发布）
update a set a.switchip=r.switchip,a.port=r.port,a.svpi=r.source_vpi,a.svci=r.source_vci,a.flag=1 from accmac a,(select * from pvc where port is not null) r where a.brasip='222.217.183.6' and a.brasport=r.brasport and a.brasslot=r.brasslot and a.brassubslot=r.brassubslot and a.vpi=r.sink_vpi and a.vci=r.sink_vci and a.flag is null
④更新brasdata内的资源标志，将处理后的东西变成1
update brasdata set flag=1 where usermac in(select usermac from accmac where flag=1) and flag=0
⑤删除accmac数据将flag位置0
delete accmac where flag is null
⑥匹配facility中节点名称，设备类型
update a set a.nodename=f.nodename,a.tswitchtype=f.tswitchtype from accmac a,facility f where a.switchip=f.switchip and a.nodename is null
⑦匹配营帐用户资料kduser的用户数据
--普通拨号用户
update a set a.cust_name=k.cust_name,a.prod_no1=k.product_no1,a.prod_addr=k.prod_addr from accmac a,kduser k where a.ipacc=k.product_no and a.ipacc not like and a.cust_name is null
--特殊拨号用户
update accmac set ipacc1=substring(ipacc,1,charindex() where ipacc like and cust_name is null
--固定ip用户
update a set a.ipacc1=w.product_no from accmac a,wbzh w where
a.cust_name is null and a.ipacc=w.ipacc and a.ipacc like '%.%'
update a set a.cust_name=k.cust_name,a.prod_no1=k.product_no1,a.prod_addr=k.prod_addr from accmac a,kduser k where a.ipacc1=k.product_no and a.ipacc1 is not null and a. cust_name is null
⑧匹配rd_dsl_port的测量室相关信息
update a set a.hcol_code=r.hcol_code,a.dshcol_seq=r.dshcol_seq,a.hcol_code1=r.hcol_code1,a.vhcol_seq=r.vhcol_seq,a.measure_name=r.measure_name,a.measure_code=r.measure_code from accmac a,rd_dsl_port r where a.switchip=r.switchip and a.port=r.port and a.hcol_code is null
大学排名
GO
功能实现
查询
能通过“客户名称”、“客户上网帐号”、“固定IP地址”、“客户MAC地址”四项条件进行查询，支持模糊查询方式。
有效记录的查询：凡brasdata与switchdata （ATM涉及PVC）能够匹配的用户记录，定义为有效记录，存储在accmac表内；否则，视为无效记录。进行有效记录查询的操作是针对accmac表进行操作的。具体，
结果显示能分四大块“客户资料 ”、“宽带接入服务器属性”、“直连交换机属性”、“上网交换机所经过的交换机端口”。
“客户资料”从kduser表读取，显示结果包含“名称、接入方式、改装电话、套餐类型、联系人、联系电话、使用状态、合帐号码、业务最新变更时间、业务开通时间、装机地址”
“宽带接入服务器属性”从brasdata读取，显示结果包含“接入服务器IP、上网方式、接入服务器槽位/子槽位/端口、对应业务VLAN、对应VPI/VCI、采集时间”
“直连交换机属性”从switchdata读取，显示结果包含“交换机IP、端口、业务VLAN、VPI/VCI、采集时间、MAC地址”
“上网交换机所经过的交换机端口”从switchdata读取，显示结果包含“交换机IP、交换机端口、采集时间、端口类型”
何种情形下使用无效记录的查询：
有效记录无法查询到结果，需要知道帐户是否对应交换机端口或者改帐户是否上过网或者是否在IBss存在；该操作将分别进行brasdata、kduser、switchdata表的查询，并将查询结果显示出来；显示内容如果存在，则如有效记录查询结果显示一样，体现出相应的显示内容。
更新
发生在accmac表内，与备份表进行对比，发现有改动则作记录，并通知相关的维护人员进行资料的更新。
修改
针对交换机设备的口令错误的情况，提供提示告知维护人员进行修改。
内容来自www.nseac.com
注意事项
Brasdata表的更新
定期采集，每天三次；每次删除15天前的记录；该表由系统自动更新。
Switchdata表的更新
定期采集，每天三次；每次删除15天前的记录；该表由系统自动更新。
Accmac表的更新
每天更新一次，发生时间在brasdata、switchdata表更新完成之后；每15天删除30天前的记录；每次删除记录前，生成备份表，名称为accmac_删除日期，其中“删除日期”格式为“年月日小时” ，如2007071501。
Kduser表的更新
Kduser为手工导入数据，不能实现实时的动态更新；每7天从IBss读取数据；然后进行手工导入；计划实现程序导入。
Rd_dsl_port表的更新
该表为手工导入，从IBss表导入；计划实现程序导入。
系统功能界面截图
WEB浏览查询界面
CS客户端管理系统截图
应用及效果评估
该系统自投入运行以来，已经在很多方面获得了应用，并取得了很好的效果，主要表现在以下方面：
系统在ADSL端口速率达标整治工作中的应用
系统可以与现有的ADSL端口速率监控系统很好的结合起来，准确地把不达标的端口与用户帐号、用户地址关联起来。由于该系统为准确定位不达标端口直接提供了用户帐号、用户地址信息，提高了用户整治精确度，提高了整治效率。在以该系统的为重要技术支撑手段的帮助下，使维护人员能较好的找到不达标的用户进行针对性的整治。经过几个月的整治，贵港的端口达标率由2007年3月份的92.1％提高到了2007年8月份的96.2%。
系统使用的截图，如下图8.1示：
图8.1
系统在业务工单、故障处理方面的应用
系统目前在网络监控中心、线路维护中心、10000客户服务中心进行业务工单、故障处理方面的试用，收到很好的效果。使用WEB页面的“用户查询”功能，非常方便、快捷的查询到用户的所有相关信息，极大的提高了对用户故障的处理能力、缩短了业务工单的开通时间。比如，原先网络监控中心、10000客户服务中心至少需要10－15分钟处理的用户申告、用户端口提速工单，现在只需不到一分钟就能轻松准确的解决，大大提高了工作效率。据统计，端口提速工单的日积压数从系统投入使用前的5张/天，在系统投入使用后减少到0张/天。 （科教范文网http://fw.NSEAC.com编辑发布）
系统使用的截图，如下图8.2示：
图8.2
系统在宽带私拉用户方面的应用
由于系统采用二层协议进行数据采集，及时记录了用户帐号上网的端口以及MAC地址，故能准确地发现宽带的私拉用户，提高宽带私拉判断精度；为私拉用户营销入网提供了有力的支撑手段。如下图8.3示：
图8.3
系统在设备资源管理方面的应用
使用CS客户端管理功能，非常方便的进行对设备资源的管理、数据的导出等，大大方便了对设备的管理和进行数据分析。如下图8.4示：
图8.4
附件一
关于brasdata表录入处理操作实例
ISN8850的操作
拨号用户的mac与帐号对应关系的获取
1、show aaa user，出现以下两种情况，具体以蓝色部分进行区别，其中CID:[X .0 .Y]中，X为6、14的均为VLAN接入，否则为ATM接入
（1）为IP上行用户情形
UserName:[bggzz009797]
UserID:[00129] CID:[6 .0 .11203]
UserIP:[218.21.79.82] State[3]
AccountingTime:[3:58:40] StartAccountingTime:[2007/04/14 18:07:02]
Interface:[slot 06 subslot 00 port 00 VLANID 0000465]
LatestFlowInfo: Low32[217567596]bytes High32[0]Gwords
（2）为ATM上行用户情形
UserName:[bgg8689093]
UserID:[00131] CID:[4 .0 .9838]
UserIP:[218.21.86.80] State[3]
AccountingTime:[76:06:32] StartAccountingTime:[2007/04/11 17:59:11]
Interface:[slot 11 subslot 02 port 02 VPI 001 VCI 091]
LatestFlowInfo: Low32[2077847746]bytes High32[2]Gwords
注意：红色部分的值，分别对应brasdata表中ipacc、brasslot,brassubslot,brasport，vpi，vci，vlan，如果没有VPI，VCI或者VLAN，均设置为-1； （科教范文网http://fw.ΝsΕΑc.com编辑）
2、执行show vpdn session X（X为slot号；2，3，4，6，14）, 可以得出RemMAC与cid（后四位为十六进制）的对应关系；两个cid经过进制转换后形成关联，这样就可以得出用户的帐号与用户MAC的对应关系，存在临时表内，具体如下：
===================================================================
SID Cid RemMAC LocMAC Intf VASt OIntf
===================================================================
5601 c002000 0014.78d6.332f 00e0.fc1d.873b VT1:384483 UP Gigabit-ethernet6/0/0.2
6777 c002001 00e0.4c3b.793d 00e0.fc1d.873b VT1:357428 UP Gigabit-ethernet6/0/0.4
3、匹配两表的cid及slot，就实现了拨号用户帐号与MAC的对应关系，将对应的usermac写入brasdata
获取固定IP用户的mac对应关系
1、执行使用show arp X（X为slot号；2，6，14）得出固定ip用户与mac槽位的对应关系，上述结果存入brasdata的ipacc、usermac字段，注意，需要将type类型为“interface”的记录剔除。采集结果如下界面：
IpAddress Mac_Address CID Vlink Vcc Vlan Type
==============================================================================
10.18.19.159 00e0.fc7f.5a7e 6.0.9768 714. 6.0.9768 66 Static
222.83.228.1 00e0.fc1d.873b Interface
（科教作文网http://zw.nseAc.com）
222.83.228.11 000a.ebc0.94a5 6.0.11100 293. 6.0.11100 111 Dynamic
222.83.228.12 000a.eb5c.5914 6.0.11773 286. 6.0.11773 112 Dynamic
222.83.228.13 0002.b350.5122 6.0.9317 422. 6.0.9317 113 Dynamic
222.83.228.14 000a.ebbe.1871 6.0.8562 413. 6.0.8562 114 Dynamic
222.83.228.15 000f.ea3e.7ba0 6.0.9577 361. 6.0.9577 115 Dynamic
222.83.228.16 000a.ebbb.073d 6.0.11041 301. 6.0.11041 116 Dynamic
222.83.228.17 00e0.fc16.d438 6.0.9718 352. 6.0.9718 117 Dynamic
218.21.87.252 000a.ebe3.dde1 2.0.11184 177. 2.0.11184 201 Dynamic
218.21.87.254 000e.1f43.e103 2.0.10446 173. 2.0.10446 3447 Dynamic
218.21.87.6 0014.78b9.1721 2.0.11153 165. 2.0.11153 397 Dynamic
2、将采集结果写入brasslot、brassubslot、brasport、vlan字段的处理方式，需要分两种情况进行处理，具体是以CID字段中X.0.Y中的X值不同进行区分：
(1)X不为2，则将CID值的：X.0.Y进行处理：X对应braslot、brasport、brassubslot均为0，VPI、VCI均为-1。
（2）X为2，需要继续执行该指令show cib 2 0 Y,得出如下记录（例，摘录）：
（科教范文网http://fw.ΝsΕΑc.com编辑）
GX_GGBB#show cib 2 0 8981
……
20.usAuxID: 0xa20
21.ExtPara IF Vpi/Vci: 10/2/0 19/128
22.FF03Flg: 0
……
将21项（红色部分）对应的记录分别填入:brasslot、brassubslot、brasport、vpi、vci同时将vlan值置-1。
MA5200G的操作
使用display domain,得出在用的域，将得出的数据中online=0的域剔出，剩下的域即为在用的域，指令执行后，得出如下界面，红色部分即为需要判断的项目。
-------------------------------------------------------------------------
Domainname State CAR Access-limit Online type
-------------------------------------------------------------------------
default0 Active 0 49152 0 Normal
default1 Active 0 49152 0 Normal
default_admin Active 0 49152 0 Normal
pingnan-pppoe Active 0 49152 3840 Normal
内容来自www.nseac.com
pingnan-static Active 0 49152 119 Normal
pingnan-ls Active 0 - - Device
pingnan-53 Active 0 - - Device
yancao.gx Active 0 49152 4 Normal
vpn Active 0 49152 27 Normal
pingnan-qiaobei Active 0 - - Device
sinopecgx.gx Active 0 49152 0 Normal
pingnan-static-8m Active 8 49152 26 Normal
（科教作文网http://zw.ΝsΕAc.Com编辑整理）
csl.gx Active 0 49152 3 Normal
cpic.gx Active 0 49152 0 Normal
gxcol.gx Active 0 49152 0 Normal
edu.gg Active 0 49152 47 Normal
dishui.ggpn Active 0 49152 0 Normal
dianli.ggpn Active 0 49152 0 Normal
shunfenger Active 2 49152 0 Normal
gxcourt.gx Active 0 49152 0 Normal
（科教范文网http://fw.nseac.com）
pingnan-static-20m Active 10 49152 1 Normal
pingnan-static-4m Active 4 49152 3 Normal
yibao.ggpn Active 0 49152 31 Normal
youzheng.gg Active 0 49152 0 Normal
pingnan-static-70m Active 11 49152 1 Normal
-------------------------------------------------------------------------
使用display access-user domain pingnan-pppoe detail，将结果填入brasdata中，其中红色部分是需要进行采集录入的数据资料，特别需要指出的是EthernetA/B/C.X（A、B、C、X均为数字）含有的数字分别对应MA5200G的brasslot（A）、brassubslot（B）、brasport（C）。
User access index : 2
State : Used
User name :
（科教作文网http://zw.nseAc.com）
User access interface : Ethernet1/1/1.2
User access inner vlan : 543
User mac : 0050-8d98-e747
User ip address : 222.216.201.147
User access type : PPPoE
User authentication type : PPP authentication
Normal-server-group : pingnan1
Two-level-acct-server-group : -
Physical-acct-server-group : -
Authen method : RADIUS
Current authen method : RADIUS
Authen result : Success
Action flag : Idle
Authen state : Authed
Author state : Idle
Accounting method : RADIUS
User access time : 2007/07/16 13:43:28
Accounting start time : 2007/07/16 13:43:28
Accounting state : Accounting
（科教作文网http://zw.ΝsΕac.cOM编辑）
EAP user : No
MD5 end : No
User msidsn name : -
Idle-cut-data (time,rate) : 0 minute, 60 Kbyte/minute
VPN instance : --
GRE group : -
UserGroup : -
Multicast-profile : -
Priority : 0
Policy-route-nexthop : -
Up car enable : Yes
Up average rate : 512 (kbps)
Up burst size : 102400 (kbits)
Down car enable : Yes
Down average rate : 2048 (kbps)
Down burst size : 102400 (kbits)
（转载自http://zw.ＮＳＥaＣ.com科教作文网）
Up packets number(high,low) : (0,184113)
Up bytes number(high,low) : (0,22524539)
Down packets number(high,low) : (0,230683)
Down bytes number(high,low) : (0,208135662)
Stb user type : Normal user
If in iptv hash : No
Shaping template : -
Queue index : 65535
Vp group : 65535
Option82 information : -
Are you sure to show some information?(y/n)[y]:
Shaping template : -
Queue index : 65535
Vp group : 65535
Option82 information : -
Are you sure to show some information?(y/n)[y]:y
User access index : 15
State : Used
User name : （科教作文网http://zw.ΝsΕAc.Com编辑整理）
User access interface : GigabitEthernet2/0/1.2
User access inner vlan : 478
User mac : 0019-210d-8299
User access type : PPPoE
User authentication type : PPP authentication
Normal-server-group : pingnan1
Two-level-acct-server-group : -
Physical-acct-server-group : -
Authen method : RADIUS
Current authen method : RADIUS
Authen result : Success
Action flag : Idle
Authen state : Authed
MA5200的操作
show connect username ，得出mac与帐号（或者ip地址）的匹配关系，将记录填入brasdata
300 UCIB=2402 State=Online
IP=218.65.247.107 MAC=00-e0-4c-fa-2b-f3 TotalBytes=2088598
301 UCIB=2405 State=Online
IP=218.65.244.43 MAC=00-18-f3-25-47-ab TotalBytes=1711302330
302 UCIB=2412 State=Online （科教作文网http://zw.NSEaC.com编辑发布）
IP=218.65.245.171 MAC=00-0a-eb-1a-a1-32 TotalBytes=106819160
303 UCIB=2418 State=Online
IP=218.65.244.62 MAC=00-50-8d-77-9e-84 TotalBytes=3177381
304 UCIB=2419 State=Online
IP=218.65.244.11 MAC=00-19-5b-d4-93-a1 TotalBytes=825171132
305 UCIB=2421 State=Online
IP=218.65.247.201 MAC=00-04-61-71-8f-70 TotalBytes=108830479
306 UCIB=2422 State=Online
IP=218.65.246.184 MAC=00-08-0d-5c-2c-cf TotalBytes=968707746
307 UCIB=2442 State=Online
IP=218.65.246.239 MAC=00-0a-eb-84-be-a9 TotalBytes=720314400
308 UCIB=2462 State=Online
IP=218.65.247.232 MAC=00-04-61-46-e5-00 TotalBytes=145973470
309 UCIB=2465 State=Online
IP=218.65.246.70 MAC=00-19-21-d9-28-d5 TotalBytes=828731747
show vlan static user slot，得出为固定ip地址的用户，将ip地址，对应brasslot、brasport记录登记入
