Accounting and Acct-Delay-Time in MySQL

[Phil Mayers]

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,
    reply,
    username, realm,
    callingstationid, framedipaddress,
    nasipaddress, nasport,
    replyclass,
    replymessage
  ) values (
    now(), '${localopts.hostname}', '%{Virtual-Server}',
    '%{reply:Packet-Type}',
    '%{SQL-User-Name}', '%{Realm}',
    '%{Calling-Station-Id}', '%{reply:Framed-IP-Address}',
    '%{NAS-IP-Address}', '%{%{NAS-Port}:-%{NAS-Port-Id}}',
    '%{reply:Class}',
    '%{reply:Reply-Message}'
  )

...it's actually a bit more complex than that, but you get the idea.