On 23 Sep 2014, at 15:40, Alan DeKok wrote:

Alex Sharaz wrote:

...

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?

Yes. That's why the queries can be edited.

http://wiki.freeradius.org/config/Run%20time%20variables

Use %m to get the month of the request. Use it in the table name, and it will be dynamically expanded.

o.k. many thanks

Be sure to create the table for the next month BEFORE the month starts, otherwise the server won't be able to write to the table.

Already done that Rgds Alex

Alan DeKok.

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

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@lists.freeradius.org [mailto:freeradius-users-bounces+r.stonham=hull.ac.uk@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