<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.apple-style-span
{mso-style-name:apple-style-span;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle19
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D;mso-fareast-language:EN-US">Alex,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D;mso-fareast-language:EN-US">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D;mso-fareast-language:EN-US">Yours<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D;mso-fareast-language:EN-US">Rob<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif""> freeradius-users-bounces+r.stonham=hull.ac.uk@lists.freeradius.org
[mailto:freeradius-users-bounces+r.stonham=hull.ac.uk@lists.freeradius.org] <b>On Behalf Of
</b>Alex Sharaz<br>
<b>Sent:</b> 23 September 2014 16:12<br>
<b>To:</b> FreeRadius users mailing list<br>
<b>Subject:</b> Re: dynamically defining name of radacct and radpostauth table names<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Hi Rob,<o:p></o:p></p>
<div>
<p class="MsoNormal">sounds good, send us the SPs<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Rgds<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">A<o:p></o:p></p>
</div>
<div>
<div>
<div>
<p class="MsoNormal"><span style="font-size:13.5pt;font-family:"Helvetica","sans-serif";color:black">==========<br>
Time for another Macmillan Cancer Support event. This time its the Indian Himalaya's Cycle Challenge 2014<br>
<br>
Please sponsor me at <a href="http://www.justgiving.com/Alex-Sharaz">http://www.justgiving.com/Alex-Sharaz</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:13.5pt;font-family:"Helvetica","sans-serif";color:black"><o:p> </o:p></span></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On 23 Sep 2014, at 15:41, Rob Stonham wrote:<o:p></o:p></p>
</div>
<p class="MsoNormal"><br>
<br>
<o:p></o:p></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<p class="MsoNormal">Alex,<br>
<br>
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:<br>
"insert into radacc_%{m}_%{Y} (acctsessionid, acc........"<br>
<br>
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.<br>
<br>
Rob<br>
<br>
<br>
-----Original Message-----<br>
From: <a href="mailto:freeradius-users-bounces+r.stonham=hull.ac.uk@lists.freeradius.org">
freeradius-users-bounces+r.stonham=hull.ac.uk@lists.freeradius.org</a> [mailto:freeradius-users-bounces+r.stonham=<a href="mailto:hull.ac.uk@lists.freeradius.org">hull.ac.uk@lists.freeradius.org</a>] On Behalf Of Alex Sharaz<br>
Sent: 23 September 2014 15:32<br>
To: FreeRadius users mailing list<br>
Subject: dynamically defining name of radacct and radpostauth table names<br>
<br>
Hi,<br>
<br>
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?<br>
<br>
Rgds<br>
Alex<br>
<br>
-<br>
List info/subscribe/unsubscribe? See <a href="http://www.freeradius.org/list/users.html**************************************************">
http://www.freeradius.org/list/users.html**************************************************</a><br>
To view the terms under which this email is <br>
distributed, please go to <br>
<a href="http://www2.hull.ac.uk/legal/disclaimer.aspx">http://www2.hull.ac.uk/legal/disclaimer.aspx</a><br>
**************************************************-<br>
List info/subscribe/unsubscribe? See <a href="http://www.freeradius.org/list/users.html">
http://www.freeradius.org/list/users.html</a><o:p></o:p></p>
</div>
</blockquote>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</body>
</html>