Syed Jahanzaib Personal Blog to Share Knowledge !

November 1, 2017

Mikrotik with Freeradius/mySQL – Dealing with STALE sessions in FR – Part 5

Filed under: freeradius — Tags: , — Syed Jahanzaib / Pinochio~:) @ 1:26 PM

~ Dealing with STALE session in Freeradius 2.x ~
! From the CORE of FREERADIUS !
By
Syed jahanzaib

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …


Scenario:

Mikrotik is working as as NAS (pppoe server) along with Freeradius for authorization/accounting. When electric power or hardware failure occurs or in a situation where NAS cannot update the FR about user is being disconnected & not active any more, the FR will consider user ACTIVE in radacct table, therefore on next dialup attempt by the user (once every thing is restored), he will get access denied because

  1. There is Simltanous-Use attribute to prevent multiple login from same user
  2. user accctsoptime is NULL because FR have not receive any update from the NAS about user is not online any more.

To remove such stale sessions, there are various methods, you can make your own bash script & schedule it to run every x minutes (example every 5 minutes). Or you can use IF query in authorize session so that when user tries to reconnect & his sessions have NULL  then in this case the query should put stop entry in acctstoptime and allow user new login. or make a PHP program that can be scheuled to run every 5 minutes and then query the radacct session for users whose account update have not received from the NAS.


STEP – 1

First you need to add lastupdate column in your RADACCT table (in radius db) . Infact all solutions posted in this guide relies on it. so add it

ALTER TABLE 'radacct' ADD 'lastupdate' TIMESTAMP NOT NULL AFTER 'xascendsessionsvrkey';

Now you can use following methods, which ever is ok with you, or you can combine both as well, I tested it a local network & worked well for me …. z@iB



Solution # 1
Using BASH in CRON

You can use following bash script to run every 5 minutes which will check for any stale session by matching last update time with current. If the radius have not received any updates for that account from the NAS for more then 5 minutes , it wil consider this session as STALE and will close its session its record.

mysql -uroot -pSQLPASS -s --skip-column-names -e "use radius; UPDATE radacct SET acctstoptime = NOW(), acctterminatecause = 'SCRIPT ACTION' WHERE acctstoptime IS NULL AND lastupdate < DATE_SUB(NOW(), INTERVAL 5 MINUTE)";

Solution#2
This will update the radacct acctstoptime only if user will try to re-connect, ]

Edit /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

in AUTHORIZE { section add following query

if (User-Name){
if("%{sql:UPDATE radacct set AcctStopTime=ADDDATE(AcctStartTime,INTERVAL AcctSessionTime SECOND), AcctTerminateCause='Clear-Stale Session' WHERE UserName='%{User-Name}' and CallingStationId='%{Calling-Station-Id}' and AcctStopTime is NULL}"){
}
}

Save & restart freeradius server.

This way if NAS goes out, the session will still show online in radacct table, but when user will relogin next time, his session on radacct table will update and new entry will be created.


Regard’s
SYED JAHANZAIB

7 Comments »

  1. […] FREERADIUS WITH MIKROTIK – Part #5 […]

    Like

    Pingback by Mikrotik with Freeradius/mySQL # Part-1 | Syed Jahanzaib Personal Blog to Share Knowledge ! — November 2, 2017 @ 8:27 AM

  2. […] FREERADIUS WITH MIKROTIK – Part #5 – Stale Sessions […]

    Like

    Pingback by Mikrotik with Freeradius/mySQL – Quota Limit # Part-7 | Syed Jahanzaib Personal Blog to Share Knowledge ! — January 8, 2018 @ 11:37 AM

  3. […] FREERADIUS WITH MIKROTIK – Part #5 – Stale Sessions […]

    Like

    Pingback by Mikrotik with Freeradius/mySQL – Change IP Pool After Expiration # Part-3 | Syed Jahanzaib Personal Blog to Share Knowledge ! — January 10, 2018 @ 12:48 PM

  4. […] FREERADIUS WITH MIKROTIK – Part #5 – Stale Sessions […]

    Like

    Pingback by Mikrotik with Freeradius/mySQL – Trimming & Archiving RADACCT # Part-8 | Syed Jahanzaib Personal Blog to Share Knowledge ! — January 15, 2018 @ 2:40 PM

  5. Salam Alaykum Syed , actually how to radzap -u the user after you cleared his session in mysql db , i can see here sometimes the user cant login because already logged in i radzap -u then the user comes online
    and sometimes radzap responded with “nothing to do” but the user cant login becaue already logged in , any suggestions ?

    thank you very much

    Like

    Comment by Walid Alassaad — June 7, 2018 @ 1:19 PM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: