sql counter is not working
Randeep
randeep123 at gmail.com
Fri Jun 26 07:47:02 CEST 2015
Hi Alan,
As we have different groups and for each group the reduced speed is
different. So in the radgroupcheck we specified an attribute
"Fub-Rate-Limit" for different groups with different values and in while
sending Mikrotik-Rate-Limit we are getting the value of this attribute as
given below.
update control {
Tmp-Integer-3 := "%{sql: SELECT radgroupcheck.value FROM
radusergroup INNER JOIN radgroupcheck ON radusergroup.groupname =
radgroupcheck.groupname WHERE radusergroup.username='%{User-Name}' AND
radgroupcheck.attribute='Fub-Rate-Limit'}"
}
testcounter {
reject = 1
}
if (reject) {
ok
update reply {
Reply-Message := "Reached bandwidth Kick off the
speed limit"
Mikrotik-Rate-Limit := "%{control:Tmp-Integer-3}"
}
update control {
Auth-Type := "Accept"
}
}
But we are getting the following error.
rad_recv: Access-Request packet from host 192.168.1.1 port 38570, id=62,
length=194
NAS-Port-Type = Ethernet
Calling-Station-Id = "38:63:BB:AA:23:C8"
Called-Station-Id = "server1"
NAS-Port-Id = "LAN"
User-Name = "randeep"
NAS-Port = 2152726533
Acct-Session-Id = "80500005"
Framed-IP-Address = 192.168.1.178
Mikrotik-Host-IP = 192.168.1.178
CHAP-Challenge = 0x3c6e66765729ace2c5e1e081ac4cb0cb
CHAP-Password = 0x4338f981b0dd90a4ebcc3cd87e9b9d86ad
Service-Type = Login-User
WISPr-Logoff-URL = "http://192.168.1.1/logout"
NAS-Identifier = "MikroTik"
NAS-IP-Address = 192.168.1.1
Fri Jun 26 11:01:04 2015 : Info: # Executing section authorize from file
/etc/raddb/sites-enabled/default
Fri Jun 26 11:01:04 2015 : Info: +- entering group authorize {...}
Fri Jun 26 11:01:04 2015 : Info: ++[preprocess] returns ok
Fri Jun 26 11:01:04 2015 : Info: [auth_log] expand:
/var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d ->
/var/log/radius/radacct/192.168.1.1/auth-detail-20150626
Fri Jun 26 11:01:04 2015 : Info: [auth_log] /var/log/radius/radacct/%{
Client-IP-Address}/auth-detail-%Y%m%d expands to /var/log/radius/radacct/
192.168.1.1/auth-detail-20150626
Fri Jun 26 11:01:04 2015 : Info: [auth_log] expand: %t -> Fri Jun 26
11:01:04 2015
Fri Jun 26 11:01:04 2015 : Info: ++[auth_log] returns ok
Fri Jun 26 11:01:04 2015 : Info: [chap] Setting 'Auth-Type := CHAP'
Fri Jun 26 11:01:04 2015 : Info: ++[chap] returns ok
Fri Jun 26 11:01:04 2015 : Info: ++[mschap] returns noop
Fri Jun 26 11:01:04 2015 : Info: [suffix] No '@' in User-Name = "randeep",
looking up realm NULL
Fri Jun 26 11:01:04 2015 : Info: [suffix] No such realm "NULL"
Fri Jun 26 11:01:04 2015 : Info: ++[suffix] returns noop
Fri Jun 26 11:01:04 2015 : Info: [eap] No EAP-Message, not doing EAP
Fri Jun 26 11:01:04 2015 : Info: ++[eap] returns noop
Fri Jun 26 11:01:04 2015 : Info: ++[files] returns noop
Fri Jun 26 11:01:04 2015 : Info: [sql] expand: %{User-Name} -> randeep
Fri Jun 26 11:01:04 2015 : Info: [sql] sql_set_user escaped user -->
'randeep'
Fri Jun 26 11:01:04 2015 : Debug: rlm_sql (sql): Reserving sql socket id: 3
Fri Jun 26 11:01:04 2015 : Info: [sql] expand: SELECT id, username,
attribute, value, op FROM radcheck WHERE username =
'%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute,
value, op FROM radcheck WHERE username =
'randeep' ORDER BY id
Fri Jun 26 11:01:04 2015 : Debug: WARNING: Found User-Password == "...".
Fri Jun 26 11:01:04 2015 : Debug: WARNING: Are you sure you don't mean
Cleartext-Password?
Fri Jun 26 11:01:04 2015 : Debug: WARNING: See "man rlm_pap" for more
information.
Fri Jun 26 11:01:04 2015 : Info: [sql] User found in radcheck table
Fri Jun 26 11:01:04 2015 : Info: [sql] expand: SELECT id, username,
attribute, value, op FROM radreply WHERE username =
'%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute,
value, op FROM radreply WHERE username =
'randeep' ORDER BY id
Fri Jun 26 11:01:04 2015 : Info: [sql] expand: SELECT groupname
FROM radusergroup WHERE username = '%{SQL-User-Name}'
ORDER BY priority -> SELECT groupname FROM radusergroup
WHERE username = 'randeep' ORDER BY priority
Fri Jun 26 11:01:04 2015 : Info: [sql] expand: SELECT id, groupname,
attribute, Value, op FROM radgroupcheck WHERE
groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname,
attribute, Value, op FROM radgroupcheck WHERE
groupname = 'admins' ORDER BY id
*Fri Jun 26 11:01:04 2015 : Error: rlm_sql: Failed to create the pair:
Unknown attribute "Fub-Rate-Limit" requires a hex string, not "216k/216k"*
Fri Jun 26 11:01:04 2015 : Error: rlm_sql (sql): Error getting data from
database
Fri Jun 26 11:01:04 2015 : Error: [sql] Error retrieving check pairs for
group admins
Fri Jun 26 11:01:04 2015 : Error: [sql] Error processing groups; rejecting
user
Fri Jun 26 11:01:04 2015 : Debug: rlm_sql (sql): Released sql socket id: 3
Fri Jun 26 11:01:04 2015 : Info: ++[sql] returns fail
Fri Jun 26 11:01:04 2015 : Auth: Invalid user: [randeep] (from client
mikrotik port 2152726533 cli 38:63:BB:AA:23:C8)
Fri Jun 26 11:01:04 2015 : Info: Using Post-Auth-Type Reject
Fri Jun 26 11:01:04 2015 : Info: # Executing group from file
/etc/raddb/sites-enabled/default
Fri Jun 26 11:01:04 2015 : Info: +- entering group REJECT {...}
Fri Jun 26 11:01:04 2015 : Info: [attr_filter.access_reject] expand:
%{User-Name} -> randeep
Fri Jun 26 11:01:04 2015 : Debug: attr_filter: Matched entry DEFAULT at
line 11
Fri Jun 26 11:01:04 2015 : Info: ++[attr_filter.access_reject] returns
updated
Fri Jun 26 11:01:04 2015 : Info: Delaying reject of request 0 for 1 seconds
Fri Jun 26 11:01:04 2015 : Debug: Going to the next request
Fri Jun 26 11:01:04 2015 : Debug: Waking up in 0.9 seconds.
rad_recv: Access-Request packet from host 192.168.1.1 port 38570, id=62,
length=194
Fri Jun 26 11:01:04 2015 : Info: Waiting to send Access-Reject to client
mikrotik port 38570 - ID: 62
Fri Jun 26 11:01:04 2015 : Debug: Waking up in 0.6 seconds.
rad_recv: Access-Request packet from host 192.168.1.1 port 38570, id=62,
length=194
Fri Jun 26 11:01:04 2015 : Info: Waiting to send Access-Reject to client
mikrotik port 38570 - ID: 62
Fri Jun 26 11:01:04 2015 : Debug: Waking up in 0.3 seconds.
Fri Jun 26 11:01:05 2015 : Info: Sending delayed reject for request 0
Sending Access-Reject of id 62 to 192.168.1.1 port 38570
Fri Jun 26 11:01:05 2015 : Debug: Waking up in 4.9 seconds.
Fri Jun 26 11:01:10 2015 : Info: Cleaning up request 0 ID 62 with timestamp
+8
Fri Jun 26 11:01:10 2015 : Info: Ready to process requests.
How we can assign different values of variable to Mikrotik-Rate-Limit for
different group users?
Regards,
Randeep
On Thu, Jun 25, 2015 at 11:55 AM, Randeep <randeep123 at gmail.com> wrote:
> Hi Alan,
>
> We could make it work. We had to add the following.
>
> testcounter {
> reject = 1
> }
> if (reject) {
> ok
> update reply {
> Reply-Message := "Reached bandwidth . Limiting the
> bandwidth"
> Mikrotik-Rate-Limit := "512k/512k"
> }
>
> update control {
> Auth-Type := "Accept"
> }
> }
>
>
> Thanks and Regards,
> Randeep
>
> On Thu, Jun 25, 2015 at 11:05 AM, Randeep <randeep123 at gmail.com> wrote:
>
>> Hi Alan,
>>
>> In Authorize section,
>>
>> I have added this.
>>
>> testcounter {
>> reject = 1
>> }
>> if (reject) {
>> update reply {
>> Reply-Message := "You have reached your transfer
>> limit. Limited bandwitch"
>> }
>> update control {
>> Auth-Type := "Accept"
>> Mikrotik-Rate-Limit = "512k/512k"
>> }
>> }
>>
>>
>> But it is still rejecting the request.
>>
>> Please see the logs below:
>> rad_recv: Access-Request packet from host 192.168.1.1 port 49909, id=16,
>> length=194
>> NAS-Port-Type = Ethernet
>> Calling-Station-Id = "38:63:BB:AA:23:C8"
>> Called-Station-Id = "server1"
>> NAS-Port-Id = "LAN"
>> User-Name = "randeep"
>> NAS-Port = 2151677957
>> Acct-Session-Id = "80400005"
>> Framed-IP-Address = 192.168.1.178
>> Mikrotik-Host-IP = 192.168.1.178
>> CHAP-Challenge = 0x4baef28ea406d49bb458d208906128d8
>> CHAP-Password = 0xcfee47d6e2bef349d10af8145eeb71d303
>> Service-Type = Login-User
>> WISPr-Logoff-URL = "http://192.168.1.1/logout"
>> NAS-Identifier = "MikroTik"
>> NAS-IP-Address = 192.168.1.1
>> Thu Jun 25 10:47:11 2015 : Info: # Executing section authorize from file
>> /etc/raddb/sites-enabled/default
>> Thu Jun 25 10:47:11 2015 : Info: +- entering group authorize {...}
>> Thu Jun 25 10:47:11 2015 : Info: ++[preprocess] returns ok
>> Thu Jun 25 10:47:11 2015 : Info: [auth_log] expand:
>> /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d ->
>> /var/log/radius/radacct/192.168.1.1/auth-detail-20150625
>> Thu Jun 25 10:47:11 2015 : Info: [auth_log] /var/log/radius/radacct/%{
>> Client-IP-Address}/auth-detail-%Y%m%d expands to /var/log/radius/radacct/
>> 192.168.1.1/auth-detail-20150625
>> Thu Jun 25 10:47:11 2015 : Info: [auth_log] expand: %t -> Thu Jun 25
>> 10:47:11 2015
>> Thu Jun 25 10:47:11 2015 : Info: ++[auth_log] returns ok
>> Thu Jun 25 10:47:11 2015 : Info: [chap] Setting 'Auth-Type := CHAP'
>> Thu Jun 25 10:47:11 2015 : Info: ++[chap] returns ok
>> Thu Jun 25 10:47:11 2015 : Info: ++[mschap] returns noop
>> Thu Jun 25 10:47:11 2015 : Info: [suffix] No '@' in User-Name =
>> "randeep", looking up realm NULL
>> Thu Jun 25 10:47:11 2015 : Info: [suffix] No such realm "NULL"
>> Thu Jun 25 10:47:11 2015 : Info: ++[suffix] returns noop
>> Thu Jun 25 10:47:11 2015 : Info: [eap] No EAP-Message, not doing EAP
>> Thu Jun 25 10:47:11 2015 : Info: ++[eap] returns noop
>> Thu Jun 25 10:47:11 2015 : Info: ++[files] returns noop
>> Thu Jun 25 10:47:11 2015 : Info: [sql] expand: %{User-Name} -> randeep
>> Thu Jun 25 10:47:11 2015 : Info: [sql] sql_set_user escaped user -->
>> 'randeep'
>> Thu Jun 25 10:47:11 2015 : Debug: rlm_sql (sql): Reserving sql socket id:
>> 3
>> Thu Jun 25 10:47:11 2015 : Info: [sql] expand: SELECT id, username,
>> attribute, value, op FROM radcheck WHERE username =
>> '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute,
>> value, op FROM radcheck WHERE username =
>> 'randeep' ORDER BY id
>> Thu Jun 25 10:47:11 2015 : Debug: WARNING: Found User-Password == "...".
>> Thu Jun 25 10:47:11 2015 : Debug: WARNING: Are you sure you don't mean
>> Cleartext-Password?
>> Thu Jun 25 10:47:11 2015 : Debug: WARNING: See "man rlm_pap" for more
>> information.
>> Thu Jun 25 10:47:11 2015 : Info: [sql] User found in radcheck table
>> Thu Jun 25 10:47:11 2015 : Info: [sql] expand: SELECT id, username,
>> attribute, value, op FROM radreply WHERE username =
>> '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute,
>> value, op FROM radreply WHERE username =
>> 'randeep' ORDER BY id
>> Thu Jun 25 10:47:11 2015 : Info: [sql] expand: SELECT
>> groupname FROM radusergroup WHERE username =
>> '%{SQL-User-Name}' ORDER BY priority -> SELECT
>> groupname FROM radusergroup WHERE username =
>> 'randeep' ORDER BY priority
>> Thu Jun 25 10:47:11 2015 : Info: [sql] expand: SELECT id, groupname,
>> attribute, Value, op FROM radgroupcheck WHERE
>> groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname,
>> attribute, Value, op FROM radgroupcheck WHERE
>> groupname = 'admins' ORDER BY id
>> Thu Jun 25 10:47:11 2015 : Info: [sql] User found in group admins
>> Thu Jun 25 10:47:11 2015 : Info: [sql] expand: SELECT id, groupname,
>> attribute, value, op FROM radgroupreply WHERE
>> groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname,
>> attribute, value, op FROM radgroupreply WHERE
>> groupname = 'admins' ORDER BY id
>> Thu Jun 25 10:47:11 2015 : Debug: rlm_sql (sql): Released sql socket id: 3
>> Thu Jun 25 10:47:11 2015 : Info: ++[sql] returns ok
>> Thu Jun 25 10:47:11 2015 : Debug: rlm_sqlcounter: Entering module
>> authorize code
>> Thu Jun 25 10:47:11 2015 : Debug: sqlcounter_expand: 'SELECT
>> SUM(acctinputoctets) + SUM(acctoutputoctets) FROM radacct WHERE
>> username='%{User-Name}''
>> Thu Jun 25 10:47:11 2015 : Info: [testcounter] expand: SELECT
>> SUM(acctinputoctets) + SUM(acctoutputoctets) FROM radacct WHERE
>> username='%{User-Name}' -> SELECT SUM(acctinputoctets) +
>> SUM(acctoutputoctets) FROM radacct WHERE username='randeep'
>> Thu Jun 25 10:47:11 2015 : Debug: WARNING: Please replace '%S' with
>> '${sqlmod-inst}'
>> Thu Jun 25 10:47:11 2015 : Debug: sqlcounter_expand: '%{sql:SELECT
>> SUM(acctinputoctets) + SUM(acctoutputoctets) FROM radacct WHERE
>> username='randeep'}'
>> Thu Jun 25 10:47:11 2015 : Info: [testcounter] sql_xlat
>> Thu Jun 25 10:47:11 2015 : Info: [testcounter] expand: %{User-Name} ->
>> randeep
>> Thu Jun 25 10:47:11 2015 : Info: [testcounter] sql_set_user escaped user
>> --> 'randeep'
>> Thu Jun 25 10:47:11 2015 : Info: [testcounter] expand: SELECT
>> SUM(acctinputoctets) + SUM(acctoutputoctets) FROM radacct WHERE
>> username='randeep' -> SELECT SUM(acctinputoctets) + SUM(acctoutputoctets)
>> FROM radacct WHERE username='randeep'
>> Thu Jun 25 10:47:11 2015 : Debug: rlm_sql (sql): Reserving sql socket id:
>> 2
>> Thu Jun 25 10:47:11 2015 : Info: [testcounter] sql_xlat finished
>> Thu Jun 25 10:47:11 2015 : Debug: rlm_sql (sql): Released sql socket id: 2
>> Thu Jun 25 10:47:11 2015 : Info: [testcounter] expand: %{sql:SELECT
>> SUM(acctinputoctets) + SUM(acctoutputoctets) FROM radacct WHERE
>> username='randeep'} -> 42164020
>> Thu Jun 25 10:47:11 2015 : Debug: rlm_sqlcounter: (Check item - counter)
>> is less than zero
>> Thu Jun 25 10:47:11 2015 : Debug: rlm_sqlcounter: Rejected user randeep,
>> check_item=10240000, counter=42164020
>> Thu Jun 25 10:47:11 2015 : Info: ++[testcounter] returns reject
>> Thu Jun 25 10:47:11 2015 : Info: ++? if (reject)
>> Thu Jun 25 10:47:11 2015 : Info: ? Evaluating (reject) -> TRUE
>> Thu Jun 25 10:47:11 2015 : Info: ++? if (reject) -> TRUE
>> Thu Jun 25 10:47:11 2015 : Info: ++- entering if (reject) {...}
>> Thu Jun 25 10:47:11 2015 : Info: +++[reply] returns reject
>> Thu Jun 25 10:47:11 2015 : Info: +++[control] returns reject
>> Thu Jun 25 10:47:11 2015 : Info: ++- if (reject) returns reject
>> Thu Jun 25 10:47:11 2015 : Auth: Invalid user (rlm_sqlcounter: Maximum
>> monthly usage time reached): [randeep] (from client mikrotik port
>> 2151677957 cli 38:63:BB:AA:23:C8)
>> Thu Jun 25 10:47:11 2015 : Info: Using Post-Auth-Type Reject
>> Thu Jun 25 10:47:11 2015 : Info: # Executing group from file
>> /etc/raddb/sites-enabled/default
>> Thu Jun 25 10:47:11 2015 : Info: +- entering group REJECT {...}
>> Thu Jun 25 10:47:11 2015 : Info: [attr_filter.access_reject] expand:
>> %{User-Name} -> randeep
>> Thu Jun 25 10:47:11 2015 : Debug: attr_filter: Matched entry DEFAULT at
>> line 11
>> Thu Jun 25 10:47:11 2015 : Info: ++[attr_filter.access_reject] returns
>> updated
>> Thu Jun 25 10:47:11 2015 : Info: Delaying reject of request 0 for 1
>> seconds
>> Thu Jun 25 10:47:11 2015 : Debug: Going to the next request
>> Thu Jun 25 10:47:11 2015 : Debug: Waking up in 0.9 seconds.
>> rad_recv: Access-Request packet from host 192.168.1.1 port 49909, id=16,
>> length=194
>> Thu Jun 25 10:47:11 2015 : Info: Waiting to send Access-Reject to client
>> mikrotik port 49909 - ID: 16
>> Thu Jun 25 10:47:11 2015 : Debug: Waking up in 0.6 seconds.
>> rad_recv: Access-Request packet from host 192.168.1.1 port 49909, id=16,
>> length=194
>> Thu Jun 25 10:47:11 2015 : Info: Waiting to send Access-Reject to client
>> mikrotik port 49909 - ID: 16
>> Thu Jun 25 10:47:11 2015 : Debug: Waking up in 0.3 seconds.
>> Thu Jun 25 10:47:12 2015 : Info: Sending delayed reject for request 0
>> Sending Access-Reject of id 16 to 192.168.1.1 port 49909
>> Reply-Message = "You have reached your transfer limit. Limited
>> bandwitch"
>> Thu Jun 25 10:47:12 2015 : Debug: Waking up in 4.9 seconds.
>>
>> Am I doing something wrong?
>>
>> Regards,
>> Randeep
>>
>> On Wed, Jun 24, 2015 at 6:25 PM, Alan DeKok <aland at deployingradius.com>
>> wrote:
>>
>>> On Jun 24, 2015, at 8:21 AM, Randeep <randeep123 at gmail.com> wrote:
>>> > We are having a unlimit plan with after 30GB there will be FUP applied.
>>> > Initlially the speed will be 4 mbps and after 30G the speed will be
>>> 2mbps.
>>>
>>> That's possible.
>>>
>>> > But the counter is not working:
>>>
>>> No, it's working as documented. If you want it to do something else,
>>> configure it to do something else.
>>>
>>> > It is just not letting the user log in. If the limit is reached it is
>>> > sending access-reject instead of letting the user login with less
>>> internet
>>> > speed. Please see then logs below.
>>>
>>> You should edit the "authorize" section, and do:
>>>
>>> authorize {
>>> ...
>>>
>>> sqlcounter {
>>> reject = 1
>>> }
>>>
>>> if (reject) {
>>> # set 2mbps
>>>
>>> }
>>> else {
>>> # set 4mbps
>>> }
>>>
>>> Alan DeKok.
>>>
>>>
>>> -
>>> List info/subscribe/unsubscribe? See
>>> http://www.freeradius.org/list/users.html
>>
>>
>>
>>
>> --
>> Randeep
>> Mob: +919447831699[kerala]
>> Mob: +919880050349[B'lore]
>> http://twitter.com/Randeeppr
>> http://in.linkedin.com/in/randeeppr
>>
>> [image: --]
>> Randeep Raman
>> [image: http://]about.me/Randeeppr
>> <http://about.me/Randeeppr>
>>
>>
>
>
>
> --
> Randeep
> Mob: +919447831699[kerala]
> Mob: +919880050349[B'lore]
> http://twitter.com/Randeeppr
> http://in.linkedin.com/in/randeeppr
>
> [image: --]
> Randeep Raman
> [image: http://]about.me/Randeeppr
> <http://about.me/Randeeppr>
>
>
--
Randeep
Mob: +919447831699[kerala]
Mob: +919880050349[B'lore]
http://twitter.com/Randeeppr
http://in.linkedin.com/in/randeeppr
[image: --]
Randeep Raman
[image: http://]about.me/Randeeppr
<http://about.me/Randeeppr>
More information about the Freeradius-Users
mailing list