Re: Accounting and Acct-Delay-Time in MySQL

2 Dec 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,
    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.

Re: Accounting and Acct-Delay-Time in MySQL

Phil Mayers