ISG-Accounting
Материал из noname.com.ua
Cisco ISG: учет траффика по сервисам
radacct
Посмотрев на Acct-пакеты, вижу много полей, которых нет в классической таблице radacct
Accounting-Request packet from host 172.16.32.117:1646, id=244, length=219 Acct-Session-Id = "C345F4010000B2B1" Cisco-Service-Info = "NPREPAID_INTERNET" Framed-Protocol = PPP Framed-IP-Address = 195.69.244.194 Cisco-AVPair = "parent-session-id=C345F4010000B2B0" User-Name = "195.69.244.194" Acct-Status-Type = Start NAS-Port-Type = Virtual Cisco-NAS-Port = "0/0/1/613" NAS-Port = 0 NAS-Port-Id = "0/0/1/613" Service-Type = Framed-User NAS-IP-Address = 172.16.32.117 Event-Timestamp = "Jan 15 2009 21:23:43 EET" NAS-Identifier = "mongol.onet.ua" Acct-Delay-Time = 0
Accounting-Request packet from host 172.16.32.117:1646, id=253, length=281 Acct-Session-Id = "C345F4010000B2B1" Cisco-Service-Info = "NPREPAID_INTERNET" Framed-Protocol = PPP Framed-IP-Address = 195.69.244.194 Cisco-AVPair = "parent-session-id=C345F4010000B2B0" User-Name = "195.69.244.194" Cisco-Control-Info = "I0;45360" Cisco-Control-Info = "O0;36319" Acct-Input-Packets = 540 Acct-Output-Packets = 427 Acct-Input-Octets = 45360 Acct-Output-Octets = 36319 Acct-Session-Time = 541 Acct-Status-Type = Interim-Update NAS-Port-Type = Virtual Cisco-NAS-Port = "0/0/1/613" NAS-Port = 0 NAS-Port-Id = "0/0/1/613" Service-Type = Framed-User NAS-IP-Address = 172.16.32.117 Event-Timestamp = "Jan 15 2009 21:32:44 EET" NAS-Identifier = "mongol.onet.ua" Acct-Delay-Time = 0
В то время как
mysql> describe radacct; +----------------------+-------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-------------+------+-----+---------------------+----------------+ | RadAcctId | bigint(21) | NO | PRI | NULL | auto_increment | | AcctSessionId | varchar(32) | NO | MUL | | | | AcctUniqueId | varchar(32) | NO | MUL | | | | UserName | varchar(64) | NO | MUL | | | | Realm | varchar(64) | YES | | | | | NASIPAddress | varchar(15) | NO | MUL | | | | NASPortId | varchar(15) | YES | | NULL | | | NASPortType | varchar(32) | YES | | NULL | | | AcctStartTime | datetime | NO | MUL | 0000-00-00 00:00:00 | | | AcctStopTime | datetime | NO | MUL | 0000-00-00 00:00:00 | | | AcctUpdateTime | timestamp | NO | | CURRENT_TIMESTAMP | | | AcctSessionTime | int(12) | YES | | NULL | | | AcctAuthentic | varchar(32) | YES | | NULL | | | ConnectInfo_start | varchar(50) | YES | | NULL | | | ConnectInfo_stop | varchar(50) | YES | | NULL | | | AcctInputOctets | bigint(20) | YES | | NULL | | | AcctOutputOctets | bigint(20) | YES | | NULL | | | CalledStationId | varchar(50) | NO | | | | | CallingStationId | varchar(50) | NO | | | | | AcctTerminateCause | varchar(32) | NO | | | | | ServiceType | varchar(32) | YES | | NULL | | | FramedProtocol | varchar(32) | YES | | NULL | | | FramedIPAddress | varchar(15) | NO | MUL | | | | AcctStartDelay | int(12) | YES | | NULL | | | AcctStopDelay | int(12) | YES | | NULL | | | XAscendSessionSvrKey | varchar(10) | YES | | NULL | | +----------------------+-------------+------+-----+---------------------+----------------+
Для атрибутов
Cisco-Service-Info = "NPREPAID_INTERNET" Cisco-AVPair = "parent-session-id=C345F4010000B2B0" Cisco-NAS-Port = "0/0/1/613" NAS-IP-Address = 172.16.32.117 Event-Timestamp = "Jan 15 2009 21:23:43 EET" NAS-Identifier = "mongol.onet.ua"
нет места ни в таблице ни в запросе, вносящим данные.
sql.conf:
... accounting_update_query = " \ UPDATE ${acct_table1} \ SET \ FramedIPAddress = '%{Framed-IP-Address}', \ AcctSessionTime = '%{Acct-Session-Time}', \ AcctInputOctets = '%{Acct-Input-Gigawords:-0}' << 32 | \ '%{Acct-Input-Octets:-0}', \ AcctOutputOctets = '%{Acct-Output-Gigawords:-0}' << 32 | \ '%{Acct-Output-Octets:-0}' \ WHERE AcctSessionId = '%{Acct-Session-Id}' \ AND UserName = '%{SQL-User-Name}' \ AND NASIPAddress = '%{NAS-IP-Address}'"
Соответвенно, следует дополнить radacct нужными полями и исправить запросы.
SQL - запросы
Таблица radacct (добавлено 2 поля EventTimestamp и CiscoServiceInfo)
mysql> describe radacct; +----------------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+--------------+------+-----+---------------------+----------------+ | RadAcctId | bigint(21) | NO | PRI | NULL | auto_increment | | AcctSessionId | varchar(32) | NO | MUL | | | | AcctUniqueId | varchar(32) | NO | MUL | | | | UserName | varchar(64) | NO | MUL | | | | Realm | varchar(64) | YES | | | | | NASIPAddress | varchar(15) | NO | MUL | | | | NASPortId | varchar(15) | YES | | NULL | | | NASPortType | varchar(32) | YES | | NULL | | | AcctStartTime | datetime | NO | MUL | 0000-00-00 00:00:00 | | | AcctStopTime | datetime | NO | MUL | 0000-00-00 00:00:00 | | | AcctUpdateTime | timestamp | NO | | CURRENT_TIMESTAMP | | | AcctSessionTime | int(12) | YES | | NULL | | | AcctAuthentic | varchar(32) | YES | | NULL | | | ConnectInfo_start | varchar(50) | YES | | NULL | | | ConnectInfo_stop | varchar(50) | YES | | NULL | | | AcctInputOctets | bigint(20) | YES | | NULL | | | AcctOutputOctets | bigint(20) | YES | | NULL | | | CalledStationId | varchar(50) | NO | | | | | CallingStationId | varchar(50) | NO | | | | | AcctTerminateCause | varchar(32) | NO | | | | | ServiceType | varchar(32) | YES | | NULL | | | FramedProtocol | varchar(32) | YES | | NULL | | | FramedIPAddress | varchar(15) | NO | MUL | | | | AcctStartDelay | int(12) | YES | | NULL | | | AcctStopDelay | int(12) | YES | | NULL | | | XAscendSessionSvrKey | varchar(10) | YES | | NULL | | | EventTimestamp | varchar(64) | NO | | | | | CiscoServiceInfo | varchar(255) | NO | | | | +----------------------+--------------+------+-----+---------------------+----------------+
Запросы связанные с аккаунтингом (уже с изменениями)
accounting_update_query = " \ UPDATE ${acct_table1} \ SET \ FramedIPAddress = '%{Framed-IP-Address}', \ AcctSessionTime = '%{Acct-Session-Time}', \ AcctInputOctets = '%{Acct-Input-Gigawords:-0}' << 32 | '%{Acct-Input-Octets:-0}', \ AcctOutputOctets = '%{Acct-Output-Gigawords:-0}' << 32 | '%{Acct-Output-Octets:-0}', \ EventTimestamp = '%{Event-Timestamp}', \ CiscoServiceInfo = '%{Cisco-Service-Info}' \ WHERE \ AcctSessionId = '%{Acct-Session-Id}' \ AND UserName = '%{SQL-User-Name}' \ AND NASIPAddress = '%{NAS-IP-Address}'"
accounting_start_query = " \ INSERT INTO ${acct_table1} \ ( \ AcctSessionId, \ AcctUniqueId, \ UserName, \ Realm, \ NASIPAddress, \ NASPortId, \ NASPortType, \ AcctStartTime, \ AcctStopTime, \ AcctSessionTime, \ AcctAuthentic, \ ConnectInfo_start, \ ConnectInfo_stop, \ AcctInputOctets, \ AcctOutputOctets, \ CalledStationId, \ CallingStationId, \ AcctTerminateCause, \ ServiceType, \ FramedProtocol, \ FramedIPAddress, \ AcctStartDelay, \ AcctStopDelay, \ XAscendSessionSvrKey, \ EventTimestamp , \ CiscoServiceInfo \ ) \ VALUES \ ( \ '%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', \ '%{NAS-IP-Address}', \ '%{NAS-Port}', \ '%{NAS-Port-Type}', \ '%S', \ '0', \ '0', \ '%{Acct-Authentic}', \ '%{Connect-Info}', \ '', \ '0', \ '0', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', '', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ '%{Framed-IP-Address}', \ '%{Acct-Delay-Time:-0}', \ '0', \ '%{X-Ascend-Session-Svr-Key}', \ '%{Event-Timestamp}', \ '%{Cisco-Service-Info}' \ )"