dynamically defining name of radacct and radpostauth table names

[Rob Stonham]

Alex,

I've attached a SQL file to create a table (radacct) and a stored procedure to create the next 4 months of partitions and delete any partitions that are older than your retention period.

Yours

Rob


From: freeradius-users-bounces+r.stonham=hull.ac.uk at lists.freeradius.org [mailto:freeradius-users-bounces+r.stonham=hull.ac.uk at lists.freeradius.org] On Behalf Of Alex Sharaz
Sent: 23 September 2014 16:12
To: FreeRadius users mailing list
Subject: Re: dynamically defining name of radacct and radpostauth table names

Hi Rob,
sounds good, send us the SPs
Rgds
A
==========
Time for another Macmillan Cancer Support event. This time its the Indian Himalaya's Cycle Challenge 2014

Please sponsor me at http://www.justgiving.com/Alex-Sharaz



On 23 Sep 2014, at 15:41, Rob Stonham wrote:


Alex,

Take a look in the file modules/detail.  It gives some examples of expanding variables to split files in separate month/day log files.  For example you could write your SQL statement like this:
"insert into radacc_%{m}_%{Y} (acctsessionid, acc........"

This would mean that that you'd need to create 12 tables for radacct each year and 12 tables for radpostauth.  If you're using MySQL though I'd suggest that you look at partitioning the tables.  I can send you some MySQL stored procedures to manage partitioned tables if you'd like.

Rob


-----Original Message-----
From: freeradius-users-bounces+r.stonham=hull.ac.uk at lists.freeradius.org<mailto:freeradius-users-bounces+r.stonham=hull.ac.uk at lists.freeradius.org> [mailto:freeradius-users-bounces+r.stonham=hull.ac.uk at lists.freeradius.org<mailto:hull.ac.uk at lists.freeradius.org>] On Behalf Of Alex Sharaz
Sent: 23 September 2014 15:32
To: FreeRadius users mailing list
Subject: dynamically defining name of radacct and radpostauth table names

Hi,

For quite some time now i've been using the sql_log module to write SQL statements into a file on each of my RADIUS servers which are processed by rad-sqlrelay. It works well but due to the number of records produced I do need to do some form of mysql table management. Rather than have 2 huge tables (radacct and radpostauth) I'd like to start using monthly tables. Would it be possible to dynamically generate a table name of the form radacct + "_" + "mth" + "yr"  e.g. radacct_sep14 or radacct_1014 from within the sql_logs so that when I generate my sql statements they'll be inserted into the appropriate "month" table?

Rgds
Alex

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html**************************************************
To view the terms under which this email is
distributed, please go to
http://www2.hull.ac.uk/legal/disclaimer.aspx
**************************************************-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20140923/a81046b4/attachment.html>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: freeradius-radacct.txt
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20140923/a81046b4/attachment.txt>
-------------- next part --------------
**************************************************
To view the terms under which this email is 
distributed, please go to 
http://www2.hull.ac.uk/legal/disclaimer.aspx
**************************************************