Write 3GPP attributes accounting into mysql problem

bit1 bit1 at freemail.hu
Mon Jan 13 16:51:23 CET 2014


Hi 
Sorry for the less info. 

This is the test method.

#cat /usr/local/etc/raddb/sql/mysql/dialup.conf | grep -e request -e test -e fullpacket
             acctstartdelay,   xascendsessionsvrkey,  test,fullpacket) \
             '0','0','%{request:3GPP-IMSI}','%Z')"
             acctstartdelay,   acctstopdelay,    xascendsessionsvrkey, test,fullpacket) \
             '%{%{Acct-Delay-Time}:-0}', '0', '0','%{request:3GPP-IMSI}','%Z')"
#
#
#
#
#
#cat /tmp/auth.packet
Acct-Status-Type = Start
Calling-Station-Id = "12312312312"
Framed-IP-Address = 10.0.0.1
Acct-Session-Id = "d43363551081e4a8"
NAS-IP-Address = 10.11.11.77
Framed-Protocol = GPRS-PDP-Context
Called-Station-Id = "test-gprs"
NAS-Identifier = "test-gprs"
User-Name = "testuser"
NAS-Port-Type = Virtual
Service-Type = Framed-User
3GPP-IMSI = "123123123123123"
#radclient -f /tmp/auth.packet 127.0.0.1:2813 acct testing123
Received response ID 114, code 5, length = 20
#mysql -e "select * from radacct\G" radius
*************************** 1. row ***************************
           radacctid: 228
       acctsessionid: d43363551081e4a8
        acctuniqueid: 6ce7054b4a610197
            username: testuser
           groupname:
               realm:
        nasipaddress: 10.11.11.77
           nasportid:
         nasporttype: Virtual
       acctstarttime: 2014-01-13 16:34:14
        acctstoptime: NULL
     acctsessiontime: 0
       acctauthentic:
   connectinfo_start:
    connectinfo_stop:
     acctinputoctets: 0
    acctoutputoctets: 0
     calledstationid: test-gprs
    callingstationid: 12312312312
  acctterminatecause:
         servicetype: Framed-User
      framedprotocol: GPRS-PDP-Context
     framedipaddress: 10.0.0.1
      acctstartdelay: 0
       acctstopdelay: 0
xascendsessionsvrkey: 0
                test:
          fullpacket:   Acct-Status-Type = Start
        Calling-Station-Id = "12312312312"
        Framed-IP-Address = 10.0.0.1
        Acct-Session-Id = "d43363551081e4a8"
        NAS-IP-Address = 10.11.11.77
        Framed-Protocol = GPRS-PDP-Context
        Called-Station-Id = "test-gprs"
        NAS-Identifier = "test-gprs"
        User-Name = "testuser"
        NAS-Port-Type = Virtual
        Service-Type = Framed-User
        3GPP-IMSI = "123123123123123"
        Acct-Unique-Session-Id = "6ce7054b4a610197"
        SQL-User-Name = "testuser"

#



