sql counter is not working
Randeep
randeep123 at gmail.com
Wed Jun 24 14:21:00 CEST 2015
Hi all,
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.
I have done the following.
Created a counter in counter.conf
sqlcounter testcounter {
counter-name = Max-Data-Used
check-name = Max-Data
reply-name = Mikrotik-Rate-Limit
sqlmod-inst = sql
key = User-Name
# reset = daily
reset = monthly
reply-message = "You have reached your 10 MB bandwidth
limit" sqlmod-inst = sql
query = "SELECT SUM(acctinputoctets) +
SUM(acctoutputoctets) FROM radacct WHERE username='%{${key}}'"
}
in post-auth I have given the following:
#Added for the FUP implementation:
update control {
#sum of the AcctInputOctets+AcctOutputOctets for this month. (From
the first day of the current month to till date)
Tmp-Integer-0 := "%{sql:SELECT
(SUM(acctinputoctets)+SUM(acctoutputoctets))
AS Total FROM radacct where (acctstarttime between DATE_FORMAT(NOW()
,'%Y-%m-01')
AND NOW() AND acctstoptime between DATE_FORMAT(NOW() ,'%Y-%m-01') AND
NOW()) AND radacct.username='%{User-Name}'}"
#Value of Max-Data from the radgroupcheck for the group of the user
Tmp-Integer-1 := "%{sql: SELECT radgroupcheck.value FROM
radusergroup INNER JOIN radgroupcheck ON radusergroup.groupname =
radgroupcheck.groupname WHERE radus
ergroup.username='%{User-Name}' AND radgroupcheck.attribute='Max-Data'}"
}
if ("%{control:Tmp-Integer-0}" > "%{control:Tmp-Integer-1}") {
update reply {
Reply-Message := "Fair Usage Policy Enforced, Bandwidth Limited"
Mikrotik-Rate-Limit := "512K/512K"
}
But the counter is not working:
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.
rad_recv: Access-Request packet from host 192.168.1.1 port 58768, id=153,
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 = 2150629401
Acct-Session-Id = "80300019"
Framed-IP-Address = 192.168.1.178
Mikrotik-Host-IP = 192.168.1.178
CHAP-Challenge = 0x618cb729fc30bb997aee7574fcd41676
CHAP-Password = 0x343254f5155e8862a8486eee2a0c7f45c8
Service-Type = Login-User
WISPr-Logoff-URL = "http://192.168.1.1/logout"
NAS-Identifier = "MikroTik"
NAS-IP-Address = 192.168.1.1
Wed Jun 24 17:34:33 2015 : Info: # Executing section authorize from file
/etc/raddb/sites-enabled/default
Wed Jun 24 17:34:33 2015 : Info: +- entering group authorize {...}
Wed Jun 24 17:34:33 2015 : Info: ++[preprocess] returns ok
Wed Jun 24 17:34:33 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-20150624
Wed Jun 24 17:34:33 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-20150624
Wed Jun 24 17:34:33 2015 : Info: [auth_log] expand: %t -> Wed Jun 24
17:34:33 2015
Wed Jun 24 17:34:33 2015 : Info: ++[auth_log] returns ok
Wed Jun 24 17:34:33 2015 : Info: [chap] Setting 'Auth-Type := CHAP'
Wed Jun 24 17:34:33 2015 : Info: ++[chap] returns ok
Wed Jun 24 17:34:33 2015 : Info: ++[mschap] returns noop
Wed Jun 24 17:34:33 2015 : Info: [suffix] No '@' in User-Name = "randeep",
looking up realm NULL
Wed Jun 24 17:34:33 2015 : Info: [suffix] No such realm "NULL"
Wed Jun 24 17:34:33 2015 : Info: ++[suffix] returns noop
Wed Jun 24 17:34:33 2015 : Info: [eap] No EAP-Message, not doing EAP
Wed Jun 24 17:34:33 2015 : Info: ++[eap] returns noop
Wed Jun 24 17:34:33 2015 : Info: ++[files] returns noop
Wed Jun 24 17:34:33 2015 : Info: [sql] expand: %{User-Name} -> randeep
Wed Jun 24 17:34:33 2015 : Info: [sql] sql_set_user escaped user -->
'randeep'
Wed Jun 24 17:34:33 2015 : Debug: rlm_sql (sql): Reserving sql socket id: 3
Wed Jun 24 17:34:33 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
Wed Jun 24 17:34:33 2015 : Debug: WARNING: Found User-Password == "...".
Wed Jun 24 17:34:33 2015 : Debug: WARNING: Are you sure you don't mean
Cleartext-Password?
Wed Jun 24 17:34:33 2015 : Debug: WARNING: See "man rlm_pap" for more
information.
Wed Jun 24 17:34:33 2015 : Info: [sql] User found in radcheck table
Wed Jun 24 17:34:33 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
Wed Jun 24 17:34:33 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
Wed Jun 24 17:34:33 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
Wed Jun 24 17:34:33 2015 : Info: [sql] User found in group admins
Wed Jun 24 17:34:33 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
Wed Jun 24 17:34:33 2015 : Debug: rlm_sql (sql): Released sql socket id: 3
Wed Jun 24 17:34:33 2015 : Info: ++[sql] returns ok
Wed Jun 24 17:34:33 2015 : Debug: rlm_sqlcounter: Entering module authorize
code
Wed Jun 24 17:34:33 2015 : Debug: sqlcounter_expand: 'SELECT
SUM(acctinputoctets) + SUM(acctoutputoctets) FROM radacct WHERE
username='%{User-Name}''
Wed Jun 24 17:34:33 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'
Wed Jun 24 17:34:33 2015 : Debug: WARNING: Please replace '%S' with
'${sqlmod-inst}'
Wed Jun 24 17:34:33 2015 : Debug: sqlcounter_expand: '%{sql:SELECT
SUM(acctinputoctets) + SUM(acctoutputoctets) FROM radacct WHERE
username='randeep'}'
Wed Jun 24 17:34:33 2015 : Info: [testcounter] sql_xlat
Wed Jun 24 17:34:33 2015 : Info: [testcounter] expand: %{User-Name} ->
randeep
Wed Jun 24 17:34:33 2015 : Info: [testcounter] sql_set_user escaped user
--> 'randeep'
Wed Jun 24 17:34:33 2015 : Info: [testcounter] expand: SELECT
SUM(acctinputoctets) + SUM(acctoutputoctets) FROM radacct WHERE
username='randeep' -> SELECT SUM(acctinputoctets) + SUM(acctoutputoctets)
FROM radacct WHERE username='randeep'
Wed Jun 24 17:34:33 2015 : Debug: rlm_sql (sql): Reserving sql socket id: 2
Wed Jun 24 17:34:33 2015 : Info: [testcounter] sql_xlat finished
Wed Jun 24 17:34:33 2015 : Debug: rlm_sql (sql): Released sql socket id: 2
Wed Jun 24 17:34:33 2015 : Info: [testcounter] expand: %{sql:SELECT
SUM(acctinputoctets) + SUM(acctoutputoctets) FROM radacct WHERE
username='randeep'} -> 42164020
Wed Jun 24 17:34:33 2015 : Debug: rlm_sqlcounter: (Check item - counter) is
less than zero
Wed Jun 24 17:34:33 2015 : Debug: rlm_sqlcounter: Rejected user randeep,
check_item=10240000, counter=42164020
Wed Jun 24 17:34:33 2015 : Info: ++[testcounter] returns reject
Wed Jun 24 17:34:33 2015 : Auth: Invalid user (rlm_sqlcounter: Maximum
monthly usage time reached): [randeep] (from client mikrotik port
2150629401 cli 38:63:BB:AA:23:C8)
Wed Jun 24 17:34:33 2015 : Info: Using Post-Auth-Type Reject
Wed Jun 24 17:34:33 2015 : Info: # Executing group from file
/etc/raddb/sites-enabled/default
Wed Jun 24 17:34:33 2015 : Info: +- entering group REJECT {...}
Wed Jun 24 17:34:33 2015 : Info: [attr_filter.access_reject] expand:
%{User-Name} -> randeep
Wed Jun 24 17:34:33 2015 : Debug: attr_filter: Matched entry DEFAULT at
line 11
Wed Jun 24 17:34:33 2015 : Info: ++[attr_filter.access_reject] returns
updated
Wed Jun 24 17:34:33 2015 : Info: Delaying reject of request 0 for 1 seconds
Wed Jun 24 17:34:33 2015 : Debug: Going to the next request
Wed Jun 24 17:34:33 2015 : Debug: Waking up in 0.9 seconds.
rad_recv: Access-Request packet from host 192.168.1.1 port 58768, id=153,
length=194
Wed Jun 24 17:34:34 2015 : Info: Waiting to send Access-Reject to client
mikrotik port 58768 - ID: 153
Wed Jun 24 17:34:34 2015 : Debug: Waking up in 0.6 seconds.
rad_recv: Access-Request packet from host 192.168.1.1 port 58768, id=153,
length=194
Wed Jun 24 17:34:34 2015 : Info: Waiting to send Access-Reject to client
mikrotik port 58768 - ID: 153
Wed Jun 24 17:34:34 2015 : Debug: Waking up in 0.3 seconds.
Wed Jun 24 17:34:34 2015 : Info: Sending delayed reject for request 0
Sending Access-Reject of id 153 to 192.168.1.1 port 58768
Reply-Message = "Your maximum monthly usage time has been reached"
Wed Jun 24 17:34:34 2015 : Debug: Waking up in 4.9 seconds.
Please help.
--
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