Gigaword support

[Peter Nixon]

On Tue 29 May 2007, Stefan Winter wrote:
> Hi,
>
> > Have a look at this:
> >
> > http://www.netexpertise.eu/en/FreeRadius/GigaWords.html
>
> 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