On Tue 29 May 2007, Stefan Winter wrote:
Hi,
Have a look at this:
hm, I had a look at this HOWTO, and the FreeRADIUS bits appear a bit odd. He adds a new column to radacct, and fills the gigawords attribute into this seperate column. This does not give a coherent output: you are likely interested in one single number (i.e. the number of input octets), and not in doing binary arithmetic yourself on two seperate columns of the table.
I have a few simple suggestions:
- (mysql) modify Schema AcctInputOctets to be BIGINT(20) instead of BIGINT(12) [this will make the integer big enough to hold any 64-Bit value] - modify the default query in sql.conf to do the binary arithmetic (merging Gigaword << 32 | inputOctets) into a single number and storing that in radacct.
At least for mySQL, this kind of arithmetic in an update or insert query is rather simple:
replace
AcctInputOctets = '%{Acct-Input-Octets}',
with
AcctInputOctets = '%{Acct-Input-Gigawords}' << 32 | '%{Acct-Input-Octets},
and you're done: it gives us 64-bit counter handling for SQL backends with no pain (and Output-octets, Input-Packets, Output-Packets equivalently)
Erm. This has been part of the default Postgresql queries for eons. Can someone explain to me why the same doesn't "just work" with mysql. See: accounting_update_query = "UPDATE ${acct_table1} \ SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \ AcctSessionTime = '%{Acct-Session-Time}', \ AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \ AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint) \ WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \ AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL" Cheers -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc