FREERADIUS and HOTSPOT Limits and Session-Timeout issue

RouterOS General
1

I am developing a hotspot solution, and have a problem with radius.

I’m running freeradius on debian, configured to talk to mysql.

Authentication and assigning attributes works fine, what I need is to be able to update the values in the mysql tables with decremented values ie the xmit-limit, recv-limit, and session-timeout.

The radius server assigns these values to the hotspot no problem, A user can be limited to say 1 hour of use and 50MB. The issue is that if they then log off and back on, they get their 50MB and 1 hour back all over again.

I need MT to beable to update the SQL tables with the appropriate vales when users log off the hotspot, and also incrementally every minute or so using radius accounting.

Freeradius SQL connector already executes a SQL query on accounting update, and accounting stop, which works great, but only adds records to the accouting table (radacct)

I need to be able to modify the values of radreply as well as the accounting table.

This is the query that freeradius does at the moment on accounting stop:

accounting_stop_query = "UPDATE ${acct_table2} SET ${acct_table2}.AcctStopTime = ‘%S’, ${acct_table2}.AcctSessionTime = ‘%{Acct-Session-Time}’, ${acct_table2}.AcctInputOctets = ‘%{Acct-Input-Octets}’, ${acct_table2}.AcctOutputOctets = ‘%{Acct-Output-Octets}’, ${acct_table2}.AcctTerminateCause = ‘%{Acct-Terminate-Cause}’, ${acct_table2}.AcctStopDelay = ‘%{Acct-Delay-Time}’, ${acct_table2}.ConnectInfo_stop = ‘%{Connect-Info}’ WHERE ${acct_table2}.AcctSessionId = ‘%{Acct-Session-Id}’ AND ${acct_table2}.UserName = ‘%{SQL-User-Name}’ AND ${acct_table2}.NASIPAddress = ‘%{NAS-IP-Address}’;

This is what I would like to do as well (to update the data values) :

“UPDATE radreply SET Value = (CASE Attribute WHEN ‘Recv-Limit’ THEN Value - ‘%{Acct-Input-Octets}’ WHEN ‘Xmit-Limit’ THEN Value - ‘%{Acct-Output-Octets}’ END) WHERE UserName = ‘%{SQL-User-Name}’”

If anyone has a similar scenario setup already and can help that would be much appreciated, alternatively if you are a SQL addict and can combine these two queries that would also be great.

Surely this a pretty fundamental thing to be able to update such attributes.

Regards,

Russell Tester.

2

Hi Russ,

have a look at the "rlm_counter" module ... 'man rlm_counter'.
I think this is what you need. We are using it for "Session-Timeout" attribute.
See also part about rlm_counter in radiusd.conf.

How do you configure the billing by volume in pre-paid mode?
When you say the user gets 50MB, what values for the attributes you are using?

Recv-Limit = 25MB
Xmit-Limit = 25MB

??? There is no Attribute so far for billing by volume, only these two
for seperate upstream and downstream ??

seandsl

3

We are advertising 50MB, but atcually giving the user 50MB upstream and 50MB downstream, As far as I can see there is no way to limit it with upload and download combined. This shouldnt be too much of a problem though as most traffic will be download.

4

There was a thread here in the forum on how to get at least close to giving users real 50 MB (or whatever) traffic limit (combines up/download). Do a search, perhaps you will find this useful…