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