ISG-Accounting

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 нужными полями и исправить запросы.