Re: Accounting and Acct-Delay-Time in MySQL
Stefan Winter wrote: 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. Yup. 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. reply-username ? Or accounting-request ? * 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? It sounds good to me. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting and Acct-Delay-Time in MySQL
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.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting and Acct-Delay-Time in MySQL
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? Greetings, Stefan Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting and Acct-Delay-Time in MySQL
Stefan Winter wrote: the default queries for mysql log Acct-Delay-Time into the columns acctstartdelay and acctstopdelay, respectively. They leave the timestamps for acctstarttime and acctstoptime at %S. For a non-zero delay, this means that a database reader needs to do math to get the start and stop times. Yes. There have been discussions about fixing that, and other accounting issues. It is rather unintuitive that a database user needs to calculate the *actual* event times manually by substracting the values. This is something that MySQL can easily do on its own at INSERT or UPDATE. Yes. Is there a specific reason why the two are kept separate? If not, I'll merrily volunteer to update the default query set to do so; I'll do this for my deployment's custom queries anyway. This would also make the two columns for delay time obsolete. Any thoughts on this? 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) Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Accounting and Acct-Delay-Time in MySQL
Hello, the default queries for mysql log Acct-Delay-Time into the columns acctstartdelay and acctstopdelay, respectively. They leave the timestamps for acctstarttime and acctstoptime at %S. For a non-zero delay, this means that a database reader needs to do math to get the start and stop times. It is rather unintuitive that a database user needs to calculate the *actual* event times manually by substracting the values. This is something that MySQL can easily do on its own at INSERT or UPDATE. Is there a specific reason why the two are kept separate? If not, I'll merrily volunteer to update the default query set to do so; I'll do this for my deployment's custom queries anyway. This would also make the two columns for delay time obsolete. Any thoughts on this? Greetings, Stefan Winter -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
