ISG-Accounting
Материал из noname.com.ua
Версия от 11:11, 24 июня 2010; Sirmax (обсуждение | вклад)
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 = "router.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 = "router.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 = "router.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}' \
)"
Сервисы
Каждый сервис для которого планируется вести учет траффика должен иметь в своем описании следующие атрибуты:
Cisco-AVpair += "subscriber:accounting-list=LIST-NAME" Acct-Interim-Interval = 60
LIST-NAME - предварительно сконфигурирован на роутере.
В моем случае это
Cisco-AVpair += "subscriber:accounting-list=ISG-AUTH-1"
! aaa group server radius ISG-RADIUS server 172.16.30.144 auth-port 1812 acct-port 1813 ! ... ! aaa accounting update periodic 1 jitter maximum 0 aaa accounting network ISG-AUTH-1 start-stop group ISG-RADIUS !
Acct-Interim-Interval не может быть менее 60 сек. (больше -может быть).
Соответвенно сервис (например) KH-IX приобретает вид
KH-IX_5_MBIT Password == "cisco"
Cisco-AVPair += "ip:traffic-class=in access-group 194 priority 8",
Cisco-AVPair += "ip:traffic-class=out access-group 194 priority 8",
Cisco-AVpair += "subscriber:accounting-list=ISG-AUTH-1",
Acct-Interim-Interval = 60,
Cisco-Service-Info += "QU;5120000;2048000;D;5120000;2048000"
Результат учета
mysql> select AcctInputOctets,AcctOutputOctets,CiscoServiceInfo from radacct where UserName='123.123.244.195' and AcctStopTime>0; +-----------------+------------------+------------------+ | AcctInputOctets | AcctOutputOctets | CiscoServiceInfo | +-----------------+------------------+------------------+ | 200088 | 196850 | NWORLD_1_MBIT | | 761253 | 11253195 | NUA-IX_2_MBIT | | 0 | 0 | NKH-IX_5_MBIT | +-----------------+------------------+------------------+ 3 rows in set (0.00 sec)
