Accounting and Acct-Delay-Time in MySQL

Phil Mayers p.mayers at
Thu Dec 2 12:20:19 CET 2010

On 18/11/10 07:58, Stefan Winter wrote:
>    Hi,
>>     I'd re-visit the entire accounting table&&   queries.  Create a *new*
>> table, so that people don't have surprises when they upgrade.
>>     Ideally, it should be robust in the face of duplicate packets, and
>> packets forwarded via 2 different paths (think radrelay + delays)
> Okay, I'll see what I can do. One thing I noticed is that the default
> schema has a column
> xascendsessionsvrkey varchar(10) default NULL,
> A VSA, of a vendor that's long dead? This is one column that I would
> wipe out. If some people find they need it, they can always modify the
> tables to their (peculiar ;-) ) needs. No reason to push this column
> into every FreeRADIUS installation on the planet.
> Another thing I miss very much is in radpostauth:
> * some gear sends a different User-Name attribute in its reply than was
> in the request. It would be good to have these two names correlated
> easily, at least for forensics. Adding a column "reply-username" would
> do a lot of good here.
> * callingstationid would also be nice to have
> * and an indication which NAS the user used to log in (and/or which
> virtual server was used to handle the request)
> All of that is info one typically has to dig out of detail files; which
> is much more cumbersome than having it in SQL.
> Any thoughts here?

I've made some pretty extensive modifications to the default SQL schemas 
here (although we use postgresql).

We log:

CREATE TABLE radpostauth (
     id serial,
     authdate timestamptz,
     authserver character varying(16),
     virtualserver text,

     reply text,

     username text NOT NULL,
     realm text,

     callingstationid text,
     framedipaddress inet,
     nasipaddress inet,
     nasport text,

     replyclass text,
     replymessage text

...and we use something like the following in radiusd.conf:

localopts {
   hostname = "thehostname"

sql {
   postauth_query = "insert into radpostauth (
    authdate, authserver, virtualserver,
    username, realm,
    callingstationid, framedipaddress,
    nasipaddress, nasport,
  ) values (
    now(), '${localopts.hostname}', '%{Virtual-Server}',
    '%{SQL-User-Name}', '%{Realm}',
    '%{Calling-Station-Id}', '%{reply:Framed-IP-Address}',
    '%{NAS-IP-Address}', '%{%{NAS-Port}:-%{NAS-Port-Id}}',
  )'s actually a bit more complex than that, but you get the idea.

More information about the Freeradius-Users mailing list