Hi all, Running FreeRADIUS Version 2.1.10. I have a happily working FreeRADIUS + mySQL backend setup, however in the radacct table it seems FreeRADIUS just dumps fields in the order of what it has. For example I store nastxspeed and nasrxspeed and disccauseext. Some sessions end and store the tx speed, rx speed and cause in the correct field, but other sessions I end up with the remote-id in the tx speed field, then tx speed in the rx field, rx speed in the disccauseext field. It seems as if the RADIUS server completely ignores the field names, so if it has a missing attribute on a session or an additional attribute, the entire accounting row moves left or right and continues on rather than leaving a blank field if it didn¹t have an attribute. Is this something I can fix by getting FreeRADIUS to somehow explicitly put each attribute in the appropriate field? -Scott ________________________________ The content of this message and any attachments may be privileged, confidential or sensitive. Any unauthorised used is prohibited. Views expressed in this message are those of the individual sender, except where stated otherwise with appropriate authority. All pricing provided is valid at the time of writing only and due to factors such as the exchange rate, may change without notice. Sales are made subject to our Terms & Conditions, available on our website or on request. ________________________________
On Sun, Oct 20, 2013 at 10:19 AM, Scott Pettit - Vorco <SPettit@vorco.net>wrote:
Hi all,
Running FreeRADIUS Version 2.1.10.
I have a happily working FreeRADIUS + mySQL backend setup, however in the radacct table it seems FreeRADIUS just dumps fields in the order of what it has.
The default setup doesn't do that
For example I store nastxspeed and nasrxspeed and disccauseext.
It's not in the default setup. Someone modified that.
Some sessions end and store the tx speed, rx speed and cause in the correct field, but other sessions I end up with the remote-id in the tx speed field, then tx speed in the rx field, rx speed in the disccauseext field.
Someone modified both the schemas and queries, and they messed up your config. A correct sql query (e.g. in sql/mysql/dialup.conf) would look like this (taken from the default) accounting_stop_query_alt = " \ INSERT INTO ${acct_table2} \ (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) \ VALUES \ ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \ '%{SQL-User-Name}', \ '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \ '%{NAS-Port-Type}', \ DATE_SUB('%S', \ INTERVAL (%{%{Acct-Session-Time}:-0} + \ %{%{Acct-Delay-Time}:-0}) SECOND), \ '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', \ '%{Connect-Info}', \ '%{%{Acct-Input-Gigawords}:-0}' << 32 | \ '%{%{Acct-Input-Octets}:-0}', \ '%{%{Acct-Output-Gigawords}:-0}' << 32 | \ '%{%{Acct-Output-Octets}:-0}', \ '%{Called-Station-Id}', '%{Calling-Station-Id}', \ '%{Acct-Terminate-Cause}', \ '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \ '0', '%{%{Acct-Delay-Time}:-0}')" What does your queries look like? For example, is it possible that you ommit field names in the queries? (e.g INSERT INTO ${acct_table2} VALUES ...) Is this something I can fix by getting FreeRADIUS to somehow explicitly
put each attribute in the appropriate field?
Get someone who understands sql to write the queries. -- Fajar
Thanks. Yes someone else did build these configs and that snippet has got me to the right place. FWIW here is what was wrong: UPDATE ${acct_table2} SET \ acctstoptime = '%S', \ acctsessiontime = '%{Acct-Session-Time}', \ acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \ '%{%{Acct-Input-Octets}:-0}', \ acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \ '%{%{Acct-Output-Octets}:-0}', \ acctterminatecause = '%{Acct-Terminate-Cause}', \ acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \ connectinfo_stop = '%{Connect-Info}', \ nastxspeed = '%{Cisco-AVPair[1]}', \ nasrxspeed = '%{Cisco-AVPair[2]}', \ disccauseext = '%{Cisco-AVPair[3]}', \ ciscoavpairother1 = '%{Cisco-AVPair[4]}', \ ciscoavpairother2 = '%{Cisco-AVPair[0]' \ WHERE acctsessionid = '%{Acct-Session-Id}' \ AND username = '%{SQL-User-Name}' \ AND nasipaddress = '%{NAS-IP-Address}’" The Cisco-AVPair[0] wasn’t closed off with } so the query was inputting junk. It also appears that those Cisco-AVPair attributes would be better handled with rewrites as the contents of those attributes can change around depending on what the BRAS sends for that user. -Scott ________________________________ The content of this message and any attachments may be privileged, confidential or sensitive. Any unauthorised used is prohibited. Views expressed in this message are those of the individual sender, except where stated otherwise with appropriate authority. All pricing provided is valid at the time of writing only and due to factors such as the exchange rate, may change without notice. Sales are made subject to our Terms & Conditions, available on our website or on request. ________________________________
On 20 Oct 2013, at 06:46, Scott Pettit - Vorco <SPettit@vorco.net> wrote:
Thanks. Yes someone else did build these configs and that snippet has got me to the right place.
FWIW here is what was wrong:
UPDATE ${acct_table2} SET \ acctstoptime = '%S', \ acctsessiontime = '%{Acct-Session-Time}', \ acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \ '%{%{Acct-Input-Octets}:-0}', \ acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \ '%{%{Acct-Output-Octets}:-0}', \ acctterminatecause = '%{Acct-Terminate-Cause}', \ acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \ connectinfo_stop = '%{Connect-Info}', \ nastxspeed = '%{Cisco-AVPair[1]}', \ nasrxspeed = '%{Cisco-AVPair[2]}', \ disccauseext = '%{Cisco-AVPair[3]}', \ ciscoavpairother1 = '%{Cisco-AVPair[4]}', \ ciscoavpairother2 = '%{Cisco-AVPair[0]' \ WHERE acctsessionid = '%{Acct-Session-Id}' \ AND username = '%{SQL-User-Name}' \ AND nasipaddress = '%{NAS-IP-Address}’"
The Cisco-AVPair[0] wasn’t closed off with } so the query was inputting junk.
It also appears that those Cisco-AVPair attributes would be better handled with rewrites as the contents of those attributes can change around depending on what the BRAS sends for that user.
Just to say, using fixed indexes of Cisco-AVPair is not a robust way or writing a policy. Firmware updates, or configuration changes may result in the ordering of the Cisco-AVPair to change, and your configuration will no longer function as expected. For 2.x.x you need to break out into something like perl, and assign the values to internal attributes (defined in raddb/dictionary). For 3.x.x you can use a foreach loop to do the assignment. -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team
participants (3)
-
Arran Cudbard-Bell -
Fajar A. Nugraha -
Scott Pettit - Vorco