Help with sqlcounter for data transferred

[Fabrizio Fiore Donati]

Hi,
i'm configuring a server with a sql counter to check the total byte in a
week for the users.
But the server will reply a wrong count.

Here's the counter:

sqlcounter weeklybytecounter {
counter-name = Weekly-Total-Max-Octets
check-name = Max-Weekly-Octets
reply-name = Mikrotik-Total-Limit
sqlmod-inst = sql
key = User-Name
reset = weekly
query = SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) FROM radacct
WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime
 '%b'


But the reply doesn't is the operation between (check-name value) - (query
value)

for example:

rlm_sqlcounter: Authorized user fabrizio, check_item=30,
counter=38101894

I expect a reply of 30 - 38101894 = 2961898106

but i receive different value also bigger.


Any idea ?


-- 
Fabrizio Fiore Donati

Mobile: +39 3289872420
E-mail: fabrizio.fioredon...@2bite.net

2bite s.r.l.
Via Saragat snc
67100 L'Aquila (AQ) - Italy
Tel.: +39 0862441583
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: sqlcounter instance not found in modules section

[Russell Stockhammer]

On Wed, 3 Oct 2012, Fajar A. Nugraha wrote:




At times like this it's handy if you pasted the WHOLE output of
radiusd -X. Among others, it will show whether it reads the correct
file. Usually you edit one file while FR loads the other.



Interestingly enough when I run radiusd -X it loads perfectly then any 
time I run radiusd normally it works as well...  But if I change the name 
of a sqlcounter instance and run radiusd normally it will error until 
radiusd -X is run again  Very strange!!!


I spend all last night testing this and it seams I was always doign the 
right thing config wise it was just radiusd didn't read all the config 
files until it was run with the debug -X argument once.


If anyone can replicate this it might be a bug or it could just be a bug 
in my compile.


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

sqlcounter instance not found in modules section

[Russell Stockhammer]

Hey Everyone,

I'm trying to create a new sqlcounter instance that counts octets.  I 
think

what I've done is correct but I get the following error when I start
FreeRadius...



Mon Oct  1 21:09:41 2012 : Error:
/usr/local/etc/raddb/sites-enabled/default[203]: Failed to find 
dailyoctetcounter in the modules section.
Mon Oct  1 21:09:41 2012 : Error: 
/usr/local/etc/raddb/sites-enabled/default[69]: Errors parsing authorize 
section.

Mon Oct  1 21:09:41 2012 : Error: Failed to load virtual server default



This error also occurs when I change the name of dailycounter to be
dailytimecounter.

My relivant config is as follows...

 radius.conf 

instantiate {

dailycounter
dailyoctetcounter

}

-

 sql/mysql/counter.conf 

sqlcounter dailyoctetcounter {
counter-name = Daily-Session-Octet
check-name = Max-Daily-Session-Octet
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = daily

query = SELECT SUM(acctinputoctets + acctoutputoctets) \
 FROM radacct WHERE username = '%{%k}' \
 AND UNIX_TIMESTAMP(acctstarttime)  '%b'
}

