ISG-Accounting: различия между версиями
Материал из noname.com.ua
Перейти к навигацииПерейти к поискуSirmax (обсуждение | вклад) |
Sirmax (обсуждение | вклад) |
||
Строка 114: | Строка 114: | ||
==SQL - запросы== |
==SQL - запросы== |
||
+ | Таблица radacct (добавлено 2 поля EventTimestamp и CiscoServiceInfo) |
||
+ | <PRE>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 | | | | |
||
+ | +----------------------+--------------+------+-----+---------------------+----------------+ |
||
+ | </PRE> |
||
+ | |||
+ | Запросы связанные с аккаунтингом (уже с изменениями) |
||
+ | |||
+ | <PRE> |
||
+ | |||
+ | |||
+ | 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}'" |
||
+ | </PRE> |
||
+ | |||
+ | <PRE> |
||
+ | 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}' \ |
||
+ | )" |
||
+ | </PRE> |
Версия 16:25, 16 января 2009
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}' \ )"