Hi,
I am going to define a group for freeradius users with the following
features:
- Group name: Multi60
- When users login between "14:00:00" - "02:00:00" , they pay normal
internet connection fee (S1). but when users login from "00:00:00" to
"07:00:00" they pay halt price fee (S2). and when they connect from
"07:00:00" to "14:00:00" they pay only 1/3 price of normal connection fee
(S3).
- The users have no limitation on connection time so they can connect
allover the day/night ( 24 hourly)
for example 60 hours connection time between 07:00:00 - 14:00:00 is equal to
40 hours connection time between 02:00:00 - 07:00:00 and it equals 20 hours
connection time between normal duration.
so we have the following ralation between S1,S2,S3 price:
S2_Price = 1/2 * S1_Price
S3_Price = 1/3 * S1_Price
and the following ralation between S1,S2,S3 connection time:
S2_Connection = 2 * S1_Connection
S3_Connection = 3 * S1_Connection
suppose we limit the users to use only 60 hours in Nomal connection time and
if users reach their max limitation, their account will removed and new
account should be provided.
if 'Multi60' group Users connect between 14:00:00 - 02:00:00 they maximumly
can connect 60 hours and if they ONLY connect between 02:00:00 - 07:00:00
they maximumly can connect 120 hours and if they connect between 07:00:00 -
14:00:00 they maximumly connect ONLY 180 hours !
overall there is the following relation between connection times:
S1_Connection_Time + 1/2 S2_Connection_Time + 1/3 S3_Connection_Time <= 60
Hours
---------------------------------
To implement the above accounting with freeradius + mysql I do the
following:
1- defining an sql instance in sql.conf named 'sql'
2- defining the below sqlcounter in radiusd.conf:
sqlcounter Multi60 {
counter-name = M60
check-name = Max-M60-Session
sqlmod-inst = sql
key = User-Name
reset = never
query="SELECT SUM(IF(HOUR(AcctStartTime)>='02:00:00' AND
HOUR(AcctStopTime)<='06:00:00',0.5*AcctSessionTime,0)+IF(HOUR(AcctStartTime)>='07:00:00'
AND
HOUR(AcctStopTime)<='13:00:00',0.33*AcctSessionTime,0)+IF(HOUR(AcctStartTime)>='14:00:00'
AND
HOUR(AcctStopTime)<='23:00:00',AcctSessionTime,0)+IF(HOUR(AcctStartTime)>='00:00:00'
AND HOUR(AcctStopTime)<='01:00:00',AcctSessionTime,0) ) from radacct WHERE
username='%{%k}'"
}
3- adding Max-M60-Session attribute in radcheckgroup table for Group
'Multi60' with the value of 216000 ( = 60 hours * 3600 )
4- Adding some usernames in radgroup table with the group attribute of
'Multi60'
5- Runing Radiusd -X
everything is ok but the only things is the below message in radius bedug
mode:
Reserving sql socket id: 2
query: SELECT SUM(IF(HOUR(AcctStartTime)>='02:00:00' AND
HOUR(AcctStopTime)<='06:00:00',0.5*AcctSessionTime,0)+IF(HOUR(AcctStartTime)>='07:00:00'
AND HOUR(AcctStopTime)<='13:00:00',0.33*AcctSessionTime,0)+IF(HOUR(Acct
MYSQL check_error: 1064 received
rlm_sql: database query error
rlm_sql: Released sql socket id: 2
radius_xlat: ''
rlm_sqlcounter: (Check item - counter) is greater than zero
rlm_sqlcounter: Authorized user saber, check_item=216000, counter=0
rlm_sqlcounter: Sent Reply-Item for user saber, Type=Session-Timeout,
value=216000
modcall[authorize]: module "Multi60" returns ok
Please let me know what is wrong with my sql query as it works correctly
with no errors in mysql command line shell.
does radius cut the tail of long sql queries of sqlcounter ? or what ?
please look at the debug log and and my Tail-less query there !!!!!
any suggestion or new ways of implementing such services are welcome and I
thanks all freeradius users and developers for their consideration.
Best Regards,
Mehdi Roomi
_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
