Radacct Reused ?

[Oscar Jofre]

Hi,

At the end I did finish the workaround to stop radius to reuse radacct old sessions when NAS is not sending a strong unique session id (acctsessionid)

For me the problem was that mikrotik uses 1 hexa digit on acctsessionid that is incremented every reboot. For that NASes that are rebooted every day we will have after 16 days
the same acctsessionid. So if the username that creates the first session is "always" the same we will end with the same acctsessionid+username that will generate the a  acctuniqueid
that has been created some days ago. So radius will reuse that session instead of creating a new one.

To solve it I did create a daily job that for every session closed (acctstoptime not null) would regenerate de acctuniqueid with another one with timestamp that would make be unique "forever".
Also to know the rows from radacct (sessions) that has ben updated I did create a new row on radacct called newuniqueid with 0 by default and 1 when acctuniqueid updated.

In my case I use mysql.

So this is the steps I've done

1)	Alter Database

	ALTER TABLE `radius`.`radacct` ADD COLUMN `newuniqueid` TINYINT(4) NULL DEFAULT 0 AFTER `framedipaddress`;

	You can do this sentence and freeradius would still working because all the sql sentences used on freeradius only uses the rows needed.

	It takes me 25 minutes alter the table.

2)	Update Database

	I did update radacct with the new acctuniqueid for all rows with acctstoptime not null. Updating also newuniqueid to 1
	Be careful this update can take long time. I did break the update with radacctid<200000 , radacctid<400000 ... 

	update radius.radacct set newuniqueid=1, acctuniqueid=md5(concat(username,acctsessionid,framedipaddress,acctuniqueid,UNIX_TIMESTAMP(acctupdatetime),UNIX_TIMESTAMP(acctstoptime)))
					where acctstoptime is not null and newuniqueid<>1 ;
	

3) 	Cron Job every day to "SAFE" sessions
	
	Every day at 1.30 I run a cron job that would update all acctstarttime between yesterday and 60 days ago with acctstoptime not null with the new acctuniqueid.
	60 days ago would make create new uniqueid for that sessions that has been opened for more than 1 day.

	This is PHP peace of code

	$fFecIni = date('Y-m-d',strtotime("-60 days"));
	$fFecFin = date('Y-m-d',strtotime("-1 days"));
	$tFecha=" BETWEEN '".$fFecIni." 00:00:00' AND '".$fFecFin." 23:59:59'";

	$tSql= "update radius.radacct set newuniqueid=1,
                      acctuniqueid=md5(concat(username,acctsessionid,framedipaddress,acctuniqueid,UNIX_TIMESTAMP(acctupdatetime),UNIX_TIMESTAMP(acctstoptime)))
            		where acctstarttime $tFecha and acctstoptime is not null and newuniqueid=0; ";


Hope it's clear enough.

Rgds,

Oscar Jofre.


-----Mensaje original-----
De: Freeradius-Users <freeradius-users-bounces+oscar=jofre.com at lists.freeradius.org> En nombre de Alan DeKok
Enviado el: miércoles, 08 de mayo de 2019 0:23
Para: FreeRadius users mailing list <freeradius-users at lists.freeradius.org>
Asunto: Re: Radacct Reused ?

On May 7, 2019, at 3:18 PM, Oscar <oscar at jofre.com> wrote:
> Good idea, I've searcha bit but haven't seen any example so I gess as you suggest I can add it and I don't need to change anything from inside freeraius setup.

  It's just a column in SQL.  The SQL database documentation says how to modify SQL tables.

  Since FreeRADIUS doesn't know about the column, it will never read or write the column.

> Is that easy ?

  Yes, it's easy.

  Alan DeKok.


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