Use Event-Timestamp for Accounting Start/Stop with MySQL
Hi I would like to write the Event-Timestamp from Accounting Start/Stop messages to my MySQL database instead of the server side time. I tried to configure the dialup.conf, but it doesn't work properly. I replaced %S with %{Event-Timestamp}, the result is "0000-00-00 00:00:00" in the database, the log file says : expand: UPDATE radacct SET acctstoptime = '%{Event-Timestamp}', ** snip ** -> UPDATE radacct SET acctstoptime = 'Dec 21 2010 10:02:30 CET' ** snip ** When I use something like DATE_FORMAT(date,format) from MySQL, the format string is somehow expanded. FROM_UNIXTIME isn't working either. I understand that unix timestamps are printed as strings like 'Dec 21 2010 10:02:30 CET', but only for logging or for the sql statements too? Thank you for your help. Btw.: FreeRADIUS Version 2.0.4 Best Juri
Am 20.12.2010 um 14:33 schrieb Juri Glaß:
I would like to write the Event-Timestamp from Accounting Start/Stop messages to my MySQL database instead of the server side time.
I tried to configure the dialup.conf, but it doesn't work properly.
I replaced %S with %{Event-Timestamp}, the result is "0000-00-00 00:00:00" in the database, the log file says : expand: UPDATE radacct SET acctstoptime = '%{Event-Timestamp}', ** snip ** -> UPDATE radacct SET acctstoptime = 'Dec 21 2010 10:02:30 CET' ** snip **
When I use something like DATE_FORMAT(date,format) from MySQL, the format string is somehow expanded. FROM_UNIXTIME isn't working either.
I understand that unix timestamps are printed as strings like 'Dec 21 2010 10:02:30 CET', but only for logging or for the sql statements too?
I solved this by changing the strftime format string in print.c If someone has a better solution, I would appreciate an answer.
Juri Glaß wrote:
I would like to write the Event-Timestamp from Accounting Start/Stop messages to my MySQL database instead of the server side time.
I tried to configure the dialup.conf, but it doesn't work properly.
I replaced %S with %{Event-Timestamp}, the result is "0000-00-00 00:00:00" in the database, the log file says : expand: UPDATE radacct SET acctstoptime = '%{Event-Timestamp}', ** snip ** -> UPDATE radacct SET acctstoptime = 'Dec 21 2010 10:02:30 CET' ** snip **
i.e. the Event-Timestamp is not in an SQL format. That's why the %S variable exists.
When I use something like DATE_FORMAT(date,format) from MySQL, the format string is somehow expanded. FROM_UNIXTIME isn't working either.
I understand that unix timestamps are printed as strings like 'Dec 21 2010 10:02:30 CET', but only for logging or for the sql statements too?
For everything, unfortunately. They cannot currently be printed as 32-bit integers. Maybe in 2.1.11. Alan DeKok.
On Tue, Dec 21, 2010 at 11:26 AM, Alan DeKok <aland@deployingradius.com> wrote:
Juri Glaß wrote:
I would like to write the Event-Timestamp from Accounting Start/Stop messages to my MySQL database instead of the server side time.
I tried to configure the dialup.conf, but it doesn't work properly.
I replaced %S with %{Event-Timestamp}, the result is "0000-00-00 00:00:00" in the database, the log file says : expand: UPDATE radacct SET acctstoptime = '%{Event-Timestamp}', ** snip ** -> UPDATE radacct SET acctstoptime = 'Dec 21 2010 10:02:30 CET' ** snip **
i.e. the Event-Timestamp is not in an SQL format. That's why the %S variable exists.
When I use something like DATE_FORMAT(date,format) from MySQL, the format string is somehow expanded. FROM_UNIXTIME isn't working either.
I understand that unix timestamps are printed as strings like 'Dec 21 2010 10:02:30 CET', but only for logging or for the sql statements too?
For everything, unfortunately. They cannot currently be printed as 32-bit integers. Maybe in 2.1.11.
2.1.10 allows you to use "{%Event-Timestamp#}" to get date type attributes printed in numeric format. It doesn't seem to be documented, but its in the code. Eddie
Eddie Stassen wrote:
2.1.10 allows you to use "{%Event-Timestamp#}" to get date type attributes printed in numeric format. It doesn't seem to be documented, but its in the code.
$ man unlang It's there. There's enough stuff in the server that I'm starting to forget what it can do. Alan DeKok.
On Tue, Dec 21, 2010 at 3:28 PM, Alan DeKok <aland@deployingradius.com> wrote:
Eddie Stassen wrote:
2.1.10 allows you to use "{%Event-Timestamp#}" to get date type attributes printed in numeric format. It doesn't seem to be documented, but its in the code.
$ man unlang
It's there.
Thanks, I was looking at the web man page at http://freeradius.org/radiusd/man/unlang.html, which I now notice is not quite up to date.
There's enough stuff in the server that I'm starting to forget what it can do.
Thats one of the best parts of programming - looking over your old code and finding all the awesome stuff you did and already forgot about ;-)
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
My 2 cents... Might not be the best way but for now it works... in the DDL schema.sql evttime_utcepstop varchar(30) NULL default NULL evttime_utcepstop (Would be our column in the Mysql DB to store the Event-Time-Stamp Attr Radius '55') I don't need it in the start so I added in the INSERT in the "accounting_start_query" conf file dialup.conf: but the value is "" I was more interested in the stop. So.. In the "accounting_stop_query" I changed the update and included evttime_utcepstop = '%{Event-Timestamp}' \ Yes it is a string for example "Dec 7 2010 00:14:54 UTC" ( my server locale is UTC) then in a very straight forward sql you can do something like: select unix_timestamp(str_to_date(evttime_utcepstop, "%b %d %Y %T")); That way you will get back your EPOCH. I thought in the possibility of convert it before at the time of the update but I rather to not spend the time in the realtime and instead in the post processing. Hope it helps Ramon ----- Original Message ---- From: Eddie Stassen <estassen@gmail.com> To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Sent: Tue, December 21, 2010 2:58:53 PM Subject: Re: Use Event-Timestamp for Accounting Start/Stop with MySQL On Tue, Dec 21, 2010 at 3:28 PM, Alan DeKok <aland@deployingradius.com> wrote:
Eddie Stassen wrote:
2.1.10 allows you to use "{%Event-Timestamp#}" to get date type attributes printed in numeric format. It doesn't seem to be documented, but its in the code.
$ man unlang
It's there.
Thanks, I was looking at the web man page at http://freeradius.org/radiusd/man/unlang.html, which I now notice is not quite up to date.
There's enough stuff in the server that I'm starting to forget what it can do.
Thats one of the best parts of programming - looking over your old code and finding all the awesome stuff you did and already forgot about ;-)
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (4)
-
Alan DeKok -
Eddie Stassen -
Juri Glaß -
Ramon J. Castillo