Here is the debug output.
Mon Jan 13 16:34:14 2014 : Info: # Executing section preacct from file /usr/local/etc/raddb/sites-enabled/default
Mon Jan 13 16:34:14 2014 : Info: +- entering group preacct {...}
Mon Jan 13 16:34:14 2014 : Info: ++[preprocess] returns ok
Mon Jan 13 16:34:14 2014 : Info: [acct_unique] WARNING: Attribute NAS-Port was not found in request, unique ID MAY be inconsistent
Mon Jan 13 16:34:14 2014 : Info: [acct_unique] Hashing ',NAS-Identifier = "test-gprs",NAS-IP-Address = 10.11.11.77,Acct-Session-Id = "d43363551081e4a8",User-Name = "testuser"'
Mon Jan 13 16:34:14 2014 : Info: [acct_unique] Acct-Unique-Session-ID = "6ce7054b4a610197".
Mon Jan 13 16:34:14 2014 : Info: ++[acct_unique] returns ok
Mon Jan 13 16:34:14 2014 : Info: [suffix] No '@' in User-Name = "testuser", looking up realm NULL
Mon Jan 13 16:34:14 2014 : Info: [suffix] No such realm "NULL"
Mon Jan 13 16:34:14 2014 : Info: ++[suffix] returns noop
Mon Jan 13 16:34:14 2014 : Info: # Executing section accounting from file /usr/local/etc/raddb/sites-enabled/default
Mon Jan 13 16:34:14 2014 : Info: +- entering group accounting {...}
Mon Jan 13 16:34:14 2014 : Info: [detail]       expand: %{Packet-Src-IP-Address} -> 127.0.0.1
Mon Jan 13 16:34:14 2014 : Info: [detail]       expand: /var/log/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d -> /var/log/radacct/127.0.0.1/detail-20140113
Mon Jan 13 16:34:14 2014 : Info: [detail] /var/log/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/radacct/127.0.0.1/detail-20140113
Mon Jan 13 16:34:14 2014 : Info: [detail]       expand: %t -> Mon Jan 13 16:34:14 2014
Mon Jan 13 16:34:14 2014 : Info: ++[detail] returns ok
Mon Jan 13 16:34:14 2014 : Info: [sql]  expand: %{User-Name} -> testuser
Mon Jan 13 16:34:14 2014 : Info: [sql] sql_set_user escaped user --> 'testuser'
Mon Jan 13 16:34:14 2014 : Info: [sql]  expand: %{Acct-Delay-Time} ->
Mon Jan 13 16:34:14 2014 : Info: [sql]  ... expanding second conditional
Mon Jan 13 16:34:14 2014 : Info: [sql]  expand:            INSERT INTO radacct             (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, test,fullpacket)           VALUES             ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',              '%{SQL-User-Name}',              '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',              '%{NAS-Port-Type}', '%S', NULL,              '0', '%{Acct-Authentic}', '%{Connect-Info}',              '', '0', '0',              '%{Called-Station-Id}', '%{Calling-Station-Id}', '',              '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP
Mon Jan 13 16:34:14 2014 : Debug: rlm_sql (sql): Reserving sql socket id: 3
Mon Jan 13 16:34:14 2014 : Debug: rlm_sql (sql): Released sql socket id: 3
Mon Jan 13 16:34:14 2014 : Info: ++[sql] returns ok
Mon Jan 13 16:34:14 2014 : Info: ++[exec] returns noop
Mon Jan 13 16:34:14 2014 : Info: [attr_filter.accounting_response]      expand: %{User-Name} -> testuser
Mon Jan 13 16:34:14 2014 : Debug: attr_filter: Matched entry DEFAULT at line 12
Mon Jan 13 16:34:14 2014 : Info: ++[attr_filter.accounting_response] returns updated
Sending Accounting-Response of id 114 to 127.0.0.1 port 14127
Mon Jan 13 16:34:14 2014 : Info: Finished request 0.
Mon Jan 13 16:34:14 2014 : Info: Cleaning up request 0 ID 114 with timestamp +183
Mon Jan 13 16:34:14 2014 : Debug: Going to the next request
Mon Jan 13 16:34:14 2014 : Info: Ready to process requests.

Unfortunately the end of sql insert slided out.

Best Regards
Dohány Tibor
mailto:bit1 at freemail.hu

Alan DeKok <aland at deployingradius.com> írta:
>bit1 wrote:
>> But the '%{request:3GPP-IMSI}' does not work, it is empty, however the '%Z' countains the full accounting packet. It seems the 3GPP-* runtime variable does not get value or may be the format is wrong.
>
>  (a) run the server in debugging mode to see what's going on
>
>  (b) see the FAQ about accounting data.
>
>  This isn't magic.  If there's no 3GPP-IMSI in the request, then the
>server can't insert it into the database.
>
>> I'v made a not so elegant solution with mysql-udf-preg and regexped the necessary attribs from full packet , but i think it could be too slow with more attribs/inserts. 
>> Do you have any idea how to solve this issue?
>
>  You've been very careful to post as little information as possible.
>i.e. not the debug output as suggested in the FAQ, "man" page, web
>pages, Wiki, and daily on this list.
>
>  So... no.  I have no idea how to solve this issue.
>
>  Alan DeKok.
>-
>List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
 


More information about the Freeradius-Users mailing list