<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:st1="urn:schemas-microsoft-com:office:smarttags" 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 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="PersonName"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:blue;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:595.3pt 841.9pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-GB link=blue vlink=blue>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Hi Leigh<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Your script is most useful.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>We have interim updates every 2 hours and
hadn’t implemented anything to close stale sessions.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>So now I’ve modified your script and
will run it every night to look for sessions where the AcctStatus = Alive and where
the updated column, that we have added to see when the last interim update was,
isn’t today’s date.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>I’ve taken onboard your suggestion
of setting Alive sessions to Stop and then working out the Octet difference
from the new session and the Admin-Rest session and I’ve tested it on a
test account we have. However after setting the session to Stop with an
Admin-Reset AcctStatus the new update indeed did put a new entry in radacct,
but there is no way to tie the forced Stop session with the new session. The
AcctSessionId and AcctUniqueId are different across the 2 sessions.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Any ideas?<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Tony </span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span lang=EN-US style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span lang=EN-US
style='font-size:10.0pt;font-family:Tahoma;font-weight:bold'>From:</span></font></b><font
size=2 face=Tahoma><span lang=EN-US style='font-size:10.0pt;font-family:Tahoma'>
freeradius-users-bounces+tony=tonyspencer.co.uk@lists.freeradius.org
[mailto:freeradius-users-bounces+tony=tonyspencer.co.uk@lists.freeradius.org] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Leigh Martell<br>
<b><span style='font-weight:bold'>Sent:</span></b> 20 November 2008 20:41<br>
<b><span style='font-weight:bold'>To:</span></b> <st1:PersonName w:st="on">FreeRadius
users mailing list</st1:PersonName><br>
<b><span style='font-weight:bold'>Subject:</span></b> Re: New entry for Interim
packet</span></font><span lang=EN-US><o:p></o:p></span></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>Hello Tony,<br>
If you set you interim update to 5 or 10min you could then run a script
at midnight to send accounting packets to finalize it in the MySQL DB with the
Acct-Terminate-Cause = Admin-Reset; so next interim packet radius will
automatically start a new one. Now here is where it can get tricky, radius will
not reset the counter in this case but you could write a script to fire at
accounting stop or monthly to subtract the previous months last accounting
packet with Termination cause Admin-Reset from the first accouting packet of
the current month...sorry if I am rambling but this will work without a huge
discrepency; please be careful and write your script conditions properly(ie:
don't subtract from an accounting packet that came on the first past 12:30am).<br>
<br>
Here is an example of a nightly close-session script I run to clean up stale
sessions.<br>
<br>
#!/usr/bin/perl<br>
use Authen::Radius;<br>
use DBI;<br>
use DateTime;<br>
use DateTime::Format::Strptime;<br>
getopen();<br>
<br>
sub connect_db {<br>
local($radius_name,$radius_user,$radius_passwd)
= @_;<br>
$connect_name = "connect to
db";<br>
$dbh_radius =
DBI->connect("DBI:mysql:$radius_name:localhost", $radius_user,
$radius_passwd);<br>
if(!$dbh_radius) {<br>
print "Error $connect_name: Cannot connect to $radius_name -
$dbh_radius->errstr<BR>\n";<br>
exit 2;<br>
}<br>
}<br>
<br>
sub disconnect_db {<br>
$dbh_radius->disconnect();<br>
return 1;<br>
}<br>
<br>
sub getopen {<br>
$radius_name = "radiusdb";<br>
$radius_user = "username";<br>
$radius_passwd =
"password";<br>
my $dtn = DateTime->now();<br>
$date = $dtn->ymd .
"%";<br>
&connect_db($radius_name,$radius_user,$radius_passwd);<br>
$qAccount = qq{SELECT * FROM radacct
WHERE AcctStopTime = \'0000-00-00 00:00:00\' AND AcctStartTime NOT LIKE
\'$date\'};<br>
$sth =
$dbh_radius->prepare($qAccount);<br>
$sth->execute() or warn
"ERROR IN EXECUTE";<br>
if (! defined($sth->rows()) ||
$sth->rows() == 0 ) {<br>
print "NO OPEN SESSIONS\n";<br>
exit 2;<br>
}<br>
$i = 0;<br>
while ($i < $sth->rows) {<br>
$test = $sth->fetchrow_hashref();<br>
$nasport="$test->{NASPortId}";<br>
$username="$test->{UserName}";<br>
$nasporttype="$test->{NASPortType}";<br>
if ($test->{AcctSessionTime} eq "0") {<br>
$acctime="1";<br>
} else {<br>
$acctime="$test->{AcctSessionTime}";<br>
}<br>
$uniqueid="$test->{AcctUniqueId}";<br>
$inoctets="$test->{AcctInputOctets}";<br>
$outoctets="$test->{AcctOutputOctets}";<br>
$nasip="$test->{NASIPAddress}";<br>
$nasid="$test->{NasIdentifier}";<br>
$callid="$test->{CallingStationId}";<br>
$calledid="$test->{CalledStationId}";<br>
$sessid="$test->{AcctSessionId}";<br>
$authtype="$test->{AcctAuthentic}";<br>
$framedip="$test->{FramedIPAddress}";<br>
$realm="$test->{Realm}";<br>
$acctstart="$test->{AcctStartTime}";<br>
my $parser =<br>
DateTime::Format::Strptime->new( pattern => '%Y-%m-%d %H:%M:%S' );<br>
<br>
my $dt = $parser->parse_datetime($acctstart);<br>
my $dtn = DateTime->now();<br>
$delay = ($dtn->epoch() - $dt->epoch()) - $test->{AcctSessionTime};<br>
print "=================\n";<br>
print "RadAcctId: $test->{RadAcctId}\n";<br>
print "UserName: $test->{UserName}\n";<br>
print "AcctStart: " . $dt->ymd . ' ' . $dt->hms .
"\n";<br>
print "AcctStop: " . $dtn->ymd . ' ' . $dtn->hms .
"\n";<br>
print "AcctSessionTime: $acctime \n";<br>
print "AcctStopDelay: $delay\n";<br>
radsend();<br>
sleep 1;<br>
$i++;<br>
}<br>
$sth->finish();<br>
&disconnect_db();<br>
}<br>
<br>
<br>
sub radsend {<br>
my $r = new Authen::Radius(Host
=> 'localhost:1813', Service => radacct, Secret => 'SHARESECRET');<br>
Authen::Radius->load_dictionary('/usr/share/freeradius/dictionary');<br>
$r->clear_attributes;<br>
$r->add_attributes (<br>
{ Name => 'User-Name', Value => $username},<br>
{ Name => 'NAS-Port', Value => $nasport},<br>
{ Name => 'NAS-Port-Type', Value => $nasporttype},<br>
{ Name => 'Acct-Session-Time', Value => $acctime},<br>
{ Name => 'Acct-Unique-Session-Id', Value => $uniqueid},<br>
{ Name => 'Acct-Input-Octets', Value => $inoctets},<br>
{ Name => 'Acct-Output-Octets', Value => $outoctets},<br>
{ Name => 'Acct-Terminate-Cause', Value => "Admin-Reset"},<br>
{ Name => 'Acct-Status-Type', Value => "Stop" },<br>
{ Name => 'NAS-IP-Address', Value => $nasip},<br>
{ Name => 'NAS-Identifier', Value => $nasid },<br>
{ Name => 'Calling-Station-Id', Value => $callid },<br>
{ Name => 'Called-Station-Id', Value => $calledid },<br>
{ Name => 'Acct-Delay-Time', Value => $delay },<br>
{ Name => 'Acct-Session-Id', Value => $sessid},<br>
{ Name => 'Acct-Authentic', Value => $authtype},<br>
{ Name => 'Framed-IP-Address', Value => $framedip},<br>
{ Name => 'Realm', Value => $realm, Type => 'string' }<br>
);<br>
$r->send_packet(ACCOUNTING_REQUEST);<br>
$rcv =
$r->recv_packet(ACCOUNTING_RESPONSE);<br>
my $error=$r->strerror();<br>
if ( $error eq "none" ) {<br>
print "New Status: Session Closed\n";<br>
} else {<br>
print "Error: Session not Closed\nOUTPUT: $error\n";<br>
}<br>
}<br>
<br>
<br>
I am not the best programmer in the world but it works, I hope this helps spark
some creative to solve your issue.<br>
<br>
--<br>
Leigh Martell<br>
<br>
<o:p></o:p></span></font></p>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>On Thu, Nov 20, 2008 at 2:15 PM, Tony Spencer <<a
href="mailto:tony@tonyspencer.co.uk">tony@tonyspencer.co.uk</a>> wrote:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>I'd rather not disconnect 4,000 users in one go.<o:p></o:p></span></font></p>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><br>
> -----Original Message-----<br>
> From: freeradius-users-bounces+tony=<a href="http://tonyspencer.co.uk"
target="_blank">tonyspencer.co.uk</a>@<a href="http://lists.freeradius.org"
target="_blank">lists.freeradius.org</a><br>
> [mailto:<a href="mailto:freeradius-users-">freeradius-users-</a><o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>> bounces+tony=<a href="http://tonyspencer.co.uk" target="_blank">tonyspencer.co.uk</a>@<a
href="http://lists.freeradius.org" target="_blank">lists.freeradius.org</a>] On
Behalf Of Marinko<br>
> Tarlac<br>
> Sent: 20 November 2008 19:02<br>
> To: <st1:PersonName w:st="on">FreeRadius users mailing list</st1:PersonName><br>
> Subject: Re: New entry for Interim packet<br>
><o:p></o:p></span></font></p>
</div>
<div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>> Create CRON script which starts 5-10 min after midnight (first day
in<br>
> the month) and disconnect all active users. Then you will have<br>
> AcctStopTime information in your database and you can sum traffic from<br>
> previous month...<br>
><br>
> Tony Spencer wrote:<br>
> ><br>
> > Hello<br>
> ><br>
> > Our setup is as follows:<br>
> ><br>
> > Centos 5.2<br>
> ><br>
> > FreeRADIUS Version 2.0.2<br>
> ><br>
> > MySQL Version: 4.1.20<br>
> ><br>
> > We are using FreeRadius for our ADSL users and its working fine.<br>
> ><br>
> > Except when it comes to working out the usage stats for each user at<br>
> > the end of each month.<br>
> ><br>
> > Its easy to do with all sessions that started in the previous month<br>
> > and have a Stop status.<br>
> ><br>
> > But it's difficult when a session rolled over to the next month<br>
> > because the status is Alive.<br>
> ><br>
> > We're trying to find a why to make FreeRadius:<br>
> ><br>
> > Enter a new entry into the Radacct table for a session for an Interim<br>
> > update<br>
> ><br>
> > Mark the previous session with a stop Status and update the OctetsIn<br>
> > and OctetsOut for that session with the current value.<br>
> ><br>
> > Set the new session OctetsIn and OctetsOut at zero until the next<br>
> > update and then it starts from the beginning again.<br>
> ><br>
> > However we can't find a way of making FreeRadius:<br>
> ><br>
> > Run 2 sql statements in the same update.<br>
> ><br>
> > Set the new session counter to zero and not roll over the next
updates<br>
> > Octets.<br>
> ><br>
> > We have found the following site:<br>
> > <a
href="http://www.netexpertise.eu/en/freeradius/daily-accounting.html"
target="_blank">http://www.netexpertise.eu/en/freeradius/daily-accounting.html</a>
with a<br>
> > way of doing this within MySQL with procedures, but apparently this<br>
> > only works with MySQL 5.<br>
> ><br>
> > Having installed MySQL 5 on a test server and importing our Radius<br>
> > database we tried running the first procedure but get an error:<br>
> ><br>
> > ERROR 1064 (42000): You have an error in your SQL syntax; check the<br>
> > manual that corresponds to your MySQL server version for the right<br>
> > syntax to use near 'DECLARE COUNTER_LIMIT BIGINT(12)' at line 1<br>
> ><br>
> > mysql> SET COUNTER_LIMIT = POW(2,32);<br>
> ><br>
> > ERROR 1193 (HY000): Unknown system variable 'COUNTER_LIMIT<br>
> ><br>
> > Has anyone any ideas on how to do what we require or has anyone had<br>
> > any luck with the instructions on the URL?<br>
> ><br>
> > Thanks in advance.<br>
> ><br>
> > Tony<br>
> ><br>
> >
------------------------------------------------------------------------<br>
> ><br>
> > -<br>
> > List info/subscribe/unsubscribe? See<br>
> <a href="http://www.freeradius.org/list/users.html" target="_blank">http://www.freeradius.org/list/users.html</a><br>
><br>
> -<br>
> List info/subscribe/unsubscribe? See<br>
> <a href="http://www.freeradius.org/list/users.html" target="_blank">http://www.freeradius.org/list/users.html</a><br>
<br>
-<br>
List info/subscribe/unsubscribe? See <a
href="http://www.freeradius.org/list/users.html" target="_blank">http://www.freeradius.org/list/users.html</a><o:p></o:p></span></font></p>
</div>
</div>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
</div>
</body>
</html>