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