Hi Leigh

Your script is most useful.

We have interim updates every 2 hours and hadn’t implemented anything to close stale sessions.

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.

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.

Any ideas?

Tony

---

From: freeradius-users-bounces+tony=tonyspencer.co.uk@lists.freeradius.org [mailto:freeradius-users-bounces+tony=tonyspencer.co.uk@lists.freeradius.org] On Behalf Of Leigh Martell
Sent: 20 November 2008 20:41
To: FreeRadius users mailing list
Subject: Re: New entry for Interim packet

Hello Tony,
  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).

Here is an example of a nightly close-session script I run to clean up stale sessions.

#!/usr/bin/perl
use Authen::Radius;
use DBI;
use DateTime;
use DateTime::Format::Strptime;
getopen();

sub connect_db {
        local($radius_name,$radius_user,$radius_passwd) = @_;
        $connect_name = "connect to db";
        $dbh_radius = DBI->connect("DBI:mysql:$radius_name:localhost", $radius_user, $radius_passwd);
        if(!$dbh_radius) {
                print "Error $connect_name: Cannot connect to $radius_name - $dbh_radius->errstr<BR>\n";
                exit 2;
        }
}

sub disconnect_db {
        $dbh_radius->disconnect();
        return 1;
}

sub getopen {
        $radius_name = "radiusdb";
        $radius_user = "username";
        $radius_passwd = "password";
        my $dtn = DateTime->now();
        $date = $dtn->ymd . "%";
        &connect_db($radius_name,$radius_user,$radius_passwd);
        $qAccount = qq{SELECT * FROM radacct WHERE AcctStopTime = \'0000-00-00 00:00:00\' AND AcctStartTime NOT LIKE \'$date\'};
        $sth = $dbh_radius->prepare($qAccount);
        $sth->execute() or warn "ERROR IN EXECUTE";
        if (! defined($sth->rows()) || $sth->rows() == 0 ) {
                print "NO OPEN SESSIONS\n";
                exit 2;
        }
        $i = 0;
        while ($i < $sth->rows) {
                $test = $sth->fetchrow_hashref();
                $nasport="$test->{NASPortId}";
                $username="$test->{UserName}";
                $nasporttype="$test->{NASPortType}";
                if ($test->{AcctSessionTime} eq "0") {
                        $acctime="1";
                } else {
                        $acctime="$test->{AcctSessionTime}";
                }
                $uniqueid="$test->{AcctUniqueId}";
                $inoctets="$test->{AcctInputOctets}";
                $outoctets="$test->{AcctOutputOctets}";
                $nasip="$test->{NASIPAddress}";
                $nasid="$test->{NasIdentifier}";
                $callid="$test->{CallingStationId}";
                $calledid="$test->{CalledStationId}";
                $sessid="$test->{AcctSessionId}";
                $authtype="$test->{AcctAuthentic}";
                $framedip="$test->{FramedIPAddress}";
                $realm="$test->{Realm}";
                $acctstart="$test->{AcctStartTime}";
                my $parser =
                    DateTime::Format::Strptime->new( pattern => '%Y-%m-%d %H:%M:%S' );

                my $dt = $parser->parse_datetime($acctstart);
                my $dtn = DateTime->now();
                $delay = ($dtn->epoch() - $dt->epoch()) - $test->{AcctSessionTime};
                print "=================\n";
                print "RadAcctId: $test->{RadAcctId}\n";
                print "UserName: $test->{UserName}\n";
                print "AcctStart: " . $dt->ymd . ' ' . $dt->hms . "\n";
                print "AcctStop: " . $dtn->ymd . ' ' . $dtn->hms . "\n";
                print "AcctSessionTime: $acctime \n";
                print "AcctStopDelay: $delay\n";
                radsend();
                sleep 1;
                $i++;
        }
        $sth->finish();
        &disconnect_db();
}


sub radsend {
        my $r = new Authen::Radius(Host => 'localhost:1813', Service => radacct, Secret => 'SHARESECRET');
        Authen::Radius->load_dictionary('/usr/share/freeradius/dictionary');
        $r->clear_attributes;
        $r->add_attributes (
                { Name => 'User-Name', Value => $username},
                { Name => 'NAS-Port', Value => $nasport},
                { Name => 'NAS-Port-Type', Value => $nasporttype},
                { Name => 'Acct-Session-Time', Value => $acctime},
                { Name => 'Acct-Unique-Session-Id', Value => $uniqueid},
                { Name => 'Acct-Input-Octets', Value => $inoctets},
                { Name => 'Acct-Output-Octets', Value => $outoctets},
                { Name => 'Acct-Terminate-Cause', Value => "Admin-Reset"},
                { Name => 'Acct-Status-Type', Value => "Stop" },
                { Name => 'NAS-IP-Address', Value => $nasip},
                { Name => 'NAS-Identifier', Value => $nasid },
                { Name => 'Calling-Station-Id', Value =>  $callid },
                { Name => 'Called-Station-Id', Value => $calledid },
                { Name => 'Acct-Delay-Time', Value => $delay },
                { Name => 'Acct-Session-Id', Value => $sessid},
                { Name => 'Acct-Authentic', Value => $authtype},
                { Name => 'Framed-IP-Address', Value => $framedip},
                { Name => 'Realm', Value => $realm, Type => 'string' }
        );
        $r->send_packet(ACCOUNTING_REQUEST);
        $rcv = $r->recv_packet(ACCOUNTING_RESPONSE);
        my $error=$r->strerror();
        if ( $error eq "none" ) {
                print "New Status: Session Closed\n";
        } else {
                print "Error: Session not Closed\nOUTPUT: $error\n";
        }
}


I am not the best programmer in the world but it works, I hope this helps spark some creative to solve your issue.

--
Leigh Martell

On Thu, Nov 20, 2008 at 2:15 PM, Tony Spencer <tony@tonyspencer.co.uk> wrote:

I'd rather not disconnect 4,000 users in one go.

> -----Original Message-----
> From: freeradius-users-bounces+tony=tonyspencer.co.uk@lists.freeradius.org
> [mailto:freeradius-users-

> bounces+tony=tonyspencer.co.uk@lists.freeradius.org] On Behalf Of Marinko
> Tarlac
> Sent: 20 November 2008 19:02
> To: FreeRadius users mailing list
> Subject: Re: New entry for Interim packet
>

> Create CRON script which starts 5-10 min after midnight (first day in
> the month) and disconnect all active users. Then you will have
> AcctStopTime information in your database and you can sum traffic from
> previous month...
>
> Tony Spencer wrote:
> >
> > Hello
> >
> > Our setup is as follows:
> >
> > Centos 5.2
> >
> > FreeRADIUS Version 2.0.2
> >
> > MySQL Version: 4.1.20
> >
> > We are using FreeRadius for our ADSL users and its working fine.
> >
> > Except when it comes to working out the usage stats for each user at
> > the end of each month.
> >
> > Its easy to do with all sessions that started in the previous month
> > and have a Stop status.
> >
> > But it's difficult when a session rolled over to the next month
> > because the status is Alive.
> >
> > We're trying to find a why to make FreeRadius:
> >
> > Enter a new entry into the Radacct table for a session for an Interim
> > update
> >
> > Mark the previous session with a stop Status and update the OctetsIn
> > and OctetsOut for that session with the current value.
> >
> > Set the new session OctetsIn and OctetsOut at zero until the next
> > update and then it starts from the beginning again.
> >
> > However we can't find a way of making FreeRadius:
> >
> > Run 2 sql statements in the same update.
> >
> > Set the new session counter to zero and not roll over the next updates
> > Octets.
> >
> > We have found the following site:
> > http://www.netexpertise.eu/en/freeradius/daily-accounting.html with a
> > way of doing this within MySQL with procedures, but apparently this
> > only works with MySQL 5.
> >
> > Having installed MySQL 5 on a test server and importing our Radius
> > database we tried running the first procedure but get an error:
> >
> > ERROR 1064 (42000): You have an error in your SQL syntax; check the
> > manual that corresponds to your MySQL server version for the right
> > syntax to use near 'DECLARE COUNTER_LIMIT BIGINT(12)' at line 1
> >
> > mysql> SET COUNTER_LIMIT = POW(2,32);
> >
> > ERROR 1193 (HY000): Unknown system variable 'COUNTER_LIMIT
> >
> > Has anyone any ideas on how to do what we require or has anyone had
> > any luck with the instructions on the URL?
> >
> > Thanks in advance.
> >
> > Tony
> >
> > ------------------------------------------------------------------------
> >
> > -
> > List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html

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