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的用户数据
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('@',ipacc)-1) 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表信息匹配
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('@',ipacc)-1) 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表内，与备份表进行对比，发现有改动则作记录，并通知相关的维护人员进行资料的更新。
修改
针对交换机设备的口令错误的情况，提供提示告知维护人员进行修改。
注意事项
Brasdata表的更新
定期采集，每天三次；每次删除15天前的记录；该表由系统自动更新。
Switchdata表的更新
定期采集，每天三次；每次删除15天前的记录；该表由系统自动更新。
Accmac表的更新
每天更新一次，发生时间在brasdata、switchdata表更新完成之后；每15天删除30天前的记录；每次删除记录前，生成备份表，名称为accmac_删除日期，其中“删除日期”格式为“年月日小时” ，如2007071501。
Kduser表的更新
Kduser为手工导入数据，不能实现实时的动态更新；每7天从IBss读取数据；然后进行手工导入；计划实现程序导入。
Rd_dsl_port表的更新
该表为手工导入，从IBss表导入；计划实现程序导入。
系统功能界面截图
WEB浏览查询界面
User access index : 2
State : Used
User name : bggaz027125@pingnan-pppoe
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
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)
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 : bgg8004900@pingnan-pppoe
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