sqlcounter dailycounter {
counter-name = Daily-Session-Time
check-name = Max-Daily-Session
reply-name = Session-Timeout
sqlmod-inst = sql
key = User-Name
reset = daily

query = SELECT SUM(acctsessiontime - \
 GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
 FROM radacct WHERE username = '%{%k}' AND \
 UNIX_TIMESTAMP(acctstarttime) + acctsessiontime  '%b'
}



 sites-enabled/default 

authorize {

dailycounter {
reject = 1
}
if (reject) {
update reply {
Reply-Message := Your daily time limit is up!
}
ok = reject
}

dailyoctetcounter {
reject = 1
}
if (reject) {
update reply {
Reply-Message := Your daily transfer limit is 
up!

}
ok = reject
}

}



Is there a set of keywords that are only valid for sqlcounters?  Or like I
think anything that isn't already another instance should be vaild. Please
tell me where I'm going wrong with my config.

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

Re: sqlcounter instance not found in modules section

[Alan DeKok]

Russell Stockhammer wrote:
 I'm trying to create a new sqlcounter instance that counts octets.  I think
 what I've done is correct but I get the following error when I start
 FreeRadius...
 
...
  sql/mysql/counter.conf 

  Why?  The default sqlcounter file is in raddb/modules.  Put the
dailyoctetcounter configuration there, and it will work.

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

Re: sqlcounter instance not found in modules section

[Fajar A. Nugraha]

On Tue, Oct 2, 2012 at 6:47 PM, Alan DeKok al...@deployingradius.com wrote:
 Russell Stockhammer wrote:
 I'm trying to create a new sqlcounter instance that counts octets.  I think
 what I've done is correct but I get the following error when I start
 FreeRadius...

 ...
  sql/mysql/counter.conf 

   Why?  The default sqlcounter file is in raddb/modules.  Put the
 dailyoctetcounter configuration there, and it will work.

I think he might got confused because there are
raddb/sql/mysql/counter.conf and raddb/sql/postgresql/counter.conf, as
well as raddb/modules/sqlcounter_expire_on_login.

Perhaps we should add a small instruction on top of
raddb/sql/*/counter.conf, saying something like copy this file to
raddb/modules and adjust as needed? Plus probably a small update to
doc/rlm_sqlcounter. If no one else does this first I'll probably be
able to send a pull request in the next day or two.

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

Re: sqlcounter instance not found in modules section

[Alan DeKok]

Fajar A. Nugraha wrote:
 I think he might got confused because there are
 raddb/sql/mysql/counter.conf and raddb/sql/postgresql/counter.conf, as
 well as raddb/modules/sqlcounter_expire_on_login.

  OK... and the modules are in the raddb/modules directory.

 Perhaps we should add a small instruction on top of
 raddb/sql/*/counter.conf, saying something like copy this file to
 raddb/modules and adjust as needed? Plus probably a small update to
 doc/rlm_sqlcounter. If no one else does this first I'll probably be
 able to send a pull request in the next day or two.

  Sure.

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

Re: sqlcounter instance not found in modules section

[Russell Stockhammer]

On Tue, 2 Oct 2012, Alan DeKok wrote:


Russell Stockhammer wrote:

I'm trying to create a new sqlcounter instance that counts octets.  I think
what I've done is correct but I get the following error when I start
FreeRadius...


...

 sql/mysql/counter.conf 


 Why?  The default sqlcounter file is in raddb/modules.  Put the
dailyoctetcounter configuration there, and it will work.



Thanks for the response.  There wasn't a sqlcounter file in raddb/modules 
when I installed it.  I copied the file into raddb/modules and still get the

same error.  Is there something else I need to do?


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


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

Re: sqlcounter instance not found in modules section

[Russell Stockhammer]

On Tue, 2 Oct 2012, Fajar A. Nugraha wrote:


On Tue, Oct 2, 2012 at 6:47 PM, Alan DeKok al...@deployingradius.com wrote:

Russell Stockhammer wrote:

I'm trying to create a new sqlcounter instance that counts octets.  I think
what I've done is correct but I get the following error when I start
FreeRadius...


...

 sql/mysql/counter.conf 


  Why?  The default sqlcounter file is in raddb/modules.  Put the
dailyoctetcounter configuration there, and it will work.


I think he might got confused because there are
raddb/sql/mysql/counter.conf and raddb/sql/postgresql/counter.conf, as
well as raddb/modules/sqlcounter_expire_on_login.

Perhaps we should add a small instruction on top of
raddb/sql/*/counter.conf, saying something like copy this file to
raddb/modules and adjust as needed? Plus probably a small update to
doc/rlm_sqlcounter. If no one else does this first I'll probably be
able to send a pull request in the next day or two.


Ok, but there is also a;

-

$INCLUDE sql/mysql/counter.conf



but default in the modules section of radius.conf.  So I assumed it didn't 
need to be copied anywhere.






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


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

Re: sqlcounter instance not found in modules section

[Fajar A. Nugraha]

On Wed, Oct 3, 2012 at 12:04 PM, Russell Stockhammer
russg...@329ncle.no-ip.org wrote:
 Ok, but there is also a;

 -

 $INCLUDE sql/mysql/counter.conf

 

Good catch :)

FR is pretty flexible in its configuration. Suffice to say that most
(all?) module configuration (including sql and eap) in 3.x is moved to
modules-available, but in 2.x you might find them anywhere inside
raddb.

You can either uncomment that line, or copy it to modules directory,
which should do the same thing.

 but default in the modules section of radius.conf.  So I assumed it didn't
 need to be copied anywhere.

At times like this it's handy if you pasted the WHOLE output of
radiusd -X. Among others, it will show whether it reads the correct
file. Usually you edit one file while FR loads the other.

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

Re: Question about SQLcounter and reject sessions

[Andres Gomez Ruiz]

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

Question about SQLcounter and reject sessions

[Andres Gomez Ruiz]

Hi everybody!!


I have been using Freeradius as AAA of some wireless hotspots and it works
great!!


After reading the Rlm_sqlcounter wiki page I started to use it, and it also
works great. This is the code of my sqlcounters:



sqlcounter dailycounter {

counter-name = Daily-Session-Time

check-name = Max-Daily-Session

reply-name = Session-Timeout

sqlmod-inst = sql

key = User-Name

reset = daily
query = SELECT SUM(acctsessiontime) FROM radacct  WHERE \
username='%{%k}' AND acctstarttime  FROM_UNIXTIME('%b')
}

sqlcounter noresetcounter {
counter-name = Max-All-Session-Time
check-name = Max-All-Session
sqlmod-inst = sql
key = User-Name
reset = never
   query = SELECT (UNIX_TIMESTAMP( NOW() ) -
IFNULL(UNIX_TIMESTAMP(acctst$

}

Everything works fine, but now I have a question about the dailycounter:
I have some users that I need to reject their sessions at midnight, because
of that Im using the dailycounter... but I need that user can't login again
(the user is valid only 1 day).
In this moment the user can login again the next day. How can I do to
invalid the user after midnight?

An example of an user:

radcheck table
username: user1
User-Password :=

radusergroup table
username: user1
groupname: 1day

radusergroup table
groupname: 1day
Max-Daily-Session := 12000

Thanks a lot!!

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

Re: Question about SQLcounter and reject sessions

[Fajar A. Nugraha]

On Wed, Aug 8, 2012 at 8:34 PM, Andres Gomez Ruiz
andres.go...@urbalink.co wrote:
 I have some users that I need to reject their sessions at midnight, because
 of that Im using the dailycounter...

IIRC that's not what dailycounter is for.

 but I need that user can't login again
 (the user is valid only 1 day).
 In this moment the user can login again the next day. How can I do to
 invalid the user after midnight?

One way to do that was mentioned in the past. Try reading the
archives: 
http://freeradius.1045715.n5.nabble.com/Unix-TimeStamp-Based-Login-td5708187.html
. In particular, look at Phil's post.

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

Re: Probmels with sqlcounter module in FreeRADIUS 2.1.12

[Andrei Petru Mura]

 2012 : Info: [sql] ... expanding second conditional
Fri Jun 29 10:20:06 2012 : Info: [sql] expand: INSERT INTO radacct
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress,NASPortId,
NASPortType, AcctStartTime, AcctAuthentic,   ConnectInfo_start,
CalledStationId, CallingStationId, ServiceType,   FramedProtocol,
FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey)
VALUES('%{Acct-Session-Id}',   '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}',   NULLIF('%{Realm}', ''),   '%{NAS-IP-Address}',
%{%{NAS-Port}:-NULL},   '%{NAS-Port-Type}',   ('%S'::timestamp -
'%{%{Acct-Delay-Time}:-0}'::interval),   '%{Acct-Authentic}',
'%{Connect-Info}',   '%{Called-Station-Id}',   '%{Calling-Station-Id}',
'%{Service-Type}',   '%{Framed-Protocol}',   NULLIF('%{Framed-IP-Address}',
'')::inet,   0,   '%{X-Ascend-Session-Svr-Key}') - INSERT INTO radacct
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress,NASPortId,
NASPortType, AcctStartTime, AcctAuthentic,   ConnectInfo_start,
CalledStationId, CallingStationId, ServiceType,   FramedProtocol,
FramedIPAddress, AcctStartDelay,
Fri Jun 29 10:20:06 2012 : Debug: rlm_sql (sql): Reserving sql socket id: 0
Fri Jun 29 10:20:06 2012 : Debug: rlm_sql_postgresql: Status:
PGRES_COMMAND_OK
Fri Jun 29 10:20:06 2012 : Debug: rlm_sql_postgresql: query affected rows =
1
Fri Jun 29 10:20:06 2012 : Debug: rlm_sql (sql): Released sql socket id: 0
Fri Jun 29 10:20:06 2012 : Info: ++[sql] returns ok
Sending Accounting-Response of id 47 to 127.0.0.1 port 56325
Fri Jun 29 10:20:06 2012 : Info: Finished request 2.
Fri Jun 29 10:20:06 2012 : Info: Cleaning up request 2 ID 47 with timestamp
+263
Fri Jun 29 10:20:06 2012 : Debug: Going to the next request
Fri Jun 29 10:20:06 2012 : Debug: Waking up in 4.9 seconds.
Fri Jun 29 10:20:11 2012 : Info: Cleaning up request 1 ID 87 with timestamp
+263
Fri Jun 29 10:20:11 2012 : Info: Ready to process requests.

when I authenticate for the second time the user (after logged it out), I
get some more lines in the debug output, and also the Session-Timeout
attribute is send.
Bellow are some lines that appear in plus to the first debug track:

For //***1 the lines are:

Fri Jun 29 10:25:43 2012 : Debug: rlm_sqlcounter: Check item is greater
than query result
Fri Jun 29 10:25:43 2012 : Debug: rlm_sqlcounter: Authorized user test3,
check_item=60, counter=10
Fri Jun 29 10:25:43 2012 : Debug: rlm_sqlcounter: Sent Reply-Item for user
test3, Type=Session-Timeout, value=50
Fri Jun 29 10:25:43 2012 : Info: ++[hourlycounter] returns ok

For //***2 Sending Access-Accept of id 117 to 127.0.0.1 port 39595
Session-Timeout = 50


So, as you can see from my comments, the Session-Timeout attribute isn't
present in the radius response debug from the first login. The second time
when it appears, it is caluculated correctly. For now I think that I have
an issue with FR, so I don't go further to my chillispot NAS yet.
Do you have some suggestions?

N.B.
1. I changed the counter module name from dailycounter to hourlycounter,
for clearness.
2. Also, in the good working scenario debug trace, I logged in with test3
username. (to clarify the username incompatibilities from the two debug
output)
3. If you need also the second debug trace entirely, just let me know.

On Thu, Jun 28, 2012 at 3:50 PM, Fajar A. Nugraha l...@fajar.net wrote:

 On Thu, Jun 28, 2012 at 7:34 PM, Andrei Petru Mura mapand...@gmail.com
 wrote:
 id  | username  | attribute| op |   value
 
 
 -++++
   167 | test1 | Password  | := | test1
   168 | test1 | Max-Daily-Session | := | 60
 
  The problem is that every time when I authenticate for the first time per
  hour (because the sqlcounter is resetted hourly), with username test1, I
 can
  access the services given by freeradius an unlimited time.

 That's not right.
 - Did you read the wiki?
 - Did you try to run FR in debug mode? Did it send Session-Timeout
 attribute? Was it calculated correctly?
 - Does your NAS honor Session-Timeout attribute?

 IIRC some NAS (e.g. chillispot) ignores some attributes (e.g.
 Acct-Interim-Interval) if it's too small (e.g = 60 seconds). That
 might be the case in your setup (although the attribute here is
 different).

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

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

Re: Probmels with sqlcounter module in FreeRADIUS 2.1.12

[Fajar A. Nugraha]

On Fri, Jun 29, 2012 at 2:58 PM, Andrei Petru Mura mapand...@gmail.com wrote:

 Fri Jun 29 10:20:06 2012 : Info: [hourlycounter] expand: %{sql:SELECT
 SUM(AcctSessionTime -                 GREATER((1340953200 -
 AcctStartTime::ABSTIME::INT4), 0))                 FROM radacct WHERE
 UserName='test1' AND                 AcctStartTime::ABSTIME::INT4 +
 AcctSessionTime  '1340953200'} -

 //***1 Look here!!!

 Fri Jun 29 10:20:06 2012 : Debug: rlm_sqlcounter: No integer found in string
 
 Fri Jun 29 10:20:06 2012 : Info: ++[hourlycounter] returns noop

 //***1

well ... fix that :)

 So, as you can see from my comments, the Session-Timeout attribute isn't
 present in the radius response debug from the first login. The second time
 when it appears, it is caluculated correctly. For now I think that I have an
 issue with FR, so I don't go further to my chillispot NAS yet.
 Do you have some suggestions?

It MIGHT be a bug, somewhat, in the default sql query in that it
returns no integer if there's no previous accounting record. You can
try running the query manually on a user which already logged in
before and on new user.

If you can contribute a fix, please do. Maybe adding some IF/ELSE to
the sql query?

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

Re: Probmels with sqlcounter module in FreeRADIUS 2.1.12

[Andrei Petru Mura]

  So, as you can see from my comments, the Session-Timeout attribute isn't
  present in the radius response debug from the first login. The second
 time
  when it appears, it is caluculated correctly. For now I think that I
 have an
  issue with FR, so I don't go further to my chillispot NAS yet.
  Do you have some suggestions?

 It MIGHT be a bug, somewhat, in the default sql query in that it
 returns no integer if there's no previous accounting record. You can
 try running the query manually on a user which already logged in
 before and on new user.


 If you can contribute a fix, please do. Maybe adding some IF/ELSE to
 the sql query?


Your suggestion worked. Also, commited the change on git repo.
Thanks!



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

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

Probmels with sqlcounter module in FreeRADIUS 2.1.12

[Andrei Petru Mura]

I'm running FreeRADIUS in conjunction with PostgreSQL 9.1. Snippet from
radiusd.conf:

modules {

...
$INCLUDE sql/postgresql/counter.conf
...

}

in my sql/postgresql/counter.conf, I have the following:

sqlcounter dailycounter {
counter-name = Daily-Session-Time
check-name = Max-Daily-Session
reply-name = Session-Timeout
sqlmod-inst = sql
key = User-Name
reset = hourly

query = SELECT SUM(AcctSessionTime - GREATER((%b -
AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}'
AND AcctStartTime::ABSTIME::INT4 + AcctSessionTime  '%b'
}

Attention!!! The dailycounter has an hourly reset.

in the sites-available/default, under authorize section:

authorize {

...
dailycounter
...
}

Now in my database. I have in radcheck table two rows:

   id  | username  | attribute| op |   value

-++++
 167 | test1 | Password  | := | test1
 168 | test1 | Max-Daily-Session | := | 60

The problem is that every time when I authenticate for the first time per
hour (because the sqlcounter is resetted hourly), with username test1, I
can access the services given by freeradius an unlimited time. The
sqlcounter is ebanled only if after I log in first time, log out and log in
again. If I exceed the time specified in the radcheck table in the first
log in, at the second login (in the same hour), I cannot login again due to
the sqlcounter that says that the time is up.

Question: can anyone help me how to put the right settings in database or
FR' files so that the sqlcounter module will work from the first login?
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Probmels with sqlcounter module in FreeRADIUS 2.1.12

[Alan DeKok]

Andrei Petru Mura wrote:
 Now in my database. I have in radcheck table two rows: 
 
id  | username  | attribute| op |   value
  
 -++++
  167 | test1 | Password  | := | test1

  Please fix that.  Really.  It's been ~6 years that Password := has
been *documented* as being wrong.

  See the FAQ.

  168 | test1 | Max-Daily-Session | := | 60
 
 The problem is that every time when I authenticate for the first time
 per hour (because the sqlcounter is resetted hourly), with username
 test1, I can access the services given by freeradius an unlimited time.

  Blame the NAS.

 The sqlcounter is ebanled only if after I log in first time, log out and
 log in again. If I exceed the time specified in the radcheck table in
 the first log in, at the second login (in the same hour), I cannot login
 again due to the sqlcounter that says that the time is up.
 
 Question: can anyone help me how to put the right settings in database
 or FR' files so that the sqlcounter module will work from the first login?

  Read the debug output.  If the correct Session-Timeout is being
returned, blame the NAS.

  As *always* read the debug output.  We really can't say that enough.
Perhaps putting that in the you have subscribed notice to the list
would make a difference?

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

Re: Probmels with sqlcounter module in FreeRADIUS 2.1.12

[Fajar A. Nugraha]

On Thu, Jun 28, 2012 at 7:34 PM, Andrei Petru Mura mapand...@gmail.com wrote:
    id  |     username      |     attribute            | op |   value

 -++++
  167 | test1                 | Password              | := | test1
  168 | test1                 | Max-Daily-Session | := | 60

 The problem is that every time when I authenticate for the first time per
 hour (because the sqlcounter is resetted hourly), with username test1, I can
 access the services given by freeradius an unlimited time.

That's not right.
- Did you read the wiki?
- Did you try to run FR in debug mode? Did it send Session-Timeout
attribute? Was it calculated correctly?
- Does your NAS honor Session-Timeout attribute?

IIRC some NAS (e.g. chillispot) ignores some attributes (e.g.
Acct-Interim-Interval) if it's too small (e.g = 60 seconds). That
might be the case in your setup (although the attribute here is
different).

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

Re: sqlcounter returning Gigawords?

[Alan DeKok]

YvesDM wrote:
 We 're about to upgrade our radius which is still running 1.1.7
 We use monthly datalimits so we patched the sqlcounter in order to
 make it reply max 4GB of left quota (to avoid wrapping), even if the
 user still has 10GB quota left.
 Of course this results in a logged out user when he reaches a session of 4GB.
 
 As general datatraffic increases we would like to avoid this in our
 new radius setup.
 In the newest version, is there a way to reply gigawords from sqlcounter?
 If not, is there another solution to this?

  The latest version has rlm_expr, which is 64-bit clean.  You can use
it to split the counters into 32-bit pieces.

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

Re: sqlcounter returning Gigawords?

[YvesDM]

On Fri, Apr 1, 2011 at 10:40 AM, Alan DeKok al...@deployingradius.com wrote:

  The latest version has rlm_expr, which is 64-bit clean.  You can use
 it to split the counters into 32-bit pieces.

  Alan DeKok.

Tnx Alan, will check it out.

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

sqlcounter returning Gigawords?

[YvesDM]

Hi,

We 're about to upgrade our radius which is still running 1.1.7
We use monthly datalimits so we patched the sqlcounter in order to
make it reply max 4GB of left quota (to avoid wrapping), even if the
user still has 10GB quota left.
Of course this results in a logged out user when he reaches a session of 4GB.

As general datatraffic increases we would like to avoid this in our
new radius setup.
In the newest version, is there a way to reply gigawords from sqlcounter?
If not, is there another solution to this?

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

Help retrieving sqlcounter values

[Mark D. Montgomery II]

We are working on re-setting up our hotspot (coovachilli/freeradius  
accounting in sql/ldap authentication) better and are trying to setup  
bandwidth limiting and such.



What we seem to be having problems with is how to pull the data out of  
the sqlcounter module to compare the data used to the data limit.



Such as:
if ( amount_user_has_used = limit_allowed)
{
do stuff to block them or whatever
}


We are trying to understand how the counter-name (Max-Usage-Allowed),  
check-name (Check-Max-Usage), and reply-name (Reply-Max-Usage) in the  
counter (UsageCounter) are accessed and evaluated to do the comparison.


When we put
if (counter-name = blah)
in, the function appears to be called and run, but this does not give  
us a valid comparison.
Freeradius debug shows that the counter sql query is running and  
producing a numerical output, but obviously this is not the correct  
way to call it to get the comparison we want.


We have tried such things as counter-name:check-name and such but  
just get errors and the counter query never runs.


Any insight?
We have searched all over and have not been able to get this working yet.

Thanks.

Mark II

--
Mark D. Montgomery II
http://www.techiem2.net


binuwYLzsfcfm.bin
Description: PGP Public Key


pgpcwlB09TPRJ.pgp
Description: PGP Digital Signature
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Please help me with sqlcounter

[frankfang]

I want to use sqlcounter to control the user's traffic usage, and I have
these needs:

1. I have read  http://wiki.freeradius.org/Rlm_sqlcounter the wiki  about
the sqlcounter, and I get %b as the unix time value of beginning of reset
period but how can I set this value? I want to sqlcounter begin count at
a specific time such as the register time.. Is it possible?

2. When user's traffic usage over a value, I hope the server will disconnect
the connected user immediately, Is it possible for doing this?

I have read some article about sqlcounter, but I'm still confused about
these questions, can anyone help me?

I'm very appreciate for your help

--
View this message in context: 
http://freeradius.1045715.n5.nabble.com/Please-help-me-with-sqlcounter-tp4192991p4192991.html
Sent from the FreeRadius - User mailing list archive at Nabble.com.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Please help me with sqlcounter

[Suman Dash]

I am trying to do the same in sqlcounter but looks like the %b is hard 
coded and there is no way to make it dynamically read from database. I 
have tried using custom sqlcounter but it doe not escapes properly.


Anyone effort in commenting on this thread will be highly appreciable as 
it will enable the user to do a custom time based session accounting 
instead of fixed 1 ~ 30 date accounting.


Best Regards
Suman


On 3/21/2011 11:54 AM, frankfang wrote:

I want to use sqlcounter to control the user's traffic usage, and I have
these needs:

1. I have read  http://wiki.freeradius.org/Rlm_sqlcounter the wiki  about
the sqlcounter, and I get %b as the unix time value of beginning of reset
period but how can I set this value? I want to sqlcounter begin count at
a specific time such as the register time.. Is it possible?

2. When user's traffic usage over a value, I hope the server will disconnect
the connected user immediately, Is it possible for doing this?

I have read some article about sqlcounter, but I'm still confused about
these questions, can anyone help me?

I'm very appreciate for your help

--
View this message in context: 
http://freeradius.1045715.n5.nabble.com/Please-help-me-with-sqlcounter-tp4192991p4192991.html
Sent from the FreeRadius - User mailing list archive at Nabble.com.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


__ Information from ESET NOD32 Antivirus, version of virus signature 
database 5924 (20110303) __

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




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

Re: Changing radgroup with a sqlcounter ?

[Arrgghh]

Hello JDL-4,

I use Mysql and sorry the quota is indeed 100MB.

Thank you very much for this great explanation, I think that's pretty what I
want to do.

Could you let me know where this group_membership_query is called ? Do i
need to put it in the authorize section of freeradius ?

Thank you again, you are really helpful !! 
-- 
View this message in context: 
http://freeradius.1045715.n5.nabble.com/Changing-radgroup-with-a-sqlcounter-tp3270524p3274236.html
Sent from the FreeRadius - User mailing list archive at Nabble.com.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Changing radgroup with a sqlcounter ?

[Arrgghh]

Anyone ?
-- 
View this message in context: 
http://freeradius.1045715.n5.nabble.com/Changing-radgroup-with-a-sqlcounter-tp3270524p3273562.html
Sent from the FreeRadius - User mailing list archive at Nabble.com.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Changing radgroup with a sqlcounter ?

[JDL]

If I understand you correctly, instead of kicking the user with the 
sqlcounter, you simply want to move them into a different group.  It should 
pretty straight forward.  You need to set read_groups = yes in your sql 
module.  I would recommend that you put the check and reply attributes in the 
database as well.  You should be able to use the default tables and queries for 
this (i.e. authorize_group_check_query and authorize_group_reply_query).  See 
the FreeRADIUS schema and docs.

I am not sure what 100mo means.  Do you mean 100MB (Megabytes) per month?

The only custom part would be to write a proper group_membership_query that 
returns the correct group name.  This becomes more an SQL issue than a radius 
issue.  You also did not indicate what database type you are using.  If you are 
using MySQL, it should be something like the following.  You will have to tweak 
it to get the results you want.  Test the SQL query directly against your 
database (i.e. outside of FreeRADIUS).  Once you get the results you want, use 
that query as your group_membership_query.

group_membership_query = SELECT 
IF((SUM(acctinputoctets)+SUM(acctoutputoctets))104857600, '512Kgroup', 
'128Kgroup') FROM radacct WHERE username='%{%k}' AND 
acctstarttime=DATE_FORMAT(CURDATE(), '%%Y-%%m-01 00:00:00')

Note:  The double percent signs in the DATE_FORMAT command causes FreeRADIUS to 
escape the percent sign.  FreeRADIUS converts this to a single percent sign 
before sending the query to the database.  For testing the above query directly 
against MySQL, you would need to use something like the following.  Notice the 
single percent signs.

SELECT IF((SUM(acctinputoctets)+SUM(acctoutputoctets))104857600, '512Kgroup', 
'128Kgroup') FROM radacct WHERE username='testuser' AND 
acctstarttime=DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00')

See http://dev.mysql.com/doc/ if you need further assistance with the MySQL IF 
operator or any other part of the query.

Note that this does not use the sqlcounter at all.  It is simply changing the 
group membership based on the SQL query.

Hope this helps.  Good luck!

Jim L.


On Nov 18, 2010, at 5:23 AM, Arrgghh wrote:

 
 Hello,
 
 I am working on a radius config for a hotspot. I already configured a script
 that kick the user when the quota exceed thanks to some very useful posts in
 the coova forum.
 
 I used this counter :
 
 
 sqlcounter noresetBytecounter {
counter-name = Total-Max-Octets
check-name = Max-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = never
query = SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
 FROM radacct WHERE UserName='%{%k}'
  }
 
 
 My goal is that a user belongs to a standard group with a 512kbps bandwith
 and a quota of 100mo. Then when he exceeds his quota, he is switched to a
 128 kbps group.
 
 I know how to setup groups that limits bandwidth and volume. What I don't
 know is how to set up a counter that instead of kicking the user out of the
 network when he exceeds his quota switch him to another radgroup.
 
 Any clue how i can do it ?
 
 Thank you very much,
 -- 
 View this message in context: 
 http://freeradius.1045715.n5.nabble.com/Changing-radgroup-with-a-sqlcounter-tp3270524p3270524.html
 Sent from the FreeRadius - User mailing list archive at Nabble.com.
 -
 List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


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

Changing radgroup with a sqlcounter ?

[Arrgghh]

Hello,

I am working on a radius config for a hotspot. I already configured a script
that kick the user when the quota exceed thanks to some very useful posts in
the coova forum.

I used this counter :


sqlcounter noresetBytecounter {
counter-name = Total-Max-Octets
check-name = Max-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = never
query = SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
FROM radacct WHERE UserName='%{%k}'
  }


My goal is that a user belongs to a standard group with a 512kbps bandwith
and a quota of 100mo. Then when he exceeds his quota, he is switched to a
128 kbps group.

I know how to setup groups that limits bandwidth and volume. What I don't
know is how to set up a counter that instead of kicking the user out of the
network when he exceeds his quota switch him to another radgroup.

Any clue how i can do it ?

Thank you very much,
-- 
View this message in context: 
http://freeradius.1045715.n5.nabble.com/Changing-radgroup-with-a-sqlcounter-tp3270524p3270524.html
Sent from the FreeRadius - User mailing list archive at Nabble.com.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: sqlcounter help

[Mordor Networks]

Dear Sir
this is the log in debugging mode , in query in using
query = SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct
WHERE UserName='%{%k}'
it works just fine

Waking up in 5 seconds...
rad_recv: Access-Request packet from host 192.168.22.2:52098, id=20,
length=114
Service-Type = Framed-User
Framed-Protocol = PPP
User-Name = wassim
CHAP-Challenge = 0x1d546fdf501eaf2909814861d0b4bd2fa8d51ae91946
RP-HURL = \225\360[.\303\217v\241Ae\366\373\340\014\025\334G
Calling-Station-Id = 00:23:CD:10:99:A7
NAS-IP-Address =
NAS-Port = 25
  Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 10
  modcall[authorize]: module preprocess returns ok for request 10
  rlm_chap: Setting 'Auth-Type := CHAP'
  modcall[authorize]: module chap returns ok for request 10
  modcall[authorize]: module mschap returns noop for request 10
rlm_realm: No '@' in User-Name = wassim, looking up realm NULL
rlm_realm: No such realm NULL
  modcall[authorize]: module suffix returns noop for request 10
radius_xlat:  'wassim'
rlm_sql (sql): sql_set_user escaped user -- 'wassim'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op   FROM
radcheck   WHERE Username = 'wassim'   ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 1
rlm_sql_mysql: query:  SELECT id, UserName, Attribute, Value, op
FROM radcheck   WHERE Username = 'wassim'   ORDER BY id
radius_xlat:  'SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
FROM radgroupcheck,usergroup WHERE usergroup.Username = 'wassim' AND
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
rlm_sql_mysql: query:  SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
FROM radgroupcheck,usergroup WHERE usergroup.Username = 'wassim' AND
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id
radius_xlat:  'SELECT id, UserName, Attribute, Value, op   FROM
radreply   WHERE Username = 'wassim'   ORDER BY id'
rlm_sql_mysql: query:  SELECT id, UserName, Attribute, Value, op
FROM radreply   WHERE Username = 'wassim'   ORDER BY id
radius_xlat:  'SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
FROM radgroupreply,usergroup WHERE usergroup.Username = 'wassim' AND
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql_mysql: query:  SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
FROM radgroupreply,usergroup WHERE usergroup.Username = 'wassim' AND
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id
rlm_sql (sql): Released sql socket id: 1
  modcall[authorize]: module sql returns ok for request 10
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
FROM radacct WHERE UserName='%{User-Name}''
radius_xlat:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
radacct WHERE UserName='wassim''
sqlcounter_expand:  '%{sql:SELECT
(SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='wassim'}'
radius_xlat: Running registered xlat function of module sql for string
'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='wassim''
rlm_sql (sql): - sql_xlat
radius_xlat:  'wassim'
rlm_sql (sql): sql_set_user escaped user -- 'wassim'
radius_xlat:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
radacct WHERE UserName='wassim''
radius_xlat:  '/usr/local/var/log/radius/sqltrace.sql'
rlm_sql (sql): Reserving sql socket id: 0
rlm_sql_mysql: query:  SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
FROM radacct WHERE UserName='wassim'
rlm_sql (sql): - sql_xlat finished
rlm_sql (sql): Released sql socket id: 0
radius_xlat:  '2080402569'
rlm_sqlcounter: (Check item - counter) is less than zero
rlm_sqlcounter: Rejected user wassim, check_item=1000, counter=2080402569
  modcall[authorize]: module chillispot_max_bytes_daily returns reject for
request 10
modcall: leaving group authorize (returns reject) for request 10
Delaying request 10 for 5 seconds
Finished request 10
Going to the next request

Now if i replace the query with :
sqlcounter chillispot_max_bytes_daily {
counter-name = ChilliSpot-Max-Total-Octets
check-name = ChilliSpot-Max-Total-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = daily
query = SELECT SUM(AcctInputOctets - GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0)) + SUM(AcctOutputOctets -GREATEST
((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE
UserName='%{%k}' AND AcctStartTime  FROM_UNIXTIME('%b')
#query = SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct
WHERE UserName='%{%k}'
}
which should reset daily

sqlcounter help

[Mordor Networks]

Dear Gentlemen
I'm having a problem with my daily count :

sqlcounter chillispot_max_bytes_daily {
counter-name = ChilliSpot-Max-Total-Octets
check-name = ChilliSpot-Max-Total-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = daily
query = SELECT SUM(AcctInputOctets - GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0))
FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) +
AcctSessionTime  '%b'
}

It is disconnecting the user if limit is reached but user can connect again
like it is not stopping the account, now if i replace the query with #query
= SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='%{%k}' that will disconnect the account and it wont be able to
connect again but the problem here with reset , how i will be able to reset
daily?
Best regards
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: sqlcounter help

[Alan DeKok]

Mordor Networks wrote:
 It is disconnecting the user if limit is reached but user can connect
 again like it is not stopping the account, now if i replace the query
 with #query = SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
 radacct WHERE UserName='%{%k}' that will disconnect the account and it
 wont be able to connect again but the problem here with reset , how i
 will be able to reset daily?

  As always, run it in debugging mode to see what it's doing.

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

sqlcounter is not kicking users

[ziko]

Hello,  I configured sqlcounter on my radius server and trying to limit hotspot 
users time. But when time expires, Max-All-Session is not kicking users. (but 
when they log out, then they cant log in)  How can i solve this problem?

I am using:
Freeradius 2
CentOS 5.3
Mikrotik as NAS.

My sqlcounter.conf:

sqlcounter noresetcounter {
counter-name = Max-All-Session-Time 
check-name = Max-All-Session 
sqlmod-inst = sql 
key = User-Name 
reset = never 
query = SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE 
UserName='%{%k}' 


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

Re: sqlcounter is not kicking users

[Alan DeKok]

ziko wrote:
 Hello,  I configured sqlcounter on my radius server and trying to limit
 hotspot users time. But when time expires, Max-All-Session is not
 kicking users. (but when they log out, then they cant log in)  How can i
 solve this problem?

  Use a NAS that enforces Session-Timeout.

  This isn't a problem on the RADIUS server.

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

Re: sqlcounter is not kicking users

[ziko]

Thank you for answer. I decided to use session-timeout  with Max-All-Session.  
Session-timeout kicks users when time expires, but then they can log in again. 
and max-all-session ant session-timeout alliance works great :)






From: Alan DeKok al...@deployingradius.com
To: FreeRadius users mailing list freeradius-users@lists.freeradius.org
Sent: Fri, August 27, 2010 2:46:46 PM
Subject: Re: sqlcounter is not kicking users

ziko wrote:
 Hello,  I configured sqlcounter on my radius server and trying to limit
 hotspot users time. But when time expires, Max-All-Session is not
 kicking users. (but when they log out, then they cant log in)  How can i
 solve this problem?

  Use a NAS that enforces Session-Timeout.

  This isn't a problem on the RADIUS server.

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



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

Re: SqlCounter reload after initial authentication

[liran tal]

As Alan said this happens since the remaining time was already sent to the
NAS which
causes the disconnection, although if the user added more time then after he
was initially disconnected by the NAS
the user may still login again for the remaining time (which is the delta -
what he already added - what he already finihsed).

Seems like a reasonable work-around for the user to simply re-connect for
the remaining time.


Regards,
Liran.


On Thu, Aug 12, 2010 at 10:07 AM, tadi...@verizon.net wrote:


 I'm using Freeradius + Chillispot+MySql for hotspot. Sqlcounter
 noresetcounter works fine for prepaid access time, however the counter is
 loaded only once when the user first authenticate.
 This means that even if Max-All-Session changes after initial logon (as it
 happens when the user adds more credit) the user is session is still
 terminated at the end of the initial count down.
 How can I force Freeradius sqlcounter to re-load and begin counting down
 when Max-All-Session is updated after initial authentication? If this is not
 possible any workaround or hacks to accomplish this behaviour will be
 greatly appreciated.
 Thanks.
 -
 List info/subscribe/unsubscribe? See
 http://www.freeradius.org/list/users.html

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

SqlCounter reload after initial authentication

[tadiguy]

I'm using Freeradius + Chillispot+MySql for hotspot. Sqlcounter noresetcounter 
works fine for prepaid access time, however the counter is loaded only once 
when the user first authenticate.
This means that even if Max-All-Session changes after initial logon (as it 
happens when the user adds more credit) the user is session is still terminated 
at the end of the initial count down.
How can I force Freeradius sqlcounter to re-load and begin counting down when 
Max-All-Session is updated after initial authentication? If this is not 
possible any workaround or hacks to accomplish this behaviour will be greatly 
appreciated. 
Thanks.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: SqlCounter reload after initial authentication

[Alan DeKok]

tadi...@verizon.net wrote:
 I'm using Freeradius + Chillispot+MySql for hotspot. Sqlcounter 
 noresetcounter works fine for prepaid access time, however the counter is 
 loaded only once when the user first authenticate.
 This means that even if Max-All-Session changes after initial logon (as it 
 happens when the user adds more credit) the user is session is still 
 terminated at the end of the initial count down.
 How can I force Freeradius sqlcounter to re-load and begin counting down when 
 Max-All-Session is updated after initial authentication? If this is not 
 possible any workaround or hacks to accomplish this behaviour will be greatly 
 appreciated. 

  You can't.  The SQL counter module returns a Session-Timeout attribute
to the NAS, and the *NAS* is the one enforcing the session length.  If
you update the database, the NAS doesn't know this, and still enforces
the session length.

  Read the NAS documentation to see how it can extend existing sessions.
 If the documentation doesn't describe how to do it, it's impossible.

  And it's *not* a FreeRADIUS issue.

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

Re: reset sqlcounter on the same day for each user

[Anton Zaytsev]

On Fri, Jun 4, 2010 at 11:52 AM, Антон Зайцев anton.zajt...@gmail.comwrote:

 Hello everyone.

 Need some help. I want to limit my users with MB traffic monthly. I have
 set up sqlcounter and it works great. But it resets in month starting user
 first connect to NAS. Users can register and connect on different day during
 the month and then it resets differently for each user.

 And this is the question. How can i reset counter on the certain day for
 certain user(example last day of month).And then counter can resets monthly
 for all users beginning from the first day of month.

 Maybe I need some attributes or ...

 Thanks



Can anybody help with this.
As I understand counters reset only hourly monthly or weekly.
I did search on mail archive and nothing useful have found.
Maybe give me some directions to look for to resolve this problem
Maybe I should use expiration attribute or choose else way.

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

reset sqlcounter on the same day for each user

[Антон Зайцев]

Hello everyone.

Need some help. I want to limit my users with MB traffic monthly. I have set
up sqlcounter and it works great. But it resets in month starting user first
connect to NAS. Users can register and connect on different day during the
month and then it resets differently for each user.

And this is the question. How can i reset counter on the certain day for
certain user(example last day of month).And then counter can resets monthly
for all users beginning from the first day of month.

Maybe I need some attributes or ...

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

sqlcounter and ldap backend

[Carlos Antonio Gómez Brizulela]

Hello

I have installed freeradius + LDAP backend. I need to limit the
connection time per user. I found sqlcounter as a solution but I have
two problems:

1 - I need to take the values: Max-Daily-Session and
Max-Monthly-Session from LDAP and not from mysql DB.

2 - I need to terminate the connection when it meets the maximum
connection time.

Best regards,

Carlos A.

Sorry my English, I speak Spanish.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Session-Octets-Limit and sqlcounter

[Charles]

Do you manage to fix your problem?
Kindly share your solution. I am interested in knowing how I can configure my 
freeradius to limit users by both time and  max download size

e.g. 1usd for 1 hour or 20MB (whichever comes first).

charles
  - Original Message - 
  From: Hamid Reza Hasani 
  To: freeradius-users@lists.freeradius.org 
  Sent: Sunday, November 29, 2009 5:45 PM
  Subject: Session-Octets-Limit and sqlcounter


  Hi, 

  I'm using freeradius-2.1.6, and I'm going to make a download limitation for 
my users. I used sqlcounter module and config it as follow: 

  sqlcounter monthlydownload { 

  counter-name = Monthly-Download-Byte 

  check-name = Max-Monthly-Download 

  reply-name = Session-Octets-Limit 

  key = User-Name 

  sqlmod-inst = sql 

  query = SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM radacct WHERE 
UserName='%{%k}' AND AcctStartTime  FROM_UNIXTIME('%b') 

  reset = monthly 

  safe-characters = 
@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: / 

  } 


  According to the log messages, it shows anything is OK: 


  rlm_sqlcounter: Sent Reply-Item for user hrh, Type=Session-Octets-Limit, 
value=600106145 

  ++[monthlydownload] returns ok 


  But in proceeding it shows me what it sent, and there isn't 
Session-Octets-Limit! as you can see bellow: 


  Sending Access-Accept of id 222 to 127.0.0.1 port 32769 

  Framed-IP-Address := 20.20.20.1 

  Framed-IP-Netmask := 255.255.255.0 

  Session-Timeout = 5460 

  Finished request 0. 


  Where is my problem? 

  more log is available at the end of message. 

  Thanks for you helps. 

  --

  Ya Ali


  Hamid Reza Hasani




  More Log:

  Module: Linked to module rlm_sqlcounter 

  Module: Instantiating monthlydownload 

  sqlcounter monthlydownload { 

  counter-name = Monthly-Download-Byte 

  check-name = Max-Monthly-Download 

  reply-name = Session-Octets-Limit 

  key = User-Name 

  sqlmod-inst = sql 

  query = SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM radacct WHERE 
UserName='%{%k}' AND AcctStartTime  FROM_UNIXTIME('%b') 

  reset = monthly 

  safe-characters = 
@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: / 

  } 

  rlm_sqlcounter: Reply attribute Session-Octets-Limit is number 3009 

  rlm_sqlcounter: Counter attribute Monthly-Download-Byte is number 11273 

  rlm_sqlcounter: Check attribute Max-Monthly-Download is number 11274 

  rlm_sqlcounter: Current Time: 1259506851 [2009-11-29 18:30:51], Next reset 
1259613000 [2009-12-01 00:00:00] 

  rlm_sqlcounter: Current Time: 1259506851 [2009-11-29 18:30:51], Prev reset 
1257021000 [2009-11-01 00:00:00] 

  Module: Checking preacct {...} for more modules to load 

  . 

  . 

  . 

  rlm_sqlcounter: Entering module authorize code 

  sqlcounter_expand: 'SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM radacct 
WHERE UserName='%{User-Name}' AND AcctStartTime  FROM_UNIXTIME('1257021000')' 

  [monthlydownload] expand: SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM 
radacct WHERE UserName='%{User-Name}' AND AcctStartTime  
FROM_UNIXTIME('1257021000') - SELECT SUM(AcctOutputOctets+AcctInputOctets) 
FROM radacct WHERE UserName='hrh' AND AcctStartTime  
FROM_UNIXTIME('1257021000') 

  sqlcounter_expand: '%{sql:SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM 
radacct WHERE UserName='hrh' AND AcctStartTime  FROM_UNIXTIME('1257021000')}' 

  [monthlydownload] sql_xlat 

  [monthlydownload] expand: %{User-Name} - hrh 

  [monthlydownload] sql_set_user escaped user -- 'hrh' 

  [monthlydownload] expand: SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM 
radacct WHERE UserName='hrh' AND AcctStartTime  FROM_UNIXTIME('1257021000') - 
SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM radacct WHERE UserName='hrh' 
AND AcctStartTime  FROM_UNIXTIME('1257021000') 

  [monthlydownload] expand: /usr/var/log/radius/sqltrace.sql - 
/usr/var/log/radius/sqltrace.sql 

  rlm_sql (sql): Reserving sql socket id: 3 

  rlm_sql_mysql: query: SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM 
radacct WHERE UserName='hrh' AND AcctStartTime  FROM_UNIXTIME('1257021000') 

  [monthlydownload] sql_xlat finished 

  rlm_sql (sql): Released sql socket id: 3 

  [monthlydownload] expand: %{sql:SELECT SUM(AcctOutputOctets+AcctInputOctets) 
FROM radacct WHERE UserName='hrh' AND AcctStartTime  
FROM_UNIXTIME('1257021000')} - 100213 

  rlm_sqlcounter: Check item is greater than query result 

  rlm_sqlcounter: Authorized user hrh, check_item=6, counter=100213 

  rlm_sqlcounter: Sent Reply-Item for user hrh, Type=Session-Octets-Limit, 
value=600106145 

  ++[monthlydownload] returns ok 

  . 

  . 

  . 

  Sending Access-Accept of id 222 to 127.0.0.1 port 32769 

  Framed-IP-Address := 20.20.20.1 

  Framed-IP-Netmask := 255.255.255.0 

  Session-Timeout = 5460 

  Finished request 0. 

  Going to the next request 

  Waking up in 4.9 seconds. 




   5 free 
Domains

Re: Session-Octets-Limit and sqlcounter

[Alan DeKok]

Charles wrote:
 Do you manage to fix your problem?
 Kindly share your solution. I am interested in knowing how I can
 configure my freeradius to limit users by both time and  max download size

  As with *ALL* of these questions:

Does your NAS support this?

  Go read the NAS documentation.

  FreeRADIUS is *not* a firewall.  The NAS is a firewall.  FreeRADIUS
simply tells the NAS which rules to apply.

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

Re: Session-Octets-Limit and sqlcounter

[Charles]

Thanks Alan for your help.

My NAS is m0n0wall (http://m0n0.ch/wall/features.php) and its captive portal 
features are briefly outlined here: http://doc.m0n0.ch/handbook/ch12s06.html 
.

It mentions bandwidth setings.

In my current setup, I use session_timeout and it works very well but I have 
users who download heavily within an hour. So I would like to limit using 
both Session_Timeout and max octes (which ever is reached first).


Kindly help me by checking the features to see if I can use this NAS for 
this purpose. I dont know what they mean by Bandwidth Settings


Charles




- Original Message - 
From: Alan DeKok al...@deployingradius.com

To: FreeRadius users mailing list freeradius-users@lists.freeradius.org
Sent: Monday, November 30, 2009 3:04 PM
Subject: Re: Session-Octets-Limit and sqlcounter



Charles wrote:

Do you manage to fix your problem?
Kindly share your solution. I am interested in knowing how I can
configure my freeradius to limit users by both time and  max download 
size


 As with *ALL* of these questions:

Does your NAS support this?

 Go read the NAS documentation.

 FreeRADIUS is *not* a firewall.  The NAS is a firewall.  FreeRADIUS
simply tells the NAS which rules to apply.

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


__
Buy a domain : http://www.1and1.com/?k_id=25085883 



__
Buy a domain : http://www.1and1.com/?k_id=25085883
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Session-Octets-Limit and sqlcounter

[Alan DeKok]

Charles wrote:
 My NAS is m0n0wall (http://m0n0.ch/wall/features.php) and its captive
 portal features are briefly outlined here:
 http://doc.m0n0.ch/handbook/ch12s06.html .
 It mentions bandwidth setings.

  How nice.

 In my current setup, I use session_timeout and it works very well but I
 have users who download heavily within an hour. So I would like to limit
 using both Session_Timeout and max octes (which ever is reached first).

  You already said that.

 Kindly help me by checking the features to see if I can use this NAS for
 this purpose.

  Why?

 I dont know what they mean by Bandwidth Settings

  So... ask the Monowall people.  We didn't write Monowall, and we
didn't write it's documentation.  if you don't understand it, ask them
for help.

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

Re: Session-Octets-Limit and sqlcounter

[Charles]

Thanks Allan,

I think you are right, I will ask in the monowall forum.
Just that the forum is not very active on Captive Portal issues.

Could you be kind to suggest a NAS that you know which can help me achieve 
my goal?


Thanks in advance - I know I am asking too much.

Charles

- Original Message - 
From: Alan DeKok al...@deployingradius.com

To: FreeRadius users mailing list freeradius-users@lists.freeradius.org
Sent: Monday, November 30, 2009 4:08 PM
Subject: Re: Session-Octets-Limit and sqlcounter



Charles wrote:

My NAS is m0n0wall (http://m0n0.ch/wall/features.php) and its captive
portal features are briefly outlined here:
http://doc.m0n0.ch/handbook/ch12s06.html .
It mentions bandwidth setings.


 How nice.


In my current setup, I use session_timeout and it works very well but I
have users who download heavily within an hour. So I would like to limit
using both Session_Timeout and max octes (which ever is reached first).


 You already said that.


Kindly help me by checking the features to see if I can use this NAS for
this purpose.


 Why?


I dont know what they mean by Bandwidth Settings


 So... ask the Monowall people.  We didn't write Monowall, and we
didn't write it's documentation.  if you don't understand it, ask them
for help.

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


__
Buy a domain : http://www.1and1.com/?k_id=25085883 



__
Buy a domain : http://www.1and1.com/?k_id=25085883
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Session-Octets-Limit and sqlcounter

[YvesDM]

On Mon, Nov 30, 2009 at 4:44 PM, Charles char...@goma.kivu-online.com wrote:
 Thanks Allan,

 I think you are right, I will ask in the monowall forum.
 Just that the forum is not very active on Captive Portal issues.

 Could you be kind to suggest a NAS that you know which can help me achieve
 my goal?

 Thanks in advance - I know I am asking too much.

 Charles



Charles,

m0n0wall has an option in the CP settings to re-authenticate every
minute. It makes your life real easy in setting up radius.
Just set a check item in radcheck containing your datacap and set sql
counter appropiate.
But as suggested, the m0n0wall list will definately help you out.

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

Re: Session-Octets-Limit and sqlcounter

[tnt]

 I think you are right, I will ask in the monowall forum.
 Just that the forum is not very active on Captive Portal issues.

 Could you be kind to suggest a NAS that you know which can help me achieve
 my goal?

 Thanks in advance - I know I am asking too much.

Yes you do. Now go and read monowall changelog and all will be answered.

Ivan Kalik

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

Re: Session-Octets-Limit and sqlcounter

[Charles]

Thanks YvesDM,

I saw it - the attribute - and my problem is now solved
Many thinks! the Last line !

1.236 (09/30/2009)
 a.. fixed a security issue in the DHCP client (CVE-2009-0692)
 b.. captive portal fixes (jdegraeve):
   a.. changed RADIUS timeout/maxtries from 5/3 to 3/2 reducing failover 
time from 30 to 15 seconds
   b.. added RADIUS attribute support for: 
ChilliSpot-Bandwidth-Max-Up/ChilliSpot-Bandwidth-Max-Down




- Original Message - 
From: YvesDM ydm...@gmail.com

To: FreeRadius users mailing list freeradius-users@lists.freeradius.org
Sent: Monday, November 30, 2009 11:09 PM
Subject: Re: Session-Octets-Limit and sqlcounter


On Mon, Nov 30, 2009 at 4:44 PM, Charles char...@goma.kivu-online.com 
wrote:

Thanks Allan,

I think you are right, I will ask in the monowall forum.
Just that the forum is not very active on Captive Portal issues.

Could you be kind to suggest a NAS that you know which can help me 
achieve

my goal?

Thanks in advance - I know I am asking too much.

Charles




Charles,

m0n0wall has an option in the CP settings to re-authenticate every
minute. It makes your life real easy in setting up radius.
Just set a check item in radcheck containing your datacap and set sql
counter appropiate.
But as suggested, the m0n0wall list will definately help you out.

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


__
Buy a domain : http://www.1and1.com/?k_id=25085883 



__
Buy a domain : http://www.1and1.com/?k_id=25085883
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Session-Octets-Limit and sqlcounter

[Hamid Reza Hasani]

Hi,

I'm using freeradius-2.1.6, and I'm going to make a download limitation for
my users. I used sqlcounter module and config it as follow:

sqlcounter monthlydownload {

counter-name = Monthly-Download-Byte

check-name = Max-Monthly-Download

reply-name = Session-Octets-Limit

key = User-Name

sqlmod-inst = sql

query = SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM radacct WHERE
UserName='%{%k}' AND AcctStartTime  FROM_UNIXTIME('%b')

reset = monthly

safe-characters =
@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /

}

 According to the log messages, it shows anything is OK:

 rlm_sqlcounter: Sent Reply-Item for user hrh, Type=Session-Octets-Limit,
value=600106145

++[monthlydownload] returns ok

 But in proceeding it shows me what it sent, and there isn't
Session-Octets-Limit! as you can see bellow:

 Sending Access-Accept of id 222 to 127.0.0.1 port 32769

Framed-IP-Address := 20.20.20.1

Framed-IP-Netmask := 255.255.255.0

Session-Timeout = 5460

Finished request 0.

 Where is my problem?

more log is available at the end of message.

Thanks for you helps.

--

Ya Ali

 Hamid Reza Hasani


 More Log:

Module: Linked to module rlm_sqlcounter

Module: Instantiating monthlydownload

sqlcounter monthlydownload {

counter-name = Monthly-Download-Byte

check-name = Max-Monthly-Download

reply-name = Session-Octets-Limit

key = User-Name

sqlmod-inst = sql

query = SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM radacct WHERE
UserName='%{%k}' AND AcctStartTime  FROM_UNIXTIME('%b')

reset = monthly

safe-characters =
@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /

}

rlm_sqlcounter: Reply attribute Session-Octets-Limit is number 3009

rlm_sqlcounter: Counter attribute Monthly-Download-Byte is number 11273

rlm_sqlcounter: Check attribute Max-Monthly-Download is number 11274

rlm_sqlcounter: Current Time: 1259506851 [2009-11-29 18:30:51], Next reset
1259613000 [2009-12-01 00:00:00]

rlm_sqlcounter: Current Time: 1259506851 [2009-11-29 18:30:51], Prev reset
1257021000 [2009-11-01 00:00:00]

Module: Checking preacct {...} for more modules to load

.

.

.

rlm_sqlcounter: Entering module authorize code

sqlcounter_expand: 'SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM
radacct WHERE UserName='%{User-Name}' AND AcctStartTime 
FROM_UNIXTIME('1257021000')'

[monthlydownload] expand: SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM
radacct WHERE UserName='%{User-Name}' AND AcctStartTime 
FROM_UNIXTIME('1257021000') - SELECT SUM(AcctOutputOctets+AcctInputOctets)
FROM radacct WHERE UserName='hrh' AND AcctStartTime 
FROM_UNIXTIME('1257021000')

sqlcounter_expand: '%{sql:SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM
radacct WHERE UserName='hrh' AND AcctStartTime 
FROM_UNIXTIME('1257021000')}'

[monthlydownload] sql_xlat

[monthlydownload] expand: %{User-Name} - hrh

[monthlydownload] sql_set_user escaped user -- 'hrh'

[monthlydownload] expand: SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM
radacct WHERE UserName='hrh' AND AcctStartTime  FROM_UNIXTIME('1257021000')
- SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM radacct WHERE
UserName='hrh' AND AcctStartTime  FROM_UNIXTIME('1257021000')

[monthlydownload] expand: /usr/var/log/radius/sqltrace.sql -
/usr/var/log/radius/sqltrace.sql

rlm_sql (sql): Reserving sql socket id: 3

rlm_sql_mysql: query: SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM
radacct WHERE UserName='hrh' AND AcctStartTime  FROM_UNIXTIME('1257021000')


[monthlydownload] sql_xlat finished

rlm_sql (sql): Released sql socket id: 3

[monthlydownload] expand: %{sql:SELECT SUM(AcctOutputOctets+AcctInputOctets)
FROM radacct WHERE UserName='hrh' AND AcctStartTime 
FROM_UNIXTIME('1257021000')} - 100213

rlm_sqlcounter: Check item is greater than query result

rlm_sqlcounter: Authorized user hrh, check_item=6, counter=100213

rlm_sqlcounter: Sent Reply-Item for user hrh, Type=Session-Octets-Limit,
value=600106145

++[monthlydownload] returns ok

.

.

.

Sending Access-Accept of id 222 to 127.0.0.1 port 32769

Framed-IP-Address := 20.20.20.1

Framed-IP-Netmask := 255.255.255.0

Session-Timeout = 5460

Finished request 0.

Going to the next request

Waking up in 4.9 seconds.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Session-Octets-Limit and sqlcounter

[tnt]

 I'm using freeradius-2.1.6, and I'm going to make a download limitation
 for
 my users. I used sqlcounter module and config it as follow:

  According to the log messages, it shows anything is OK:

  Where is my problem?

 rlm_sqlcounter: Reply attribute Session-Octets-Limit is number 3009

Did you actually read the comments in raddb/dictionary?

Ivan Kalik

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

sqlcounter / end session at the end of the month

[YvesDM]

Hi,

I'm having issues with octet accounting. Users are monthly limited in
octets, not in time.
The problem i have is when a user logs in on the last day of the month
and stays online for 3 days then there's 2days within the new month
but the accounting counts within the previous month, which we don't
like :-)

So I was thinking about adding a counter module which calculates a
session-timeout at the last second of the month so users get logged
out just before the new month starts.
But, I don't know how to set the check-item. If I use max-all-session
and a  monthly reset I need a check item that equals the end of the
month, which can be 28 days, 30 days or 31 days.
Any known solutions to this?


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

Re: sqlcounter / end session at the end of the month

[Alan DeKok]

YvesDM wrote:
 I'm having issues with octet accounting. Users are monthly limited in
 octets, not in time.
 The problem i have is when a user logs in on the last day of the month
 and stays online for 3 days then there's 2days within the new month
 but the accounting counts within the previous month, which we don't
 like :-)

  So... run a query at the end of each month that grabs the current
counters for each user.  Bill them for that, and remember what that
number is in a new column in the table.

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

Re: authentication failed because sqlcounter...

[Nizar Zulmi]

the problem solved it just because 30 second less than one minute, i've tried 
using Max-All-Sesion:=3600 and it works..


--- On Sat, 5/9/09, Nizar Zulmi siapa_bilang_emailku_panj...@yahoo.com wrote:

From: Nizar Zulmi siapa_bilang_emailku_panj...@yahoo.com
Subject: Re: authentication failed because sqlcounter...
To: t...@kalik.net, FreeRadius users mailing list 
freeradius-users@lists.freeradius.org
Date: Saturday, May 9, 2009, 2:03 PM

i am using freeradius 1.1.7, not that old rite??? i've tried using := operator 
and cleartext-password but still doesn't work..this bellow is my radcheck 
table..
++--++++
| id | UserName | Attribute  | op | Value  |
++--++++
|  1 | nizar    | Password   | == | nizar  |
|  2 | nizar1   | Password   | == | nizar1 |
|  6 | tes  | Max-All-Session    | == | 90 |
|  4 | tes  | Password   |
 == | tes    |
|  7 | denizaro | Cleartext-Password | := | 123456 |
|  8 | denizaro | Max-All-Session    | := | 30 |
++--++++
6 rows in set (0.00 sec)

i try to log in with user denizaro first time before i add attribute 
max-all-session its succesfully logged in, but after i adding max-all-session 
attribut its failed..
whats happen??

--- On Sat, 5/9/09, Ivan Kalik t...@kalik.net wrote:

From: Ivan Kalik t...@kalik.net
Subject: Re: authentication failed because sqlcounter...
To: FreeRadius users mailing list freeradius-users@lists.freeradius.org
Date: Saturday, May 9, 2009, 2:51 AM

 mm confusing..i just enable the sqlcounter in
 radiusd.conf. i just let it
 as default no change i made in noresetcounter module. then i adding
 noresetcounter in authorize and instantiate section.
 i have defined 1user named tes and password tes has loged in normally
 before i add attribut max-all-session in the table radcheck just like
 this.
 ++--+-+++
 | id | UserName | Attribute   | op | Value  |
 ++--+-+++
 |  1 | nizar    | Password    | == | nizar  |
 |  2 | nizar1   | Password    | == | nizar1 |
 |  6 | tes  | Max-All-Session | == | 90 |
 |  4 | tes  |
 Password    | == | tes    |
 ++--+-+++
 after i adding the attribut max-all-session the user tes cannot login
 anymore. i do running freeradius in debug mode and the following si the
 result..

:= not ==. And that password attribute Password is deprecated for many
years. How old is your freeradius version?

Ivan Kalik
Kalik Informatika ISP

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


  
-Inline Attachment Follows-

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


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

Re: Implementing a logout sqlcounter

[Ming-Ching Tiew]

--- On Tue, 5/12/09, Ming-Ching Tiew mct...@yahoo.com wrote:

 
 Whereas the radclient continually sending accounting info
 to
 the server, I thought doing the reject at accouting will 
 also some what accomplishing the same purpose.
 
 Any comments ? 
 
 

Further readings seems to indicate this this could possible
implemented using unlang 'update disconnect' in preacct 
section ?

Regards


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

Re: Implementing a logout sqlcounter

[Ivan Kalik]

 I am thinking of using sqlcounter to implement a logout counter,
 ie whenever I want to logout a particular user, I set something
 into the database, then the sqlcounter will pick it up, and
 drop the existing session.


Dropping sessions on radius server will have no impact on users connection
to the NAS.

 OK I read in some of the older posts, one way people have
 implemented this feature is via then spin off radclient.
 This has the disadvantage in that, if the radius client is
 behind somekind of firewall, the server initiated radclient
 will have problem sending this to the radius client.


Use Packet-Src-IP-Address.

 Whereas the radclient continually sending accounting info to
 the server, I thought doing the reject at accouting will
 also some what accomplishing the same purpose.

Disconnecting users on accounting packets is not straightforward. NAS
features will dictate if this is at all possible. Many don't support CoA
and PoD and with some you can't remotely disconnect the user even using
SNMP.

Ivan Kalik
Kalik Informatika ISP

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

Implementing a logout sqlcounter

[Ming-Ching Tiew]

I am thinking of using sqlcounter to implement a logout counter, 
ie whenever I want to logout a particular user, I set something
into the database, then the sqlcounter will pick it up, and 
drop the existing session.

OK I read in some of the older posts, one way people have 
implemented this feature is via then spin off radclient. 
This has the disadvantage in that, if the radius client is 
behind somekind of firewall, the server initiated radclient 
will have problem sending this to the radius client.

Whereas the radclient continually sending accounting info to
the server, I thought doing the reject at accouting will 
also some what accomplishing the same purpose.

Any comments ? 



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

Re: authentication failed because sqlcounter...

[Nizar Zulmi]

i am using freeradius 1.1.7, not that old rite??? i've tried using := operator 
and cleartext-password but still doesn't work..this bellow is my radcheck 
table..
++--++++
| id | UserName | Attribute  | op | Value  |
++--++++
|  1 | nizar    | Password   | == | nizar  |
|  2 | nizar1   | Password   | == | nizar1 |
|  6 | tes  | Max-All-Session    | == | 90 |
|  4 | tes  | Password   | == | tes    |
|  7 | denizaro | Cleartext-Password | := | 123456 |
|  8 | denizaro | Max-All-Session    | := | 30 |
++--++++
6 rows in set (0.00 sec)

i try to log in with user denizaro first time before i add attribute 
max-all-session its succesfully logged in, but after i adding max-all-session 
attribut its failed..
whats happen??

--- On Sat, 5/9/09, Ivan Kalik t...@kalik.net wrote:

From: Ivan Kalik t...@kalik.net
Subject: Re: authentication failed because sqlcounter...
To: FreeRadius users mailing list freeradius-users@lists.freeradius.org
Date: Saturday, May 9, 2009, 2:51 AM

 mm confusing..i just enable the sqlcounter in radiusd.conf. i just let it
 as default no change i made in noresetcounter module. then i adding
 noresetcounter in authorize and instantiate section.
 i have defined 1user named tes and password tes has loged in normally
 before i add attribut max-all-session in the table radcheck just like
 this.
 ++--+-+++
 | id | UserName | Attribute   | op | Value  |
 ++--+-+++
 |  1 | nizar    | Password    | == | nizar  |
 |  2 | nizar1   | Password    | == | nizar1 |
 |  6 | tes  | Max-All-Session | == | 90 |
 |  4 | tes  | Password    | == | tes    |
 ++--+-+++
 after i adding the attribut max-all-session the user tes cannot login
 anymore. i do running freeradius in debug mode and the following si the
 result..

:= not ==. And that password attribute Password is deprecated for many
years. How old is your freeradius version?

Ivan Kalik
Kalik Informatika ISP

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


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

Re: authentication failed because sqlcounter...

[Nizar Zulmi]

i am using freeradius 1.1.7, not that old rite??? i've tried using := operator 
and cleartext-password but still doesn't work..this bellow is my radcheck 
table..
++--++++
| id | UserName | Attribute  | op | Value  |
++--++++
|  1 | nizar    | Password   | == | nizar  |
|  2 | nizar1   | Password   | == | nizar1 |
|  6 | tes  | Max-All-Session    | == | 90 |
|  4 | tes  | Password   | == | tes    |
|  7 | denizaro | Cleartext-Password | := | 123456 |
|  8 | denizaro | Max-All-Session    | := | 30 |
++--++++
6 rows in set (0.00 sec)

i try to log in with user denizaro first time before i add attribute 
max-all-session its succesfully logged in, but after i adding max-all-session 
attribut its failed..
whats happen??

--- On Sat, 5/9/09, Ivan Kalik t...@kalik.net wrote:

From: Ivan Kalik t...@kalik.net
Subject: Re: authentication failed because sqlcounter...
To: FreeRadius users mailing list freeradius-users@lists.freeradius.org
Date: Saturday, May 9, 2009, 2:51 AM

 mm confusing..i just enable the sqlcounter in radiusd.conf. i just let it
 as default no change i made in noresetcounter module. then i adding
 noresetcounter in authorize and instantiate section.
 i have defined 1user named tes and password tes has loged in normally
 before i add attribut max-all-session in the table radcheck just like
 this.
 ++--+-+++
 | id | UserName | Attribute   | op | Value  |
 ++--+-+++
 |  1 | nizar    | Password    | == | nizar  |
 |  2 | nizar1   | Password    | == | nizar1 |
 |  6 | tes  | Max-All-Session | == | 90 |
 |  4 | tes  | Password    | == | tes    |
 ++--+-+++
 after i adding the attribut max-all-session the user tes cannot login
 anymore. i do running freeradius in debug mode and the following si the
 result..

:= not ==. And that password attribute Password is deprecated for many
years. How old is your freeradius version?

Ivan Kalik
Kalik Informatika ISP

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


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

authentication failed because sqlcounter...

[Nizar Zulmi]

mm confusing..i just enable the sqlcounter in radiusd.conf. i just let it as 
default no change i made in noresetcounter module. then i adding noresetcounter 
in authorize and instantiate section. 
i have defined 1user named tes and password tes has loged in normally before i 
add attribut max-all-session in the table radcheck just like this. 
++--+-+++
| id | UserName | Attribute   | op | Value  |
++--+-+++
|  1 | nizar    | Password    | == | nizar  | 
|  2 | nizar1   | Password    | == | nizar1 | 
|  6 | tes  | Max-All-Session | == | 90 | 
|  4 | tes  | Password    | == | tes    | 
++--+-+++
after i adding the attribut max-all-session the user tes cannot login anymore. 
i do running freeradius in debug mode and the following si the result..
somebody help me please...
freeradius debug result===
rad_recv: Access-Request packet from host 192.168.0.1:56614, id=0, length=194
    User-Name = tes
    User-Password = tes
    NAS-IP-Address = 0.0.0.0
    Service-Type = Login-User
    Framed-IP-Address = 192.168.182.3
    Calling-Station-Id = 00-1E-68-23-E9-C8
    Called-Station-Id = 00-00-E2-78-FF-39
    NAS-Identifier = nas01
    Acct-Session-Id = 4a048168
    NAS-Port-Type = Wireless-802.11
    NAS-Port = 0
    Message-Authenticator = 0x1c3a148590ef0762aed6069cc9ac0715
    WISPr-Logoff-URL = http://192.168.182.1:3990/logoff;
  Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 3
  modcall[authorize]: module preprocess returns ok for request 3
  modcall[authorize]: module chap returns noop for request 3
  modcall[authorize]: module mschap returns noop for request 3
    rlm_realm: No '@' in User-Name = tes, looking up realm NULL
    rlm_realm: No such realm NULL
  modcall[authorize]: module suffix returns noop for request 3
  rlm_eap: No EAP-Message, not doing EAP
  modcall[authorize]: module eap returns noop for request 3
radius_xlat:  'tes'
rlm_sql (sql): sql_set_user escaped user -- 'tes'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op   FROM 
radcheck   WHERE Username = 'tes'   ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 0
radius_xlat:  'SELECT 
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op 
 FROM radgroupcheck,usergroup WHERE usergroup.Username = 'tes' AND 
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op   FROM 
radreply   WHERE Username = 'tes'   ORDER BY id'
radius_xlat:  'SELECT 
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op 
 FROM radgroupreply,usergroup WHERE usergroup.Username = 'tes' AND 
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql (sql): Released sql socket id: 0
rlm_sql (sql): No matching entry in the database for request from user [tes]
  modcall[authorize]: module sql returns notfound for request 3
rlm_sqlcounter: Entering module authorize code
rlm_sqlcounter: Could not find Check item value pair
  modcall[authorize]: module noresetcounter returns noop for request 3
rlm_pap: WARNING! No known good password found for the user.  Authentication 
may fail because of this.
  modcall[authorize]: module pap returns noop for request 3
modcall: leaving group authorize (returns ok) for request 3
auth: No authenticate method (Auth-Type) configuration found for the request: 
Rejecting the user
auth: Failed to validate the user.
Delaying request 3 for 1 seconds
Finished request 3
Going to the next request
--- Walking the entire request list ---
Waking up in 1 seconds...
--- Walking the entire request list ---
Waking up in 1 seconds...
rad_recv: Access-Request packet from host 192.168.0.1:56614, id=0, length=194
Sending Access-Reject of id 0 to 192.168.0.1 port 56614
Waking up in 1 seconds...
--- Walking the entire request list ---
Waking up in 4 seconds...
--- Walking the entire request list ---
Cleaning up request 3 ID 0 with timestamp 4a048180
Nothing to do.  Sleeping until we see a request.




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

authentication failed because sqlcounter...

[Nizar Zulmi]

mm confusing..i just enable the sqlcounter in radiusd.conf. i just let it as 
default no change i made in noresetcounter module. then i adding noresetcounter 
in authorize and instantiate section. 
i have defined 1user named tes and password tes has loged in normally before i 
add attribut max-all-session in the table radcheck just like this. 
++--+-+++
| id | UserName | Attribute   | op | Value  |
++--+-+++
|  1 | nizar    | Password    | == | nizar  | 
|  2 | nizar1   | Password    | == | nizar1 | 
|  6 | tes  | Max-All-Session | == | 90 | 
|  4 | tes  | Password    | == | tes    | 
++--+-+++
after i adding the attribut max-all-session the user tes cannot login anymore. 
i do running freeradius in debug mode and the following si the result..
somebody help me please...
freeradius debug result===
rad_recv: Access-Request packet from host 192.168.0.1:56614, id=0, length=194
    User-Name = tes
    User-Password = tes
    NAS-IP-Address = 0.0.0.0
    Service-Type = Login-User
    Framed-IP-Address = 192.168.182.3
    Calling-Station-Id = 00-1E-68-23-E9-C8
    Called-Station-Id = 00-00-E2-78-FF-39
    NAS-Identifier = nas01
    Acct-Session-Id = 4a048168
    NAS-Port-Type = Wireless-802.11
    NAS-Port = 0
    Message-Authenticator = 0x1c3a148590ef0762aed6069cc9ac0715
    WISPr-Logoff-URL = http://192.168.182.1:3990/logoff;
  Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 3
  modcall[authorize]: module preprocess returns ok for request 3
  modcall[authorize]: module chap returns noop for request 3
  modcall[authorize]: module mschap returns noop for request 3
    rlm_realm: No '@' in User-Name = tes, looking up realm NULL
    rlm_realm: No such realm NULL
  modcall[authorize]: module suffix returns noop for request 3
  rlm_eap: No EAP-Message, not doing EAP
  modcall[authorize]: module eap returns noop for request 3
radius_xlat:  'tes'
rlm_sql (sql): sql_set_user escaped user -- 'tes'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op   FROM 
radcheck   WHERE Username = 'tes'   ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 0
radius_xlat:  'SELECT 
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op 
 FROM radgroupcheck,usergroup WHERE usergroup.Username = 'tes' AND 
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op   FROM 
radreply   WHERE Username = 'tes'   ORDER BY id'
radius_xlat:  'SELECT 
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op 
 FROM radgroupreply,usergroup WHERE usergroup.Username = 'tes' AND 
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql (sql): Released sql socket id: 0
rlm_sql (sql): No matching entry in the database for request from user [tes]
  modcall[authorize]: module sql returns notfound for request 3
rlm_sqlcounter: Entering module authorize code
rlm_sqlcounter: Could not find Check item value pair
  modcall[authorize]: module noresetcounter returns noop for request 3
rlm_pap: WARNING! No known good password found for the user.  Authentication 
may fail because of this.
  modcall[authorize]: module pap returns noop for request 3
modcall: leaving group authorize (returns ok) for request 3
auth: No authenticate method (Auth-Type) configuration found for the request: 
Rejecting the user
auth: Failed to validate the user.
Delaying request 3 for 1 seconds
Finished request 3
Going to the next request
--- Walking the entire request list ---
Waking up in 1 seconds...
--- Walking the entire request list ---
Waking up in 1 seconds...
rad_recv: Access-Request packet from host 192.168.0.1:56614, id=0, length=194
Sending Access-Reject of id 0 to 192.168.0.1 port 56614
Waking up in 1 seconds...
--- Walking the entire request list ---
Waking up in 4 seconds...
--- Walking the entire request list ---
Cleaning up request 3 ID 0 with timestamp 4a048180
Nothing to do.  Sleeping until we see a request.




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

authentication failed because sqlcounter...

[Nizar Zulmi]

mm confusing..i just enable the sqlcounter in radiusd.conf. i just let it as 
default no change i made in noresetcounter module. then i adding noresetcounter 
in authorize and instantiate section. 
i have defined 1user named tes and password tes has loged in normally before i 
add attribut max-all-session in the table radcheck just like this. 
++--+-+++
| id | UserName | Attribute   | op | Value  |
++--+-+++
|  1 | nizar    | Password    | == | nizar  | 
|  2 | nizar1   | Password    | == | nizar1 | 
|  6 | tes  | Max-All-Session | == | 90 | 
|  4 | tes  | Password    | == | tes    | 
++--+-+++
after i adding the attribut max-all-session the user tes cannot login anymore. 
i do running freeradius in debug mode and the following si the result..
somebody help me please...
freeradius debug result===
rad_recv: Access-Request packet from host 192.168.0.1:56614, id=0, length=194
    User-Name = tes
    User-Password = tes
    NAS-IP-Address = 0.0.0.0
    Service-Type = Login-User
    Framed-IP-Address = 192.168.182.3
    Calling-Station-Id = 00-1E-68-23-E9-C8
    Called-Station-Id = 00-00-E2-78-FF-39
    NAS-Identifier = nas01
    Acct-Session-Id = 4a048168
    NAS-Port-Type = Wireless-802.11
    NAS-Port = 0
    Message-Authenticator = 0x1c3a148590ef0762aed6069cc9ac0715
    WISPr-Logoff-URL = http://192.168.182.1:3990/logoff;
  Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 3
  modcall[authorize]: module preprocess returns ok for request 3
  modcall[authorize]: module chap returns noop for request 3
  modcall[authorize]: module mschap returns noop for request 3
    rlm_realm: No '@' in User-Name = tes, looking up realm NULL
    rlm_realm: No such realm NULL
  modcall[authorize]: module suffix returns noop for request 3
  rlm_eap: No EAP-Message, not doing EAP
  modcall[authorize]: module eap returns noop for request 3
radius_xlat:  'tes'
rlm_sql (sql): sql_set_user escaped user -- 'tes'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op   FROM 
radcheck   WHERE Username = 'tes'   ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 0
radius_xlat:  'SELECT 
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op 
 FROM radgroupcheck,usergroup WHERE usergroup.Username = 'tes' AND 
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op   FROM 
radreply   WHERE Username = 'tes'   ORDER BY id'
radius_xlat:  'SELECT 
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op 
 FROM radgroupreply,usergroup WHERE usergroup.Username = 'tes' AND 
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql (sql): Released sql socket id: 0
rlm_sql (sql): No matching entry in the database for request from user [tes]
  modcall[authorize]: module sql returns notfound for request 3
rlm_sqlcounter: Entering module authorize code
rlm_sqlcounter: Could not find Check item value pair
  modcall[authorize]: module noresetcounter returns noop for request 3
rlm_pap: WARNING! No known good password found for the user.  Authentication 
may fail because of this.
  modcall[authorize]: module pap returns noop for request 3
modcall: leaving group authorize (returns ok) for request 3
auth: No authenticate method (Auth-Type) configuration found for the request: 
Rejecting the user
auth: Failed to validate the user.
Delaying request 3 for 1 seconds
Finished request 3
Going to the next request
--- Walking the entire request list ---
Waking up in 1 seconds...
--- Walking the entire request list ---
Waking up in 1 seconds...
rad_recv: Access-Request packet from host 192.168.0.1:56614, id=0, length=194
Sending Access-Reject of id 0 to 192.168.0.1 port 56614
Waking up in 1 seconds...
--- Walking the entire request list ---
Waking up in 4 seconds...
--- Walking the entire request list ---
Cleaning up request 3 ID 0 with timestamp 4a048180
Nothing to do.  Sleeping until we see a request.




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

Re: authentication failed because sqlcounter...

[Ivan Kalik]

 mm confusing..i just enable the sqlcounter in radiusd.conf. i just let it
 as default no change i made in noresetcounter module. then i adding
 noresetcounter in authorize and instantiate section.
 i have defined 1user named tes and password tes has loged in normally
 before i add attribut max-all-session in the table radcheck just like
 this.
 ++--+-+++
 | id | UserName | Attribute   | op | Value  |
 ++--+-+++
 |  1 | nizar    | Password    | == | nizar  |
 |  2 | nizar1   | Password    | == | nizar1 |
 |  6 | tes  | Max-All-Session | == | 90 |
 |  4 | tes  | Password    | == | tes    |
 ++--+-+++
 after i adding the attribut max-all-session the user tes cannot login
 anymore. i do running freeradius in debug mode and the following si the
 result..

:= not ==. And that password attribute Password is deprecated for many
years. How old is your freeradius version?

Ivan Kalik
Kalik Informatika ISP

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

Re: SQLCounter configuration

[alt_]

tnt-4 wrote:
 
3)  Currently I receive something like   Reply-Message = Your maximum
never usage time has been reached   when the counter reaches its limit -
even when what was counted as not time but data volume. Can Reply-Message
for sqlcounter be configured as in the expiration module?

 
 No. But you can sort out the reply with unlang.
 
 

Can you show some examples please? I try to do like this:

noresetBytecounter

if (reject) {
update reply {
Reply-Message := Traffic limit exceeded.
}
}

but if noresetBytecounter return reject freeradius immediatly return reject
to user and do not process 'if' clause

-- 
View this message in context: 
http://www.nabble.com/SQLCounter-configuration-tp21384983p22219985.html
Sent from the FreeRadius - User mailing list archive at Nabble.com.

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

Re: SQLCounter configuration

[Alan DeKok]

alt_ wrote:
 Can you show some examples please? I try to do like this:
 
 noresetBytecounter
 
 if (reject) {
 update reply {
 Reply-Message := Traffic limit exceeded.
 }
 }
 
 but if noresetBytecounter return reject freeradius immediatly return reject
 to user and do not process 'if' clause

  You need to put this in the post-auth section, Reject subsection.
 See the example configuration files.

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

Re: SQLCounter configuration

[Alexander Solodukhin]

On Thu, 26 Feb 2009 12:03:22 +0200, Alan DeKok al...@deployingradius.com  
wrote:



alt_ wrote:

Can you show some examples please? I try to do like this:

noresetBytecounter

if (reject) {
update reply {
Reply-Message := Traffic limit exceeded.
}
}

but if noresetBytecounter return reject freeradius immediatly return  
reject

to user and do not process 'if' clause


  You need to put this in the post-auth section, Reject subsection.
 See the example configuration files.



/etc/freeradius/sites-enabled/default[412]: SQL Counter modules aren't  
allowed in 'post-auth' sections -- they have no such method.


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

Re: SQLCounter configuration

[Alan DeKok]

Alexander Solodukhin wrote:
 /etc/freeradius/sites-enabled/default[412]: SQL Counter modules aren't
 allowed in 'post-auth' sections -- they have no such method.

  The REJECT handler needs to be put into the post-auth section.  Not
the sqlcounter module.

  Please READ the default configuration files.  The comments in
sites-available/default explain how the post-auth reject handler works.

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

Re: SQLCounter configuration

[tnt]

 Can you show some examples please? I try to do like this:

 noresetBytecounter

 if (reject) {
 update reply {
 Reply-Message := Traffic limit exceeded.
 }
 }

 but if noresetBytecounter return reject freeradius immediatly return
 reject
 to user and do not process 'if' clause

   You need to put this in the post-auth section, Reject subsection.
  See the example configuration files.


/etc/freeradius/sites-enabled/default[412]: SQL Counter modules aren't
allowed in 'post-auth' sections -- they have no such method.


Don't put the counter there but the counter attribute:

if(control:Counter-Attribute  0) { ...

Ivan Kalik
Kalik Informatika ISP

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

Re: SQLCounter configuration

[Alexander Solodukhin]

On Thu, 26 Feb 2009 13:19:06 +0200, t...@kalik.net wrote:


Can you show some examples please? I try to do like this:

noresetBytecounter

if (reject) {
update reply {
Reply-Message := Traffic limit exceeded.
}
}

but if noresetBytecounter return reject freeradius immediatly return
reject
to user and do not process 'if' clause


  You need to put this in the post-auth section, Reject subsection.
 See the example configuration files.



/etc/freeradius/sites-enabled/default[412]: SQL Counter modules aren't
allowed in 'post-auth' sections -- they have no such method.



Don't put the counter there but the counter attribute:

if(control:Counter-Attribute  0) { ...



Sorry, i read mans and comments in config and try to do like you say but  
it's not work as i need. I have sqlcounter:

sqlcounter noresetBytecounter {
counter-name = Total-Max-Octets
check-name = Max-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = never
query = SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM  
radacct WHERE UserName='%{%k}'

}

post-auth section in sites-enabled/default:
Post-Auth-Type REJECT {
attr_filter.access_reject

if (control:Max-Octets  0) {
update reply {
Reply-Message := Traffic limit exceeded.
}
}

}

so, what exactly name must be in control: ? As you can see i try to put  
Max-Octets, but seems that attribute simly loaded from mysql (even if  
password wrong and no traffic overlimit occures there 'Traffic limit  
exceeded.' error message returned) and it's always greater than zero. I  
try counter:ChilliSpot-Max-Total-Octets but got:


Attribute control:ChilliSpot-Max-Total-Octets was not found

in debug. Sorry but i something missunderstand in general, please put me  
on right way.




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

Re: SQLCounter configuration

[tnt]

Sorry, i read mans and comments in config and try to do like you say but
it's not work as i need. I have sqlcounter:
sqlcounter noresetBytecounter {
 counter-name = Total-Max-Octets
 check-name = Max-Octets
 reply-name = ChilliSpot-Max-Total-Octets
 sqlmod-inst = sql
 key = User-Name
 reset = never
 query = SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
radacct WHERE UserName='%{%k}'
}

post-auth section in sites-enabled/default:
 Post-Auth-Type REJECT {
 attr_filter.access_reject

 if (control:Max-Octets  0) {
 update reply {
 Reply-Message := Traffic limit exceeded.
 }
 }

 }


Looking at that it should probably be:

control:Max-Octets (check value)  control:Total-Max-Octets (counter
value). I don't think that negative value vill go into (reply:)
ChilliSpot-Max-Total-Octets. If it doesn't work, you can check if reply
value is zero.

Ivan Kalik
Kalik Informatika ISP

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

Re: SQLCounter configuration

[Alexander Solodukhin]

On Thu, 26 Feb 2009 16:55:00 +0200, t...@kalik.net wrote:


Sorry, i read mans and comments in config and try to do like you say but
it's not work as i need. I have sqlcounter:
sqlcounter noresetBytecounter {
counter-name = Total-Max-Octets
check-name = Max-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = never
query = SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))  
FROM

radacct WHERE UserName='%{%k}'
}

post-auth section in sites-enabled/default:
Post-Auth-Type REJECT {
attr_filter.access_reject

if (control:Max-Octets  0) {
update reply {
Reply-Message := Traffic limit exceeded.
}
}

}



Looking at that it should probably be:

control:Max-Octets (check value)  control:Total-Max-Octets (counter
value). I don't think that negative value vill go into (reply:)
ChilliSpot-Max-Total-Octets. If it doesn't work, you can check if reply
value is zero.


Total-Max-Octets also not found in control list:

Attribute control:Total-Max-Octets was not found


I can do like this:

if (!reply:ChilliSpot-Max-Total-Octets) {
update reply {
Reply-Message := Traffic limit exceeded.
}
}

but if user enter wrong password Traffic limit exceeded. error message  
will be displayed.


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

Re: SQLCounter configuration

[tnt]

I can do like this:

 if (!reply:ChilliSpot-Max-Total-Octets) {
 update reply {
 Reply-Message := Traffic limit exceeded.
 }
 }

but if user enter wrong password Traffic limit exceeded. error message
will be displayed.


I had a look at the code and sqlcounter module sets the Reply-Message:
Your maximum %s usage time has been reached when user is rejected.
Check for that reply message and alter it.

Ivan Kalik
Kalik Informatika ISP

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

Re: SQLCounter configuration

[Alexander Solodukhin]

On Thu, 26 Feb 2009 20:56:03 +0200, t...@kalik.net wrote:


I can do like this:

if (!reply:ChilliSpot-Max-Total-Octets) {
update reply {
Reply-Message := Traffic limit exceeded.
}
}

but if user enter wrong password Traffic limit exceeded. error message
will be displayed.



I had a look at the code and sqlcounter module sets the Reply-Message:
Your maximum %s usage time has been reached when user is rejected.
Check for that reply message and alter it.


Thank you. I see this solution in the internet, but i need configurable  
Reply-Message, becouse i want to limit not only traffic, but session time  
too.



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

Re: SQLCounter configuration

[tnt]

Thank you. I see this solution in the internet, but i need configurable
Reply-Message, becouse i want to limit not only traffic, but session time
too.


Actually, I can recall that sqlcounter had configurable Reply-Message in
early days (I had 1.0.5 where it was configurable). Download 1.0.5 and
have a look how was it done. Perhaps you can create a patch for the
current version without too much trouble.

Ivan Kalik
Kalik Informatika ISP

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

Re: SQLCounter configuration

[tnt]

1)  After the time or data volume for a period (say a month) is reached
the user is automatically disconnected - as expected. Rather them not
allowing to log in , I would like freeradius to return some other attributes
(WISPr-Bandwidth-Max-Up and WISPr-Bandwidth-Max-Down work fine with
ChilliSpot) to be sent to the router for the user, so that a connection is
still possible, but at a much lower maximum speed. Can that be done, and if
so, can someone give me a pointer. I was thinking maybe using unlang, but I
may be barking up the wrong tree.


Create a  sql group called slow that enforces those limits. Add the user
to that group (with low priority) with unlang (on authentication) or
perl sript (on accounting stop packet). Important - remove slow group
entries when counter resets.

2)  The   reset = monthly   method seem to the first of the current
month. That is one way of doing it, but I would like the reset to occur at
the same date in the month every month. So if someone signs up, say on the
12th of a month, the reset will occur every month on the 12th. Can that be
done with freeradius?


Yes. Queries are configurable. Don't use %b. Calculate start the way you
see fit.

3)  Currently I receive something like   Reply-Message = Your maximum
never usage time has been reached   when the counter reaches its limit -
even when what was counted as not time but data volume. Can Reply-Message
for sqlcounter be configured as in the expiration module?


No. But you can sort out the reply with unlang.

Ivan Kalik
Kalik Informatika ISP

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

Re: SQLCounter configuration

[Venkatesh K]

On Sat, Jan 10, 2009 at 10:36 AM, Hanno Schupp hanno.sch...@gmail.com wrote:
 Dear All,



 I have a few questions regarding the rlm_sqlcounter module and its
 configuration. I have a freeradius 2.1.0 communicating with a router running
 chillispot 1.1  I set up counters for time, data volume, and with different
 reset periods. All works fine and as expected.



 Here my questions:



 1)  After the time or data volume for a period (say a month) is reached
 the user is automatically disconnected – as expected. Rather them not
 allowing to log in , I would like freeradius to return some other attributes
 (WISPr-Bandwidth-Max-Up and WISPr-Bandwidth-Max-Down work fine with
 ChilliSpot) to be sent to the router for the user, so that a connection is
 still possible, but at a much lower maximum speed. Can that be done, and if
 so, can someone give me a pointer. I was thinking maybe using unlang, but I
 may be barking up the wrong tree.


You might be better off making changes to your provisioning system
assuming you are using a database backend although
you might be able to get away with unlang.



 2)  The   reset = monthly   method seem to the first of the current
 month. That is one way of doing it, but I would like the reset to occur at
 the same date in the month every month. So if someone signs up, say on the
 12th of a month, the reset will occur every month on the 12th. Can that be
 done with freeradius?



rlm_sqlcounter only support calendar month or day or week.


 3)  Currently I receive something like   Reply-Message = Your maximum
 never usage time has been reached   when the counter reaches its limit –
 even when what was counted as not time but data volume. Can Reply-Message
 for sqlcounter be configured as in the expiration module?



rlm_sqlcounter does not have provision for cusom reply messages. There
was a patch floating around to send custom reply messages from
rlm_sqlcounter. Probably you should look in mailing list archives.


 4)  One surprise I have had: I don't quite understand how the system
 calculates the day for the beginning of the reset = weekly. I appreciate
 that calculating the day of the week is not easy as for example first of the
 current month or beginning of the day, so I was not surprised to not have a
 Monday, but I was surprised to see the SQL query run with 'Fri Jan 09 2009
 00:00:00 GMT+1300 (New Zealand Daylight Time)' as beginning of the week,
 when running the query on a Saturday. BTW, SQL time is configured correctly,
 and the UNIX_TIMESTAMP is giving a correct timestamp for local time. What am
 I missing?
 (Note: When setting reset = 7d, I get something more explainable like 'Sat
 Jan 10 2009 00:00:00 GMT+1300 (New Zealand Daylight Time)', which is the
 beginning of the today's day. Should reset = weekly and reset =7d yield the
 same result? If not, what is weekly vs 7d, and why does the week not start
 on a Monday?

Thanks,

Venkatesh K

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

SQLCounter configuration

[Hanno Schupp]

Dear All,

 

I have a few questions regarding the rlm_sqlcounter module and its
configuration. I have a freeradius 2.1.0 communicating with a router running
chillispot 1.1  I set up counters for time, data volume, and with different
reset periods. All works fine and as expected.

 

Here my questions:

 

1)  After the time or data volume for a period (say a month) is reached
the user is automatically disconnected - as expected. Rather them not
allowing to log in , I would like freeradius to return some other attributes
(WISPr-Bandwidth-Max-Up and WISPr-Bandwidth-Max-Down work fine with
ChilliSpot) to be sent to the router for the user, so that a connection is
still possible, but at a much lower maximum speed. Can that be done, and if
so, can someone give me a pointer. I was thinking maybe using unlang, but I
may be barking up the wrong tree.

 

2)  The   reset = monthly   method seem to the first of the current
month. That is one way of doing it, but I would like the reset to occur at
the same date in the month every month. So if someone signs up, say on the
12th of a month, the reset will occur every month on the 12th. Can that be
done with freeradius?

 

3)  Currently I receive something like   Reply-Message = Your maximum
never usage time has been reached   when the counter reaches its limit -
even when what was counted as not time but data volume. Can Reply-Message
for sqlcounter be configured as in the expiration module?

 

4)  One surprise I have had: I don't quite understand how the system
calculates the day for the beginning of the reset = weekly. I appreciate
that calculating the day of the week is not easy as for example first of the
current month or beginning of the day, so I was not surprised to not have a
Monday, but I was surprised to see the SQL query run with 'Fri Jan 09 2009
00:00:00 GMT+1300 (New Zealand Daylight Time)' as beginning of the week,
when running the query on a Saturday. BTW, SQL time is configured correctly,
and the UNIX_TIMESTAMP is giving a correct timestamp for local time. What am
I missing?
(Note: When setting reset = 7d, I get something more explainable like 'Sat
Jan 10 2009 00:00:00 GMT+1300 (New Zealand Daylight Time)', which is the
beginning of the today's day. Should reset = weekly and reset =7d yield the
same result? If not, what is weekly vs 7d, and why does the week not start
on a Monday?

 

Any hints welcome

 

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

Rlm sqlcounter -- Data Based Counter

[Marcel Grandemange]

Good Day.

Im wondering is someone could assist me here.
I currently have freeradius setup and working somewhat.

However I have following setup:


sqlcounter monthlytraffic {
 counter-name = Monthly-Traffic
 check-name = Max-Monthly-Traffic
 reply-name = Mikrotik-Xmit-Limit-Gigawords
 sqlmod-inst = sql
 key = User-Name
 reset = monthly
query = SELECT (sum(AcctInputOctets)+sum(AcctOutputOctets)) FROM radacct
WHERE UserName='%{%k}' AND Month(AcctStopTime) =(Month(NOW()
#query = SELECT SUM(AcctInputOctets - GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b -
UNIX_T
#query = SELECT SUM(AcctInputOctets + AcctOutputOctets) FROM radacct WHERE
UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime)  '%b'
#query = SELECT SUM(OctetTotal) FROM thismonthsusage WHERE
UsernameView='%{%k}'
}

As you can see I have tried numerous queries to get this working correctly
and decided finally to do my own query against a view that does my work for
me.
(Currently commented out because having issues with it)

The view is called thismonthsusage

And it's definition as follows

select `radacct`.`UserName` AS `UsernameView`,`usergroup`.`GroupName` AS
`GroupNameView`,`radacct`.`AcctStartTime` AS
`AcctStartTimeView`,`radacct`.`AcctStopTime` AS
`AcctStopTimeView`,`radacct`.`AcctInputOctets` AS
`AcctInputOctetsView`,`radacct`.`AcctOutputOctets` AS
`AcctOutputOctetsView`,(sum(`radacct`.`AcctInputOctets`) +
sum(`radacct`.`AcctOutputOctets`)) AS `OctetTotal` from (`radacct` join
`usergroup`) where ((month(`radacct`.`AcctStartTime`) = month(curdate()))
and (month(`radacct`.`AcctStopTime`) = month(curdate())) and
(`radacct`.`UserName` = `usergroup`.`UserName`) and (`usergroup`.`GroupName`
 _latin1'Mac')) group by
`radacct`.`UserName`,`usergroup`.`GroupName`,`radacct`.`AcctStartTime`,`rada
cct`.`AcctStopTime`,`radacct`.`AcctOutputOctets` order by
`radacct`.`AcctStopTime`

As you can see quite simply and efficient however there is one battle I am
facing..
As soon as a new month rolls over there are NO accounting entries for that
user for that month, hence no record and freeradius denies user as he hasn't
got any entries, any body know how I should modify the view to display all
users even if they don't have an entry for current month, but rather display
them with value 0 as apposed to not at all?

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

Re: Rlm sqlcounter -- Data Based Counter

[tnt]

#query = SELECT SUM(OctetTotal) FROM thismonthsusage WHERE
UsernameView='%{%k}'
}

..
As soon as a new month rolls over there are NO accounting entries for that
user for that month, hence no record and freeradius denies user as he hasn't
got any entries, any body know how I should modify the view to display all
users even if they don't have an entry for current month, but rather display
them with value 0 as apposed to not at all?


Your question belongs on the MySQL forum. Use if() function:

SELECT IF(SUM(OctetTotal) IS NULL, 0, SUM(OctetTotal)) FROM ...

Ivan Kalik
Kalik Informatika ISP

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

Re: sqlcounter returning wrong value?

[Venkatesh K]

 is a sample sqlcounter.conf

--
sqlcounter noresetBytecounter {
counter-name = Total-Max-Octets
check-name = Max-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = never
counter-type = data
check-unit = KibiOctets
query = SELECT
(SUM(AcctInputOctets)+SUM(AcctOutputOctets))/1024 FROM radacct WHERE
UserName='%{%k}'
}
-

As you can see, you need to make changes to the units of check-item
value stored in DB. You can use KibiOctets, MibiOctets in DB and set
the sqlcounter.conf file accordingly. Even if you used KibiOctets or
MibiOctets in DB, the replies will always be sent in Octets and
maximum is limited to 4G.

Regards,

Venkatesh K



On Wed, Nov 12, 2008 at 9:11 PM, Venkatesh K [EMAIL PROTECTED] wrote:
 Hi,

 On Wed, Nov 12, 2008 at 2:06 AM, liran tal [EMAIL PROTECTED] wrote:

 Waiting for that traffic limitation patch, Venkatesh.
 Thanks.

 I am sorry. I had few busy days this week. You can expect a patch tomorrow.


 On Sun, Nov 9, 2008 at 6:00 AM, Venkatesh K [EMAIL PROTECTED] wrote:

 Hi Liran,

 On Sun, Nov 9, 2008 at 4:16 AM, liran tal [EMAIL PROTECTED] wrote:
  Hey Venkatesh,
 
  On Fri, Oct 31, 2008 at 2:26 AM, Venkatesh K [EMAIL PROTECTED] wrote:
 
  2008/10/31  [EMAIL PROTECTED]:
   It does make sense. rlm_sqlcounterworks like this toward the time of
   the
   reset: lets say you have an hour left, your limit is 20 hours and you
   have signed in 15 minutes before counter reset time.  When code
   calculates that you can be online at reset time it doesn't return
   your
   allowance (1 hour) but adds the limit for the next conting period (20
   hours) to the remaining time (15 minutes) and returns that value (20
   hours and 15 minutes). Reasoning is that your session shouldn't be
   discontinued after an hour becouse 15 minutes into the session new
   limit
   should come into force (and session limit can't be changed during the
   session).
  
   In your case there is about 2,000,000 left on the counter but only a
   few
   thousand seconds left to the end of the reset period, so code will
   add
   those few thousands to the next period limit (26,000,000) and return
   that value. Code doesn't know are you counting data or time as
   there is  no such configuration item.
  
   Venkatesh had posted the patch that switches off this peace of code
   for
   data counters by introducing that configuration item. You should try
   it.
 
  rlm_sqlcounter has one more limitation. In version 1.1.7, the maximum
  counter value was limited to 2G whereas in 2.1.1 it seems to be 4G.
  This imposes an artificial limitation of maximum of 4GB of downloads.
  I had a workaround where I patched rlm_sqlcounter to limit the per
  session downloads to 4GB if allowed usage exceeds 4GB.
 
  Except this issue, I think, with the patch I posted earlier, one
  should be fine with rlm_sqlcounter. If someone needs a patch to work
  around the 2GB/4GB limit, I will post the patch.
 
 
  Sorry for the late reply.
  I applied your patch and now data counters work as expected with a minor
  exception, the 2Gb limit
  as you have stated previously. Possibly you could also post the patch
  for
  the 2Gb/4Gb limit?
  I'm hoping it's compatible with FR 1.1.7 as well.
 

 It is ok. I am happy to know it works for you. I will email you a
 patch for 1.1.7 in couple of days. The patch is going to impose
 certain limitations on you. The maximum return value should be less
 than unsigned integer(32bit). The maximum reply value for data will be
 limited to 4GB even if actual value is more than 4GB. So, there will
 be a per session limit of 4GB though user is authorized to transfer
 more data.

 Regards,

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


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


 Regards,

 --
 Venkatesh. K




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

Re: sqlcounter returning wrong value?

[Flamur Rogova]

liran tal wrote:
 
Waiting for that traffic limitation patch, Venkatesh.

Thanks.



Hi,
I was stuck with this problem too, and I came up with this solution, 
which works in my test environment.


The idea is to store allowed bytes in Tmp-Integer-0, than just use 
unlang to compare user's allowed and actual traffic bytes.


btw, maximum traffic count is 2^31 bytes, if you do it this way.


if(control:Tmp-Integer-0) {
if(%{sql:SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM radacct 
WHERE UserName='%{User-Name}' }  %{control:Tmp-Integer-0} ) {

# traffic bytes limit reached
reject
}
}

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

Re: sqlcounter returning wrong value?

[Venkatesh K]

Hi,

On Wed, Nov 12, 2008 at 2:06 AM, liran tal [EMAIL PROTECTED] wrote:

 Waiting for that traffic limitation patch, Venkatesh.
 Thanks.

I am sorry. I had few busy days this week. You can expect a patch tomorrow.


 On Sun, Nov 9, 2008 at 6:00 AM, Venkatesh K [EMAIL PROTECTED] wrote:

 Hi Liran,

 On Sun, Nov 9, 2008 at 4:16 AM, liran tal [EMAIL PROTECTED] wrote:
  Hey Venkatesh,
 
  On Fri, Oct 31, 2008 at 2:26 AM, Venkatesh K [EMAIL PROTECTED] wrote:
 
  2008/10/31  [EMAIL PROTECTED]:
   It does make sense. rlm_sqlcounterworks like this toward the time of
   the
   reset: lets say you have an hour left, your limit is 20 hours and you
   have signed in 15 minutes before counter reset time.  When code
   calculates that you can be online at reset time it doesn't return
   your
   allowance (1 hour) but adds the limit for the next conting period (20
   hours) to the remaining time (15 minutes) and returns that value (20
   hours and 15 minutes). Reasoning is that your session shouldn't be
   discontinued after an hour becouse 15 minutes into the session new
   limit
   should come into force (and session limit can't be changed during the
   session).
  
   In your case there is about 2,000,000 left on the counter but only a
   few
   thousand seconds left to the end of the reset period, so code will
   add
   those few thousands to the next period limit (26,000,000) and return
   that value. Code doesn't know are you counting data or time as
   there is  no such configuration item.
  
   Venkatesh had posted the patch that switches off this peace of code
   for
   data counters by introducing that configuration item. You should try
   it.
 
  rlm_sqlcounter has one more limitation. In version 1.1.7, the maximum
  counter value was limited to 2G whereas in 2.1.1 it seems to be 4G.
  This imposes an artificial limitation of maximum of 4GB of downloads.
  I had a workaround where I patched rlm_sqlcounter to limit the per
  session downloads to 4GB if allowed usage exceeds 4GB.
 
  Except this issue, I think, with the patch I posted earlier, one
  should be fine with rlm_sqlcounter. If someone needs a patch to work
  around the 2GB/4GB limit, I will post the patch.
 
 
  Sorry for the late reply.
  I applied your patch and now data counters work as expected with a minor
  exception, the 2Gb limit
  as you have stated previously. Possibly you could also post the patch
  for
  the 2Gb/4Gb limit?
  I'm hoping it's compatible with FR 1.1.7 as well.
 

 It is ok. I am happy to know it works for you. I will email you a
 patch for 1.1.7 in couple of days. The patch is going to impose
 certain limitations on you. The maximum return value should be less
 than unsigned integer(32bit). The maximum reply value for data will be
 limited to 4GB even if actual value is more than 4GB. So, there will
 be a per session limit of 4GB though user is authorized to transfer
 more data.

 Regards,

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


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


Regards,

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

Re: sqlcounter returning wrong value?

[liran tal]

Hey,

Thanks for the tip, though that's FR2-specific solution and I'd like to be
able to get this sort out with older deployments
running 1.1.7 or earlier (god forbid! :-) )

That patch for rlm_sqlcounter would be ideal I think.
I think this should also be already pushed into the formal release, this
entire support for data information too.

Regards,
Liran.

On Wed, Nov 12, 2008 at 11:02 AM, Flamur Rogova [EMAIL PROTECTED] wrote:

 liran tal wrote:

  Waiting for that traffic limitation patch, Venkatesh.
 Thanks.


 Hi,
 I was stuck with this problem too, and I came up with this solution, which
 works in my test environment.

 The idea is to store allowed bytes in Tmp-Integer-0, than just use unlang
 to compare user's allowed and actual traffic bytes.

 btw, maximum traffic count is 2^31 bytes, if you do it this way.


 if(control:Tmp-Integer-0) {
if(%{sql:SELECT SUM(AcctOutputOctets+AcctInputOctets) FROM radacct
 WHERE UserName='%{User-Name}' }  %{control:Tmp-Integer-0} ) {
# traffic bytes limit reached
reject
}
 }

 Regards,
 Flamur

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

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

Re: sqlcounter returning wrong value?

[liran tal]

Waiting for that traffic limitation patch, Venkatesh.
Thanks.

On Sun, Nov 9, 2008 at 6:00 AM, Venkatesh K [EMAIL PROTECTED] wrote:

 Hi Liran,

 On Sun, Nov 9, 2008 at 4:16 AM, liran tal [EMAIL PROTECTED] wrote:
  Hey Venkatesh,
 
  On Fri, Oct 31, 2008 at 2:26 AM, Venkatesh K [EMAIL PROTECTED] wrote:
 
  2008/10/31  [EMAIL PROTECTED]:
   It does make sense. rlm_sqlcounterworks like this toward the time of
 the
   reset: lets say you have an hour left, your limit is 20 hours and you
   have signed in 15 minutes before counter reset time.  When code
   calculates that you can be online at reset time it doesn't return your
   allowance (1 hour) but adds the limit for the next conting period (20
   hours) to the remaining time (15 minutes) and returns that value (20
   hours and 15 minutes). Reasoning is that your session shouldn't be
   discontinued after an hour becouse 15 minutes into the session new
 limit
   should come into force (and session limit can't be changed during the
   session).
  
   In your case there is about 2,000,000 left on the counter but only a
 few
   thousand seconds left to the end of the reset period, so code will add
   those few thousands to the next period limit (26,000,000) and return
   that value. Code doesn't know are you counting data or time as
   there is  no such configuration item.
  
   Venkatesh had posted the patch that switches off this peace of code
 for
   data counters by introducing that configuration item. You should try
 it.
 
  rlm_sqlcounter has one more limitation. In version 1.1.7, the maximum
  counter value was limited to 2G whereas in 2.1.1 it seems to be 4G.
  This imposes an artificial limitation of maximum of 4GB of downloads.
  I had a workaround where I patched rlm_sqlcounter to limit the per
  session downloads to 4GB if allowed usage exceeds 4GB.
 
  Except this issue, I think, with the patch I posted earlier, one
  should be fine with rlm_sqlcounter. If someone needs a patch to work
  around the 2GB/4GB limit, I will post the patch.
 
 
  Sorry for the late reply.
  I applied your patch and now data counters work as expected with a minor
  exception, the 2Gb limit
  as you have stated previously. Possibly you could also post the patch for
  the 2Gb/4Gb limit?
  I'm hoping it's compatible with FR 1.1.7 as well.
 

 It is ok. I am happy to know it works for you. I will email you a
 patch for 1.1.7 in couple of days. The patch is going to impose
 certain limitations on you. The maximum return value should be less
 than unsigned integer(32bit). The maximum reply value for data will be
 limited to 4GB even if actual value is more than 4GB. So, there will
 be a per session limit of 4GB though user is authorized to transfer
 more data.

 Regards,

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

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

Re: sqlcounter returning wrong value?

[liran tal]

On Sun, Nov 9, 2008 at 6:00 AM, Venkatesh K [EMAIL PROTECTED] wrote:

 Hi Liran,

 On Sun, Nov 9, 2008 at 4:16 AM, liran tal [EMAIL PROTECTED] wrote:
  Hey Venkatesh,
 
  On Fri, Oct 31, 2008 at 2:26 AM, Venkatesh K [EMAIL PROTECTED] wrote:
 
  rlm_sqlcounter has one more limitation. In version 1.1.7, the maximum
  counter value was limited to 2G whereas in 2.1.1 it seems to be 4G.
  This imposes an artificial limitation of maximum of 4GB of downloads.
  I had a workaround where I patched rlm_sqlcounter to limit the per
  session downloads to 4GB if allowed usage exceeds 4GB.
 
  Sorry for the late reply.
  I applied your patch and now data counters work as expected with a minor
  exception, the 2Gb limit
  as you have stated previously. Possibly you could also post the patch for
  the 2Gb/4Gb limit?
  I'm hoping it's compatible with FR 1.1.7 as well.
 
 It is ok. I am happy to know it works for you. I will email you a
 patch for 1.1.7 in couple of days. The patch is going to impose
 certain limitations on you. The maximum return value should be less
 than unsigned integer(32bit). The maximum reply value for data will be
 limited to 4GB even if actual value is more than 4GB. So, there will
 be a per session limit of 4GB though user is authorized to transfer
 more data.

So the check value of the attribute remains a number bigger than 4GB, for
example an 8GB
limit but the reply attribute that is sent will contain a value of = 4GB
due to the limit?

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

Re: sqlcounter returning wrong value?

[Anders Holm]

Answers before questions? Novel idea.

limited to 4GB

Sent from my iPhone

On 9 Nov 2008, at 14:00, liran tal [EMAIL PROTECTED] wrote:



On Sun, Nov 9, 2008 at 6:00 AM, Venkatesh K [EMAIL PROTECTED] wrote:
Hi Liran,

On Sun, Nov 9, 2008 at 4:16 AM, liran tal [EMAIL PROTECTED]  
wrote:

 Hey Venkatesh,

 On Fri, Oct 31, 2008 at 2:26 AM, Venkatesh K [EMAIL PROTECTED]  
wrote:


 rlm_sqlcounter has one more limitation. In version 1.1.7, the  
maximum
 counter value was limited to 2G whereas in 2.1.1 it seems to be  
4G.
 This imposes an artificial limitation of maximum of 4GB of  
downloads.

 I had a workaround where I patched rlm_sqlcounter to limit the per
 session downloads to 4GB if allowed usage exceeds 4GB.

 Sorry for the late reply.
 I applied your patch and now data counters work as expected with a  
minor

 exception, the 2Gb limit
 as you have stated previously. Possibly you could also post the  
patch for

 the 2Gb/4Gb limit?
 I'm hoping it's compatible with FR 1.1.7 as well.

It is ok. I am happy to know it works for you. I will email you a
patch for 1.1.7 in couple of days. The patch is going to impose
certain limitations on you. The maximum return value should be less
than unsigned integer(32bit). The maximum reply value for data will be
limited to 4GB even if actual value is more than 4GB. So, there will
be a per session limit of 4GB though user is authorized to transfer
more data.
So the check value of the attribute remains a number bigger than  
4GB, for example an 8GB
limit but the reply attribute that is sent will contain a value of  
= 4GB due to the limit?


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

Re: sqlcounter returning wrong value?

[liran tal]

Hey Venkatesh,

On Fri, Oct 31, 2008 at 2:26 AM, Venkatesh K [EMAIL PROTECTED] wrote:

 2008/10/31  [EMAIL PROTECTED]:
  It does make sense. rlm_sqlcounterworks like this toward the time of the
  reset: lets say you have an hour left, your limit is 20 hours and you
  have signed in 15 minutes before counter reset time.  When code
  calculates that you can be online at reset time it doesn't return your
  allowance (1 hour) but adds the limit for the next conting period (20
  hours) to the remaining time (15 minutes) and returns that value (20
  hours and 15 minutes). Reasoning is that your session shouldn't be
  discontinued after an hour becouse 15 minutes into the session new limit
  should come into force (and session limit can't be changed during the
  session).
 
  In your case there is about 2,000,000 left on the counter but only a few
  thousand seconds left to the end of the reset period, so code will add
  those few thousands to the next period limit (26,000,000) and return
  that value. Code doesn't know are you counting data or time as
  there is  no such configuration item.
 
  Venkatesh had posted the patch that switches off this peace of code for
  data counters by introducing that configuration item. You should try it.

 rlm_sqlcounter has one more limitation. In version 1.1.7, the maximum
 counter value was limited to 2G whereas in 2.1.1 it seems to be 4G.
 This imposes an artificial limitation of maximum of 4GB of downloads.
 I had a workaround where I patched rlm_sqlcounter to limit the per
 session downloads to 4GB if allowed usage exceeds 4GB.

 Except this issue, I think, with the patch I posted earlier, one
 should be fine with rlm_sqlcounter. If someone needs a patch to work
 around the 2GB/4GB limit, I will post the patch.


Sorry for the late reply.
I applied your patch and now data counters work as expected with a minor
exception, the 2Gb limit
as you have stated previously. Possibly you could also post the patch for
the 2Gb/4Gb limit?
I'm hoping it's compatible with FR 1.1.7 as well.

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

Re: sqlcounter returning wrong value?

[Venkatesh K]

Hi Liran,

On Sun, Nov 9, 2008 at 4:16 AM, liran tal [EMAIL PROTECTED] wrote:
 Hey Venkatesh,

 On Fri, Oct 31, 2008 at 2:26 AM, Venkatesh K [EMAIL PROTECTED] wrote:

 2008/10/31  [EMAIL PROTECTED]:
  It does make sense. rlm_sqlcounterworks like this toward the time of the
  reset: lets say you have an hour left, your limit is 20 hours and you
  have signed in 15 minutes before counter reset time.  When code
  calculates that you can be online at reset time it doesn't return your
  allowance (1 hour) but adds the limit for the next conting period (20
  hours) to the remaining time (15 minutes) and returns that value (20
  hours and 15 minutes). Reasoning is that your session shouldn't be
  discontinued after an hour becouse 15 minutes into the session new limit
  should come into force (and session limit can't be changed during the
  session).
 
  In your case there is about 2,000,000 left on the counter but only a few
  thousand seconds left to the end of the reset period, so code will add
  those few thousands to the next period limit (26,000,000) and return
  that value. Code doesn't know are you counting data or time as
  there is  no such configuration item.
 
  Venkatesh had posted the patch that switches off this peace of code for
  data counters by introducing that configuration item. You should try it.

 rlm_sqlcounter has one more limitation. In version 1.1.7, the maximum
 counter value was limited to 2G whereas in 2.1.1 it seems to be 4G.
 This imposes an artificial limitation of maximum of 4GB of downloads.
 I had a workaround where I patched rlm_sqlcounter to limit the per
 session downloads to 4GB if allowed usage exceeds 4GB.

 Except this issue, I think, with the patch I posted earlier, one
 should be fine with rlm_sqlcounter. If someone needs a patch to work
 around the 2GB/4GB limit, I will post the patch.


 Sorry for the late reply.
 I applied your patch and now data counters work as expected with a minor
 exception, the 2Gb limit
 as you have stated previously. Possibly you could also post the patch for
 the 2Gb/4Gb limit?
 I'm hoping it's compatible with FR 1.1.7 as well.


It is ok. I am happy to know it works for you. I will email you a
patch for 1.1.7 in couple of days. The patch is going to impose
certain limitations on you. The maximum return value should be less
than unsigned integer(32bit). The maximum reply value for data will be
limited to 4GB even if actual value is more than 4GB. So, there will
be a per session limit of 4GB though user is authorized to transfer
more data.

Regards,

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

Re: sqlcounter returning wrong value?

[liran tal]

Well, taking this issue back to the begining - it all started with my report
that using the attribute Chilli-Max-Total-Octets
in the sqlcounter provided as follows yields wrong results for all of the
reset times (daily/weekly/monthly):

sqlcounter counterChilliSpotMaxDailyOctets {
counter-name = ChilliSpot-Max-Daily-Octets
check-name = ChilliSpot-Max-Daily-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = daily
error-msg = Sorry, your maximum traffic usage (download and
upload) has exceed the provided limit
query = SELECT (SUM(AcctInputOctets + AcctOutputOctets))
FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) +
AcctSessionTime  '%b'
}

For a user defined with the following entry in radcheck:
| 346 | tester1 | ChilliSpot-Max-Daily-Octets | := | 26214400 |

The result returned by freeradius is:
check_item=26214400 and counter=24004370, return value=26239950 (which makes
no sense).



Regards,
Liran.


2008/10/30 Venkatesh K [EMAIL PROTECTED]

 The patch I posted was out of the hat just to skip the routine
 which adds additional quota.

 We have stopped using rlm_sqlcounter long back as we moved
 from radius schema to our own custom schema and patched
 the rlm_sql to work with our custom schema.

 If you or someone list out what one would like to see in rlm_sql_counter,
 I will try to come up with the patch. Back porting to previous versions
 of radius should not be a big problem as rlm_sql and rlm_sqlcounter
 have't changed much.

 Thanks,

 Venkatesh K


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

Re: sqlcounter returning wrong value?

[tnt]

It does make sense. rlm_sqlcounterworks like this toward the time of the
reset: lets say you have an hour left, your limit is 20 hours and you
have signed in 15 minutes before counter reset time.  When code
calculates that you can be online at reset time it doesn't return your
allowance (1 hour) but adds the limit for the next conting period (20
hours) to the remaining time (15 minutes) and returns that value (20
hours and 15 minutes). Reasoning is that your session shouldn't be
discontinued after an hour becouse 15 minutes into the session new limit
should come into force (and session limit can't be changed during the
session).

In your case there is about 2,000,000 left on the counter but only a few
thousand seconds left to the end of the reset period, so code will add
those few thousands to the next period limit (26,000,000) and return
that value. Code doesn't know are you counting data or time as
there is  no such configuration item.

Venkatesh had posted the patch that switches off this peace of code for
data counters by introducing that configuration item. You should try it.

Ivan Kalik
Kalik Informatika ISP


Dana 30/10/2008, liran tal [EMAIL PROTECTED] piše:

Well, taking this issue back to the begining - it all started with my report
that using the attribute Chilli-Max-Total-Octets
in the sqlcounter provided as follows yields wrong results for all of the
reset times (daily/weekly/monthly):

sqlcounter counterChilliSpotMaxDailyOctets {
counter-name = ChilliSpot-Max-Daily-Octets
check-name = ChilliSpot-Max-Daily-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = daily
error-msg = Sorry, your maximum traffic usage (download and
upload) has exceed the provided limit
query = SELECT (SUM(AcctInputOctets + AcctOutputOctets))
FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) +
AcctSessionTime  '%b'
}

For a user defined with the following entry in radcheck:
| 346 | tester1 | ChilliSpot-Max-Daily-Octets | := | 26214400 |

The result returned by freeradius is:
check_item=26214400 and counter=24004370, return value=26239950 (which makes
no sense).



Regards,
Liran.


2008/10/30 Venkatesh K [EMAIL PROTECTED]

 The patch I posted was out of the hat just to skip the routine
 which adds additional quota.

 We have stopped using rlm_sqlcounter long back as we moved
 from radius schema to our own custom schema and patched
 the rlm_sql to work with our custom schema.

 If you or someone list out what one would like to see in rlm_sql_counter,
 I will try to come up with the patch. Back porting to previous versions
 of radius should not be a big problem as rlm_sql and rlm_sqlcounter
 have't changed much.

 Thanks,

 Venkatesh K





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

Re: sqlcounter returning wrong value?

[Venkatesh K]

2008/10/31  [EMAIL PROTECTED]:
 It does make sense. rlm_sqlcounterworks like this toward the time of the
 reset: lets say you have an hour left, your limit is 20 hours and you
 have signed in 15 minutes before counter reset time.  When code
 calculates that you can be online at reset time it doesn't return your
 allowance (1 hour) but adds the limit for the next conting period (20
 hours) to the remaining time (15 minutes) and returns that value (20
 hours and 15 minutes). Reasoning is that your session shouldn't be
 discontinued after an hour becouse 15 minutes into the session new limit
 should come into force (and session limit can't be changed during the
 session).

 In your case there is about 2,000,000 left on the counter but only a few
 thousand seconds left to the end of the reset period, so code will add
 those few thousands to the next period limit (26,000,000) and return
 that value. Code doesn't know are you counting data or time as
 there is  no such configuration item.

 Venkatesh had posted the patch that switches off this peace of code for
 data counters by introducing that configuration item. You should try it.

rlm_sqlcounter has one more limitation. In version 1.1.7, the maximum
counter value was limited to 2G whereas in 2.1.1 it seems to be 4G.
This imposes an artificial limitation of maximum of 4GB of downloads.
I had a workaround where I patched rlm_sqlcounter to limit the per
session downloads to 4GB if allowed usage exceeds 4GB.

Except this issue, I think, with the patch I posted earlier, one
should be fine with rlm_sqlcounter. If someone needs a patch to work
around the 2GB/4GB limit, I will post the patch.

Regards,

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

Re: sqlcounter returning wrong value?

[liran tal]

Thanks for the patch, I have not checked it yet.
What is the status on this issue though? A patch is nice but I'd like to see
support for data counters
to find it's place in the newer version as well as the old one (well, a
backport would be nice. Many of us still have
production systems running 1.1.X)

What still disturbs me is that many of the hotspot forums (coovachilli and
chillispot) has post on how to add an sqlcounter
which performs these data queries successfully and user's feedback is that
everything is good and working. Not that it's
something to count on but I'd guess if somsone had an issue then we'd know
about it...


Regards,
Liran.



2008/10/25 Venkatesh K [EMAIL PROTECTED]

 I have had hard time using sqlcounter for data. It has (at least had)
 limitations for counting data like 2GB limit. I ended up writing my
 own code for counting data.

 Here is a small patch which will skip adding additional quota if
 counter-type=time in sqlcounter.conf file.


 
 diff -u
 ../../../freeradius-1.1.7.orig/src/modules/rlm_sqlcounter/rlm_sqlcounter.c
 rlm_sqlcounter/rlm_sqlcounter.c
 ---
 ../../../freeradius-1.1.7.orig/src/modules/rlm_sqlcounter/rlm_sqlcounter.c
  2007-04-08
 04:51:42.0 +0530
 +++ rlm_sqlcounter/rlm_sqlcounter.c 2008-10-25 22:05:58.0 +0530
 @@ -72,6 +72,7 @@
char *sqlmod_inst;  /* instance of SQL module to use, usually
 just 'sql' */
char *query;/* SQL query to retrieve current session
 time */
char *reset;/* daily, weekly, monthly, never or user
 defined */
 +char *counter_type;/* Type of counter (data / time) */
char *allowed_chars;/* safe characters list for SQL queries */
time_t reset_time;
time_t last_reset;
 @@ -97,6 +98,7 @@
   { sqlmod-inst, PW_TYPE_STRING_PTR,
 offsetof(rlm_sqlcounter_t,sqlmod_inst), NULL, NULL },
   { query, PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,query),
 NULL, NULL },
   { reset, PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,reset),
 NULL,  NULL },
 +  { counter-type, PW_TYPE_STRING_PTR,
 offsetof(rlm_sqlcounter_t,counter_type), NULL,  NULL },
   { safe-characters, PW_TYPE_STRING_PTR,
 offsetof(rlm_sqlcounter_t,allowed_chars), NULL,
 @abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_:
 /},
   { NULL, -1, 0, NULL, NULL }
  };
 @@ -675,10 +677,13 @@
 *  limit, so that the user will not need to
 *  login again
 */
 -   if (data-reset_time  (
 -   res = (data-reset_time - request-timestamp))) {
 -   res = data-reset_time - request-timestamp;
 -   res += check_vp-lvalue;
 +   if(strcmp(data-counter_type,time)==0)
 +   {
 +   if (data-reset_time  (
 +   res = (data-reset_time -
 request-timestamp))) {
 +   res = data-reset_time -
 request-timestamp;
 +   res += check_vp-lvalue;
 +   }
}

if ((reply_item = pairfind(request-reply-vps,
 data-reply_attr)) != NULL) {

 -

 I did compile the code successfully. I have not checked it. Let me
 know if  you have
 any issues.

 Thanks,

 Venkatesh K

 2008/10/25  [EMAIL PROTECTED]:
   And they won't. It's nothing to do with the settings - it's this peace
  of the code.
 
  Let's take your example. Limit was 26MB and about 2MB was left.
  2,000,000 seconds is about 23 days. So this part of the code will kick
  in (there are 6 days left in this month) and returned value will be
  26MB + number of seconds untill 1.11. Run debug twice. You will see that
  the returned value will be reduced by the number of seconds between two
  requests.
 
  Allowance left would have to be well below 1MB for data counter to start
  working properly for monthly reset towards the end of the month, 100KB
  for weekly reset and 10KB for daily. Which is of no practical use.
 
  I don't know when was this part of the code added but data counters
  would work fine without it. I was testing time counter and still
  reproduced this only because limit value was so high (10 million). When
  I reduced it to 10,000 it worked fine.
 
  Ivan Kalik
  Kalik Informatika ISP
 
 
  Dana 25/10/2008, liran tal [EMAIL PROTECTED] piše:
 
 I've actually tested the sqlcounter for a data counter for both weekly
 and
 monthly resets and that doesn't work well either...
 This is odd because people have reported this working so... it is either
 something in the configuration that I'm missing
 or I really don't know what's going on but the counter seem to behave as
 expected.
 
 
 Regards

Re: sqlcounter returning wrong value?

[Venkatesh K]

The patch I posted was out of the hat just to skip the routine
which adds additional quota.

We have stopped using rlm_sqlcounter long back as we moved
from radius schema to our own custom schema and patched
the rlm_sql to work with our custom schema.

If you or someone list out what one would like to see in rlm_sql_counter,
I will try to come up with the patch. Back porting to previous versions
of radius should not be a big problem as rlm_sql and rlm_sqlcounter
have't changed much.

Thanks,

Venkatesh K

2008/10/30 liran tal [EMAIL PROTECTED]:

 Thanks for the patch, I have not checked it yet.
 What is the status on this issue though? A patch is nice but I'd like to see
 support for data counters
 to find it's place in the newer version as well as the old one (well, a
 backport would be nice. Many of us still have
 production systems running 1.1.X)

 What still disturbs me is that many of the hotspot forums (coovachilli and
 chillispot) has post on how to add an sqlcounter
 which performs these data queries successfully and user's feedback is that
 everything is good and working. Not that it's
 something to count on but I'd guess if somsone had an issue then we'd know
 about it...


 Regards,
 Liran.


 2008/10/25 Venkatesh K [EMAIL PROTECTED]

 I have had hard time using sqlcounter for data. It has (at least had)
 limitations for counting data like 2GB limit. I ended up writing my
 own code for counting data.

 Here is a small patch which will skip adding additional quota if
 counter-type=time in sqlcounter.conf file.


 
 diff -u
 ../../../freeradius-1.1.7.orig/src/modules/rlm_sqlcounter/rlm_sqlcounter.c
 rlm_sqlcounter/rlm_sqlcounter.c
 ---
 ../../../freeradius-1.1.7.orig/src/modules/rlm_sqlcounter/rlm_sqlcounter.c
  2007-04-08
 04:51:42.0 +0530
 +++ rlm_sqlcounter/rlm_sqlcounter.c 2008-10-25 22:05:58.0
 +0530
 @@ -72,6 +72,7 @@
char *sqlmod_inst;  /* instance of SQL module to use, usually
 just 'sql' */
char *query;/* SQL query to retrieve current session
 time */
char *reset;/* daily, weekly, monthly, never or user
 defined */
 +char *counter_type;/* Type of counter (data / time) */
char *allowed_chars;/* safe characters list for SQL queries */
time_t reset_time;
time_t last_reset;
 @@ -97,6 +98,7 @@
   { sqlmod-inst, PW_TYPE_STRING_PTR,
 offsetof(rlm_sqlcounter_t,sqlmod_inst), NULL, NULL },
   { query, PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,query),
 NULL, NULL },
   { reset, PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,reset),
 NULL,  NULL },
 +  { counter-type, PW_TYPE_STRING_PTR,
 offsetof(rlm_sqlcounter_t,counter_type), NULL,  NULL },
   { safe-characters, PW_TYPE_STRING_PTR,
 offsetof(rlm_sqlcounter_t,allowed_chars), NULL,
 @abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_:
 /},
   { NULL, -1, 0, NULL, NULL }
  };
 @@ -675,10 +677,13 @@
 *  limit, so that the user will not need to
 *  login again
 */
 -   if (data-reset_time  (
 -   res = (data-reset_time - request-timestamp))) {
 -   res = data-reset_time - request-timestamp;
 -   res += check_vp-lvalue;
 +   if(strcmp(data-counter_type,time)==0)
 +   {
 +   if (data-reset_time  (
 +   res = (data-reset_time -
 request-timestamp))) {
 +   res = data-reset_time -
 request-timestamp;
 +   res += check_vp-lvalue;
 +   }
}

if ((reply_item = pairfind(request-reply-vps,
 data-reply_attr)) != NULL) {

 -

 I did compile the code successfully. I have not checked it. Let me
 know if  you have
 any issues.

 Thanks,

 Venkatesh K

 2008/10/25  [EMAIL PROTECTED]:
  And they won't. It's nothing to do with the settings - it's this peace
  of the code.
 
  Let's take your example. Limit was 26MB and about 2MB was left.
  2,000,000 seconds is about 23 days. So this part of the code will kick
  in (there are 6 days left in this month) and returned value will be
  26MB + number of seconds untill 1.11. Run debug twice. You will see that
  the returned value will be reduced by the number of seconds between two
  requests.
 
  Allowance left would have to be well below 1MB for data counter to start
  working properly for monthly reset towards the end of the month, 100KB
  for weekly reset and 10KB for daily. Which is of no practical use.
 
  I don't know when was this part of the code added but data counters
  would work fine without it. I was testing time counter and still

Re: sqlcounter returning wrong value?

[tnt]

OK. This where the problem comes from:

/*
 *  If we are near a reset then add the next
 *  limit, so that the user will not need to
 *  login again
 */
if (data-reset_time 
(res = (data-reset_time - request-timestamp))) {
res = data-reset_time - request-timestamp;
res += check_vp-vp_integer;
}

(that's rlm_sqlcounter.c line about 710 in 2.0.5)

Sqlcounter was designed for time counters. When your allowance (limit -
time used) is greater than the time left in the period, time left in the
period is added to the limit and sent as reply.

This will create problems for data counters since limit values are much
greater than those for time counters. As a workaround I would suggest
that you comment this out if you are using data counters.

Perhaps there is a case for adding another configuration item to
sqlcounter that would determine what is counted time or data. Then for
data counters this can be skipped or replaced with something like:

if (data-reset_time  (trigger = (data-reset_time -
request-timestamp))) {
 res += check_vp-vp_integer;
}

where trigger would be set to a minute for hourly counter and hour for
daily, weekly and monthly counters. I am sorry but I don't know how to
write a patch.

Ivan Kalik
Kalik Informatika ISP

Dana 24/10/2008, liran tal [EMAIL PROTECTED] piše:

Hey Ivan

2008/10/24 [EMAIL PROTECTED]

 It (daily sqlcounter) does the same in 2.0.5:

 rlm_sqlcounter: Authorized user jagoda, check_item=1000, counter=2635
 rlm_sqlcounter: Sent Reply-Item for user jagoda, Type=Session-Timeout,
 value=10027850

 Returns value that is greater than the limit. I am using noreset
 sqlcounter and that one works fine.


Thanks for confirming this on a more up to date version.
Alan, this smells like a bug (unless we missed something along the way),
should I open up a bug ticket?
And what would be the chances it can be backported to 1.1.7?

Thanks,
Liran.

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

Re: sqlcounter returning wrong value?

[liran tal]

I've actually tested the sqlcounter for a data counter for both weekly and
monthly resets and that doesn't work well either...
This is odd because people have reported this working so... it is either
something in the configuration that I'm missing
or I really don't know what's going on but the counter seem to behave as
expected.


Regards,
Liran.

2008/10/25 [EMAIL PROTECTED]

 OK. This where the problem comes from:

/*
 *  If we are near a reset then add the next
 *  limit, so that the user will not need to
 *  login again
 */
if (data-reset_time 
(res = (data-reset_time - request-timestamp))) {
res = data-reset_time - request-timestamp;
res += check_vp-vp_integer;
}

 (that's rlm_sqlcounter.c line about 710 in 2.0.5)

 Sqlcounter was designed for time counters. When your allowance (limit -
 time used) is greater than the time left in the period, time left in the
 period is added to the limit and sent as reply.

 This will create problems for data counters since limit values are much
 greater than those for time counters. As a workaround I would suggest
 that you comment this out if you are using data counters.

 Perhaps there is a case for adding another configuration item to
 sqlcounter that would determine what is counted time or data. Then for
 data counters this can be skipped or replaced with something like:

 if (data-reset_time  (trigger = (data-reset_time -
 request-timestamp))) {
 res += check_vp-vp_integer;
 }

 where trigger would be set to a minute for hourly counter and hour for
 daily, weekly and monthly counters. I am sorry but I don't know how to
 write a patch.

 Ivan Kalik
 Kalik Informatika ISP

 Dana 24/10/2008, liran tal [EMAIL PROTECTED] piše:

 Hey Ivan
 
 2008/10/24 [EMAIL PROTECTED]
 
  It (daily sqlcounter) does the same in 2.0.5:
 
  rlm_sqlcounter: Authorized user jagoda, check_item=1000,
 counter=2635
  rlm_sqlcounter: Sent Reply-Item for user jagoda, Type=Session-Timeout,
  value=10027850
 
  Returns value that is greater than the limit. I am using noreset
  sqlcounter and that one works fine.
 
 
 Thanks for confirming this on a more up to date version.
 Alan, this smells like a bug (unless we missed something along the way),
 should I open up a bug ticket?
 And what would be the chances it can be backported to 1.1.7?
 
 Thanks,
 Liran.

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

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

Re: sqlcounter returning wrong value?

[tnt]

And they won't. It's nothing to do with the settings - it's this peace
of the code.

Let's take your example. Limit was 26MB and about 2MB was left.
2,000,000 seconds is about 23 days. So this part of the code will kick
in (there are 6 days left in this month) and returned value will be
26MB + number of seconds untill 1.11. Run debug twice. You will see that
the returned value will be reduced by the number of seconds between two
requests.

Allowance left would have to be well below 1MB for data counter to start
working properly for monthly reset towards the end of the month, 100KB
for weekly reset and 10KB for daily. Which is of no practical use.

I don't know when was this part of the code added but data counters
would work fine without it. I was testing time counter and still
reproduced this only because limit value was so high (10 million). When
I reduced it to 10,000 it worked fine.

Ivan Kalik
Kalik Informatika ISP


Dana 25/10/2008, liran tal [EMAIL PROTECTED] piše:

I've actually tested the sqlcounter for a data counter for both weekly and
monthly resets and that doesn't work well either...
This is odd because people have reported this working so... it is either
something in the configuration that I'm missing
or I really don't know what's going on but the counter seem to behave as
expected.


Regards,
Liran.

2008/10/25 [EMAIL PROTECTED]

 OK. This where the problem comes from:

/*
 *  If we are near a reset then add the next
 *  limit, so that the user will not need to
 *  login again
 */
if (data-reset_time 
(res = (data-reset_time - request-timestamp))) {
res = data-reset_time - request-timestamp;
res += check_vp-vp_integer;
}

 (that's rlm_sqlcounter.c line about 710 in 2.0.5)

 Sqlcounter was designed for time counters. When your allowance (limit -
 time used) is greater than the time left in the period, time left in the
 period is added to the limit and sent as reply.

 This will create problems for data counters since limit values are much
 greater than those for time counters. As a workaround I would suggest
 that you comment this out if you are using data counters.

 Perhaps there is a case for adding another configuration item to
 sqlcounter that would determine what is counted time or data. Then for
 data counters this can be skipped or replaced with something like:

 if (data-reset_time  (trigger = (data-reset_time -
 request-timestamp))) {
 res += check_vp-vp_integer;
 }

 where trigger would be set to a minute for hourly counter and hour for
 daily, weekly and monthly counters. I am sorry but I don't know how to
 write a patch.

 Ivan Kalik
 Kalik Informatika ISP

 Dana 24/10/2008, liran tal [EMAIL PROTECTED] piše:

 Hey Ivan
 
 2008/10/24 [EMAIL PROTECTED]
 
  It (daily sqlcounter) does the same in 2.0.5:
 
  rlm_sqlcounter: Authorized user jagoda, check_item=1000,
 counter=2635
  rlm_sqlcounter: Sent Reply-Item for user jagoda, Type=Session-Timeout,
  value=10027850
 
  Returns value that is greater than the limit. I am using noreset
  sqlcounter and that one works fine.
 
 
 Thanks for confirming this on a more up to date version.
 Alan, this smells like a bug (unless we missed something along the way),
 should I open up a bug ticket?
 And what would be the chances it can be backported to 1.1.7?
 
 Thanks,
 Liran.

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



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

Re: sqlcounter returning wrong value?

[Venkatesh K]

I have had hard time using sqlcounter for data. It has (at least had)
limitations for counting data like 2GB limit. I ended up writing my
own code for counting data.

Here is a small patch which will skip adding additional quota if
counter-type=time in sqlcounter.conf file.


diff -u 
../../../freeradius-1.1.7.orig/src/modules/rlm_sqlcounter/rlm_sqlcounter.c
rlm_sqlcounter/rlm_sqlcounter.c
--- ../../../freeradius-1.1.7.orig/src/modules/rlm_sqlcounter/rlm_sqlcounter.c  
2007-04-08
04:51:42.0 +0530
+++ rlm_sqlcounter/rlm_sqlcounter.c 2008-10-25 22:05:58.0 +0530
@@ -72,6 +72,7 @@
char *sqlmod_inst;  /* instance of SQL module to use, usually just 
'sql' */
char *query;/* SQL query to retrieve current session time */
char *reset;/* daily, weekly, monthly, never or user 
defined */
+char *counter_type;/* Type of counter (data / time) */
char *allowed_chars;/* safe characters list for SQL queries */
time_t reset_time;
time_t last_reset;
@@ -97,6 +98,7 @@
   { sqlmod-inst, PW_TYPE_STRING_PTR,
offsetof(rlm_sqlcounter_t,sqlmod_inst), NULL, NULL },
   { query, PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,query),
NULL, NULL },
   { reset, PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,reset),
NULL,  NULL },
+  { counter-type, PW_TYPE_STRING_PTR,
offsetof(rlm_sqlcounter_t,counter_type), NULL,  NULL },
   { safe-characters, PW_TYPE_STRING_PTR,
offsetof(rlm_sqlcounter_t,allowed_chars), NULL,
@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_:
/},
   { NULL, -1, 0, NULL, NULL }
 };
@@ -675,10 +677,13 @@
 *  limit, so that the user will not need to
 *  login again
 */
-   if (data-reset_time  (
-   res = (data-reset_time - request-timestamp))) {
-   res = data-reset_time - request-timestamp;
-   res += check_vp-lvalue;
+   if(strcmp(data-counter_type,time)==0)
+   {
+   if (data-reset_time  (
+   res = (data-reset_time - 
request-timestamp))) {
+   res = data-reset_time - request-timestamp;
+   res += check_vp-lvalue;
+   }
}

if ((reply_item = pairfind(request-reply-vps, 
data-reply_attr)) != NULL) {
-

I did compile the code successfully. I have not checked it. Let me
know if  you have
any issues.

Thanks,

Venkatesh K

2008/10/25  [EMAIL PROTECTED]:
 And they won't. It's nothing to do with the settings - it's this peace
 of the code.

 Let's take your example. Limit was 26MB and about 2MB was left.
 2,000,000 seconds is about 23 days. So this part of the code will kick
 in (there are 6 days left in this month) and returned value will be
 26MB + number of seconds untill 1.11. Run debug twice. You will see that
 the returned value will be reduced by the number of seconds between two
 requests.

 Allowance left would have to be well below 1MB for data counter to start
 working properly for monthly reset towards the end of the month, 100KB
 for weekly reset and 10KB for daily. Which is of no practical use.

 I don't know when was this part of the code added but data counters
 would work fine without it. I was testing time counter and still
 reproduced this only because limit value was so high (10 million). When
 I reduced it to 10,000 it worked fine.

 Ivan Kalik
 Kalik Informatika ISP


 Dana 25/10/2008, liran tal [EMAIL PROTECTED] piše:

I've actually tested the sqlcounter for a data counter for both weekly and
monthly resets and that doesn't work well either...
This is odd because people have reported this working so... it is either
something in the configuration that I'm missing
or I really don't know what's going on but the counter seem to behave as
expected.


Regards,
Liran.

2008/10/25 [EMAIL PROTECTED]

 OK. This where the problem comes from:

/*
 *  If we are near a reset then add the next
 *  limit, so that the user will not need to
 *  login again
 */
if (data-reset_time 
(res = (data-reset_time - request-timestamp))) {
res = data-reset_time - request-timestamp;
res += check_vp-vp_integer;
}

 (that's rlm_sqlcounter.c line about 710 in 2.0.5)

 Sqlcounter was designed for time counters. When your allowance (limit -
 time used) is greater than the time left in the period, time left in the
 period is added to the limit and sent as reply

RE: sqlcounter returning wrong value?

[mulianto]

hi..i think you should fix this one :

reply-name = ChilliSpot-Max-Total-Octets to :
reply-name = Session-Timeout

try it..
rgds,
Mulianto

http://www.indohotspot.net
Your Hotspot solution
  -Original Message-
  From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Behalf Of liran tal
  Sent: Friday, October 24, 2008 1:07 AM
  To: FreeRadius users mailing list
  Subject: sqlcounter returning wrong value?



  Hey,

  I'm experimenting with some sqlcounter directives in radiusd.conf and
chilli as the NAS.
  I've defined the following sqlcounter stanza for a daily traffic limit:

  sqlcounter defined in radiusd.conf:
  (the query was corrected as suggested by tnt on a previous thread on the
list, correct me if I got it wrong please)

  sqlcounter counterChilliSpotMaxDailyOctets {
  counter-name = ChilliSpot-Max-Daily-Octets
  check-name = ChilliSpot-Max-Daily-Octets
  reply-name = ChilliSpot-Max-Total-Octets
  sqlmod-inst = sql
  key = User-Name
  reset = daily
  error-msg = Sorry, your maximum traffic usage (download
and upload) has exceed the provided limit
  query = SELECT (SUM(AcctInputOctets + AcctOutputOctets))
FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) +
AcctSessionTime  '%b'
  }



  In the authorization phase, I'm seeing the following in debug log:
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
FROM radacct WHERE UserName='%{User-Name}''
radius_xlat:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
radacct WHERE UserName='tester1''
sqlcounter_expand:  '%{sql:SELECT
(SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='tester1'}'
radius_xlat: Running registered xlat function of module sql for string
'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='tester1''
rlm_sql (sql): - sql_xlat
radius_xlat:  'tester1'
rlm_sql (sql): sql_set_user escaped user -- 'tester1'
radius_xlat:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
radacct WHERE UserName='tester1''
rlm_sql (sql): Reserving sql socket id: 2
rlm_sql (sql): - sql_xlat finished
rlm_sql (sql): Released sql socket id: 2
radius_xlat:  '24004370'
rlm_sqlcounter: (Check item - counter) is greater than zero
rlm_sqlcounter: Authorized user tester1, check_item=26214400,
counter=24004370
rlm_sqlcounter: Sent Reply-Item for user tester1,
Type=ChilliSpot-Max-Total-Octets, value=26239950
  modcall[authorize]: module counterChilliSpotMaxDailyOctets returns
ok for request 0
  The entry in radcheck is as follows:

| 346 | tester1 | ChilliSpot-Max-Daily-Octets | := | 26214400 |
  What happens is that it seems the counter doesn't work as expected. When a
user logs in, performs some traffic usage, logs out and logs in again,
  the replied back attribute for chilli doesn't contain a value which is the
remainder of the traffic usage, but something else.

  According to the radius debug above, if check_item=26214400 and
counter=24004370, how come value=26239950?
  So I'm guessing I'm missing something but I'm too obsessed with the
sqlcounter to notice it. (is the subtractation not a normal decimal action?)

  The FreeRADIUS version used is 1.1.7



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

RE: sqlcounter returning wrong value?

[tnt]

No, he wants a data not time counter.

Ivan Kalik
Kalik Informatika ISP


Dana 24/10/2008, mulianto [EMAIL PROTECTED] piše:

hi..i think you should fix this one :

reply-name = ChilliSpot-Max-Total-Octets to :
reply-name = Session-Timeout

try it..
rgds,
Mulianto

http://www.indohotspot.net
Your Hotspot solution
  -Original Message-
  From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Behalf Of liran tal
  Sent: Friday, October 24, 2008 1:07 AM
  To: FreeRadius users mailing list
  Subject: sqlcounter returning wrong value?



  Hey,

  I'm experimenting with some sqlcounter directives in radiusd.conf and
chilli as the NAS.
  I've defined the following sqlcounter stanza for a daily traffic limit:

  sqlcounter defined in radiusd.conf:
  (the query was corrected as suggested by tnt on a previous thread on the
list, correct me if I got it wrong please)

  sqlcounter counterChilliSpotMaxDailyOctets {
  counter-name = ChilliSpot-Max-Daily-Octets
  check-name = ChilliSpot-Max-Daily-Octets
  reply-name = ChilliSpot-Max-Total-Octets
  sqlmod-inst = sql
  key = User-Name
  reset = daily
  error-msg = Sorry, your maximum traffic usage (download
and upload) has exceed the provided limit
  query = SELECT (SUM(AcctInputOctets + AcctOutputOctets))
FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) +
AcctSessionTime  '%b'
  }



  In the authorization phase, I'm seeing the following in debug log:
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
FROM radacct WHERE UserName='%{User-Name}''
radius_xlat:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
radacct WHERE UserName='tester1''
sqlcounter_expand:  '%{sql:SELECT
(SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='tester1'}'
radius_xlat: Running registered xlat function of module sql for string
'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='tester1''
rlm_sql (sql): - sql_xlat
radius_xlat:  'tester1'
rlm_sql (sql): sql_set_user escaped user -- 'tester1'
radius_xlat:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
radacct WHERE UserName='tester1''
rlm_sql (sql): Reserving sql socket id: 2
rlm_sql (sql): - sql_xlat finished
rlm_sql (sql): Released sql socket id: 2
radius_xlat:  '24004370'
rlm_sqlcounter: (Check item - counter) is greater than zero
rlm_sqlcounter: Authorized user tester1, check_item=26214400,
counter=24004370
rlm_sqlcounter: Sent Reply-Item for user tester1,
Type=ChilliSpot-Max-Total-Octets, value=26239950
  modcall[authorize]: module counterChilliSpotMaxDailyOctets returns
ok for request 0
  The entry in radcheck is as follows:

| 346 | tester1 | ChilliSpot-Max-Daily-Octets | := | 26214400 |
  What happens is that it seems the counter doesn't work as expected. When a
user logs in, performs some traffic usage, logs out and logs in again,
  the replied back attribute for chilli doesn't contain a value which is the
remainder of the traffic usage, but something else.

  According to the radius debug above, if check_item=26214400 and
counter=24004370, how come value=26239950?
  So I'm guessing I'm missing something but I'm too obsessed with the
sqlcounter to notice it. (is the subtractation not a normal decimal action?)

  The FreeRADIUS version used is 1.1.7



  Regards,
  Liran.



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

Re: sqlcounter returning wrong value?

[liran tal]

Hey,

2008/10/24 [EMAIL PROTECTED]

 No, he wants a data not time counter.


That's right Evan. Moreover, there is no sense in changing the attribute to
be Session-Timeout when Chilli expects something else.
Any thoughts on this issue?


Thanks,



Dana 24/10/2008, mulianto [EMAIL PROTECTED] piše:


 hi..i think you should fix this one :
 
 reply-name = ChilliSpot-Max-Total-Octets to :
 reply-name = Session-Timeout
 
 try it..
 rgds,
 Mulianto
 
 http://www.indohotspot.net
 Your Hotspot solution
   -Original Message-
   From: [EMAIL PROTECTED]
 [mailto:freeradius-users-bounces+muliantofreeradius-users-bounces%2Bmulianto
 [EMAIL PROTECTED]
 Behalf Of liran tal
   Sent: Friday, October 24, 2008 1:07 AM
   To: FreeRadius users mailing list
   Subject: sqlcounter returning wrong value?
 
 
 
   Hey,
 
   I'm experimenting with some sqlcounter directives in radiusd.conf and
 chilli as the NAS.
   I've defined the following sqlcounter stanza for a daily traffic limit:
 
   sqlcounter defined in radiusd.conf:
   (the query was corrected as suggested by tnt on a previous thread on the
 list, correct me if I got it wrong please)
 
   sqlcounter counterChilliSpotMaxDailyOctets {
   counter-name = ChilliSpot-Max-Daily-Octets
   check-name = ChilliSpot-Max-Daily-Octets
   reply-name = ChilliSpot-Max-Total-Octets
   sqlmod-inst = sql
   key = User-Name
   reset = daily
   error-msg = Sorry, your maximum traffic usage (download
 and upload) has exceed the provided limit
   query = SELECT (SUM(AcctInputOctets +
 AcctOutputOctets))
 FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) +
 AcctSessionTime  '%b'
   }
 
 
 
   In the authorization phase, I'm seeing the following in debug log:
 rlm_sqlcounter: Entering module authorize code
 sqlcounter_expand:  'SELECT
 (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
 FROM radacct WHERE UserName='%{User-Name}''
 radius_xlat:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
 FROM
 radacct WHERE UserName='tester1''
 sqlcounter_expand:  '%{sql:SELECT
 (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
 UserName='tester1'}'
 radius_xlat: Running registered xlat function of module sql for string
 'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
 UserName='tester1''
 rlm_sql (sql): - sql_xlat
 radius_xlat:  'tester1'
 rlm_sql (sql): sql_set_user escaped user -- 'tester1'
 radius_xlat:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
 FROM
 radacct WHERE UserName='tester1''
 rlm_sql (sql): Reserving sql socket id: 2
 rlm_sql (sql): - sql_xlat finished
 rlm_sql (sql): Released sql socket id: 2
 radius_xlat:  '24004370'
 rlm_sqlcounter: (Check item - counter) is greater than zero
 rlm_sqlcounter: Authorized user tester1, check_item=26214400,
 counter=24004370
 rlm_sqlcounter: Sent Reply-Item for user tester1,
 Type=ChilliSpot-Max-Total-Octets, value=26239950
   modcall[authorize]: module counterChilliSpotMaxDailyOctets returns
 ok for request 0
   The entry in radcheck is as follows:
 
 | 346 | tester1 | ChilliSpot-Max-Daily-Octets | := | 26214400 |
   What happens is that it seems the counter doesn't work as expected. When
 a
 user logs in, performs some traffic usage, logs out and logs in again,
   the replied back attribute for chilli doesn't contain a value which is
 the
 remainder of the traffic usage, but something else.
 
   According to the radius debug above, if check_item=26214400 and
 counter=24004370, how come value=26239950?
   So I'm guessing I'm missing something but I'm too obsessed with the
 sqlcounter to notice it. (is the subtractation not a normal decimal
 action?)
 
   The FreeRADIUS version used is 1.1.7


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

Re: sqlcounter returning wrong value?

[tnt]

It (daily sqlcounter) does the same in 2.0.5:

rlm_sqlcounter: Authorized user jagoda, check_item=1000, counter=2635
rlm_sqlcounter: Sent Reply-Item for user jagoda, Type=Session-Timeout,
value=10027850

Returns value that is greater than the limit. I am using noreset
sqlcounter and that one works fine.

Ivan Kalik
Kalik Informatika ISP


Dana 24/10/2008, liran tal [EMAIL PROTECTED] piše:

Hey,

2008/10/24 [EMAIL PROTECTED]

 No, he wants a data not time counter.


That's right Evan. Moreover, there is no sense in changing the attribute to
be Session-Timeout when Chilli expects something else.
Any thoughts on this issue?


Thanks,



Dana 24/10/2008, mulianto [EMAIL PROTECTED] piše:


 hi..i think you should fix this one :
 
 reply-name = ChilliSpot-Max-Total-Octets to :
 reply-name = Session-Timeout
 
 try it..
 rgds,
 Mulianto
 
 http://www.indohotspot.net
 Your Hotspot solution
   -Original Message-
   From: [EMAIL PROTECTED]
 [mailto:freeradius-users-bounces+muliantofreeradius-users-bounces%2Bmulianto
 [EMAIL PROTECTED]
 Behalf Of liran tal
   Sent: Friday, October 24, 2008 1:07 AM
   To: FreeRadius users mailing list
   Subject: sqlcounter returning wrong value?
 
 
 
   Hey,
 
   I'm experimenting with some sqlcounter directives in radiusd.conf and
 chilli as the NAS.
   I've defined the following sqlcounter stanza for a daily traffic limit:
 
   sqlcounter defined in radiusd.conf:
   (the query was corrected as suggested by tnt on a previous thread on the
 list, correct me if I got it wrong please)
 
   sqlcounter counterChilliSpotMaxDailyOctets {
   counter-name = ChilliSpot-Max-Daily-Octets
   check-name = ChilliSpot-Max-Daily-Octets
   reply-name = ChilliSpot-Max-Total-Octets
   sqlmod-inst = sql
   key = User-Name
   reset = daily
   error-msg = Sorry, your maximum traffic usage (download
 and upload) has exceed the provided limit
   query = SELECT (SUM(AcctInputOctets +
 AcctOutputOctets))
 FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) +
 AcctSessionTime  '%b'
   }
 
 
 
   In the authorization phase, I'm seeing the following in debug log:
 rlm_sqlcounter: Entering module authorize code
 sqlcounter_expand:  'SELECT
 (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
 FROM radacct WHERE UserName='%{User-Name}''
 radius_xlat:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
 FROM
 radacct WHERE UserName='tester1''
 sqlcounter_expand:  '%{sql:SELECT
 (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
 UserName='tester1'}'
 radius_xlat: Running registered xlat function of module sql for string
 'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
 UserName='tester1''
 rlm_sql (sql): - sql_xlat
 radius_xlat:  'tester1'
 rlm_sql (sql): sql_set_user escaped user -- 'tester1'
 radius_xlat:  'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
 FROM
 radacct WHERE UserName='tester1''
 rlm_sql (sql): Reserving sql socket id: 2
 rlm_sql (sql): - sql_xlat finished
 rlm_sql (sql): Released sql socket id: 2
 radius_xlat:  '24004370'
 rlm_sqlcounter: (Check item - counter) is greater than zero
 rlm_sqlcounter: Authorized user tester1, check_item=26214400,
 counter=24004370
 rlm_sqlcounter: Sent Reply-Item for user tester1,
 Type=ChilliSpot-Max-Total-Octets, value=26239950
   modcall[authorize]: module counterChilliSpotMaxDailyOctets returns
 ok for request 0
   The entry in radcheck is as follows:
 
 | 346 | tester1 | ChilliSpot-Max-Daily-Octets | := | 26214400 |
   What happens is that it seems the counter doesn't work as expected. When
 a
 user logs in, performs some traffic usage, logs out and logs in again,
   the replied back attribute for chilli doesn't contain a value which is
 the
 remainder of the traffic usage, but something else.
 
   According to the radius debug above, if check_item=26214400 and
 counter=24004370, how come value=26239950?
   So I'm guessing I'm missing something but I'm too obsessed with the
 sqlcounter to notice it. (is the subtractation not a normal decimal
 action?)
 
   The FreeRADIUS version used is 1.1.7




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