Implementing COA (FreeRadius + Mysql)

Randeep randeep123 at gmail.com
Thu Jun 25 11:44:07 CEST 2015


Hi,

We have tried to implement it.

We have added our NAS to the sites-enabled/originate-coa as given below.

home_server Mikrotik-coa {
        type = coa

        #
        #  Note that a home server of type "coa" MUST be a real NAS,
        #  with an ipaddr or ipv6addr.  It CANNOT point to a virtual
        #  server.
        #
        ipaddr = 192.168.1.1
        port = 3799

        #  This secret SHOULD NOT be the same as the shared
        #  secret in a "client" section.
        secret = mikrotik

        #  CoA specific parameters.  See raddb/proxy.conf for details.
        coa {
                irt = 2
                mrt = 16
                mrc = 5
                mrd = 30
        }
}

And added these lines in sites-enabled/default inside accounting section,

 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 coa {
                User-Name = "%{User-Name}"
                Acct-Session-Id = "%{Acct-Session-Id}"
                NAS-IP-Address = "%{NAS-IP-Address}"
                Framed-IP-Address = "%{Framed-IP-Address}"
                Mikrotik-Rate-Limit = "256K/256K"
            }
        }

But it is not sending the COA to the NAS.

Please see the following log.

rad_recv: Accounting-Request packet from host 192.168.1.1 port 42473,
id=181, length=176
        Acct-Status-Type = Interim-Update
        NAS-Port-Type = Ethernet
        Calling-Station-Id = "38:63:BB:AA:23:C8"
        Called-Station-Id = "server1"
        NAS-Port-Id = "LAN"
        User-Name = "lukup"
        NAS-Port = 2151677969
        Acct-Session-Id = "80400011"
        Framed-IP-Address = 192.168.1.178
        Mikrotik-Host-IP = 192.168.1.178
        Event-Timestamp = "Jan  2 1970 11:21:29 IST"
        Acct-Input-Octets = 4811892
        Acct-Output-Octets = 21578081
        Acct-Input-Gigawords = 0
        Acct-Output-Gigawords = 0
        Acct-Input-Packets = 21360
        Acct-Output-Packets = 20079
        Acct-Session-Time = 2159
        NAS-Identifier = "MikroTik"
        Acct-Delay-Time = 0
        NAS-IP-Address = 192.168.1.1
Thu Jun 25 14:55:53 2015 : Info: # Executing section preacct from file
/etc/raddb/sites-enabled/default
Thu Jun 25 14:55:53 2015 : Info: +- entering group preacct {...}
Thu Jun 25 14:55:53 2015 : Info: ++[preprocess] returns ok
Thu Jun 25 14:55:53 2015 : Info:        expand: %{Acct-Session-Time} -> 2159
Thu Jun 25 14:55:53 2015 : Info:        expand: %{Acct-Delay-Time} -> 0
Thu Jun 25 14:55:53 2015 : Info:        expand:  %l -
%{%{Acct-Session-Time}:-0} - %{%{Acct-Delay-Time}:-0} ->  1435224353 - 2159
- 0
Thu Jun 25 14:55:53 2015 : Info:        expand: %{expr: %l -
%{%{Acct-Session-Time}:-0} - %{%{Acct-Delay-Time}:-0}} -> 1435222194
Thu Jun 25 14:55:53 2015 : Info: ++[request] returns ok
Thu Jun 25 14:55:53 2015 : Info: [acct_unique] Hashing 'NAS-Port =
2151677969,Client-IP-Address = 192.168.1.1,NAS-IP-Address =
192.168.1.1,Acct-Session-Id = "80400011",User-Name = "lukup"'
Thu Jun 25 14:55:53 2015 : Info: [acct_unique] Acct-Unique-Session-ID =
"c796086e39f71850".
Thu Jun 25 14:55:53 2015 : Info: ++[acct_unique] returns ok
Thu Jun 25 14:55:53 2015 : Info: [suffix] No '@' in User-Name = "lukup",
looking up realm NULL
Thu Jun 25 14:55:53 2015 : Info: [suffix] No such realm "NULL"
Thu Jun 25 14:55:53 2015 : Info: ++[suffix] returns noop
Thu Jun 25 14:55:53 2015 : Info: ++[files] returns noop
Thu Jun 25 14:55:53 2015 : Info: # Executing section accounting from file
/etc/raddb/sites-enabled/default
Thu Jun 25 14:55:53 2015 : Info: +- entering group accounting {...}
Thu Jun 25 14:55:53 2015 : Info: [detail]       expand:
%{Packet-Src-IP-Address} -> 192.168.1.1
Thu Jun 25 14:55:53 2015 : Info: [detail]       expand:
/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{
Packet-Src-IPv6-Address}}/detail-%Y%m%d -> /var/log/radius/radacct/
192.168.1.1/detail-20150625
Thu Jun 25 14:55:53 2015 : Info: [detail] /var/log/radius/radacct/%{%{
Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands
to /var/log/radius/radacct/192.168.1.1/detail-20150625
Thu Jun 25 14:55:53 2015 : Info: [detail]       expand: %t -> Thu Jun 25
14:55:53 2015
Thu Jun 25 14:55:53 2015 : Info: ++[detail] returns ok
Thu Jun 25 14:55:53 2015 : Info: [radutmp]      expand:
/var/log/radius/radutmp -> /var/log/radius/radutmp
Thu Jun 25 14:55:53 2015 : Info: [radutmp]      expand: %{User-Name} ->
lukup
Thu Jun 25 14:55:53 2015 : Info: ++[radutmp] returns ok
Thu Jun 25 14:55:53 2015 : Info: [sradutmp]     expand:
/var/log/radius/sradutmp -> /var/log/radius/sradutmp
Thu Jun 25 14:55:53 2015 : Info: [sradutmp]     expand: %{User-Name} ->
lukup
Thu Jun 25 14:55:53 2015 : Info: ++[sradutmp] returns ok
Thu Jun 25 14:55:53 2015 : Info: [sql]  expand: %{User-Name} -> lukup
Thu Jun 25 14:55:53 2015 : Info: [sql] sql_set_user escaped user --> 'lukup'
Thu Jun 25 14:55:53 2015 : Info: [sql]  expand: %{Acct-Input-Gigawords} -> 0
Thu Jun 25 14:55:53 2015 : Info: [sql]  expand: %{Acct-Input-Octets} ->
4811892
Thu Jun 25 14:55:53 2015 : Info: [sql]  expand: %{Acct-Output-Gigawords} ->
0
Thu Jun 25 14:55:53 2015 : Info: [sql]  expand: %{Acct-Output-Octets} ->
21578081
Thu Jun 25 14:55:53 2015 : Info: [sql]  expand:            UPDATE
radacct           SET              framedipaddress =
'%{Framed-IP-Address}',              acctsessiontime     =
'%{Acct-Session-Time}',              acctinputoctets     =
'%{%{Acct-Input-Gigawords}:-0}'  << 32 |
'%{%{Acct-Input-Octets}:-0}',              acctoutputoctets    =
'%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}'           WHERE acctsessionid =
'%{Acct-Session-Id}'           AND username        =
'%{SQL-User-Name}'           AND nasipaddress    = '%{NAS-IP-Address}'
->            UPDATE radacct           SET              framedipaddress =
'192.168.1.178',              acctsessiontime     = '2159',
acctinputoctets     = '0'  << 32 |
'4811892',              acctoutputoctets    = '0' << 32
|                                    '21578081'           WHERE
acctsessionid = '80400011'           AND username        =
'lukup'           AN
Thu Jun 25 14:55:53 2015 : Debug: rlm_sql (sql): Reserving sql socket id: 2
Thu Jun 25 14:55:53 2015 : Debug: rlm_sql (sql): Released sql socket id: 2
Thu Jun 25 14:55:53 2015 : Info: ++[sql] returns ok
Thu Jun 25 14:55:53 2015 : Info: sql_xlat
Thu Jun 25 14:55:53 2015 : Info:        expand: %{User-Name} -> lukup
Thu Jun 25 14:55:53 2015 : Info: sql_set_user escaped user --> 'lukup'
Thu Jun 25 14:55:53 2015 : Info:        expand: 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}' -> SELECT
(SUM(acctinputoctets)+SUM(acctoutputoctets))
AS Total FROM radacct where (acctstarttime between  DATE_FORMAT(NOW()
,'2015-06-01') AND NOW() AND  acctstoptime between  DATE_FORMAT(NOW()
,'2015-06-01') AND NOW()) AND radacct.username='lukup'
Thu Jun 25 14:55:53 2015 : Debug: rlm_sql (sql): Reserving sql socket id: 1
Thu Jun 25 14:55:53 2015 : Info: sql_xlat finished
Thu Jun 25 14:55:53 2015 : Debug: rlm_sql (sql): Released sql socket id: 1
Thu Jun 25 14:55:53 2015 : Info:        expand: %{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}'} -> 34051873
Thu Jun 25 14:55:53 2015 : Info: sql_xlat
Thu Jun 25 14:55:53 2015 : Info:        expand: %{User-Name} -> lukup
Thu Jun 25 14:55:53 2015 : Info: sql_set_user escaped user --> 'lukup'
Thu Jun 25 14:55:53 2015 : Info:        expand:  SELECT radgroupcheck.value
FROM radusergroup INNER JOIN radgroupcheck ON radusergroup.groupname =
radgroupcheck.groupname WHERE  radusergroup.username='%{User-Name}' AND
radgroupcheck.attribute='Max-Data' ->  SELECT radgroupcheck.value FROM
radusergroup INNER JOIN radgroupcheck ON radusergroup.groupname =
radgroupcheck.groupname WHERE  radusergroup.username='lukup' AND
radgroupcheck.attribute='Max-Data'
Thu Jun 25 14:55:53 2015 : Debug: rlm_sql (sql): Reserving sql socket id: 0
Thu Jun 25 14:55:53 2015 : Info: sql_xlat finished
Thu Jun 25 14:55:53 2015 : Debug: rlm_sql (sql): Released sql socket id: 0
Thu Jun 25 14:55:53 2015 : Info:        expand: %{sql: SELECT
radgroupcheck.value FROM radusergroup INNER JOIN radgroupcheck ON
radusergroup.groupname = radgroupcheck.groupname WHERE
radusergroup.username='%{User-Name}' AND  radgroupcheck.attribute='Max-Data'}
-> 10240000
Thu Jun 25 14:55:53 2015 : Info: ++[control] returns ok
Thu Jun 25 14:55:53 2015 : Info: ++? if ("%{control:Tmp-Integer-0}" >
"%{control:Tmp-Integer-1}")
Thu Jun 25 14:55:53 2015 : Info:        expand: %{control:Tmp-Integer-0} ->
34051873
Thu Jun 25 14:55:53 2015 : Info:        expand: %{control:Tmp-Integer-1} ->
10240000
Thu Jun 25 14:55:53 2015 : Info: ? Evaluating ("%{control:Tmp-Integer-0}" >
"%{control:Tmp-Integer-1}") -> TRUE
Thu Jun 25 14:55:53 2015 : Info: ++? if ("%{control:Tmp-Integer-0}" >
"%{control:Tmp-Integer-1}") -> TRUE
Thu Jun 25 14:55:53 2015 : Info: ++- entering if
("%{control:Tmp-Integer-0}" > "%{control:Tmp-Integer-1}") {...}
Thu Jun 25 14:55:53 2015 : Info:        expand: %{User-Name} -> lukup
Thu Jun 25 14:55:53 2015 : Info:        expand: %{Acct-Session-Id} ->
80400011
Thu Jun 25 14:55:53 2015 : Info:        expand: %{NAS-IP-Address} ->
192.168.1.1
Thu Jun 25 14:55:53 2015 : Info:        expand: %{Framed-IP-Address} ->
192.168.1.178
Thu Jun 25 14:55:53 2015 : Info: +++[coa] returns ok
Thu Jun 25 14:55:53 2015 : Info: ++- if ("%{control:Tmp-Integer-0}" >
"%{control:Tmp-Integer-1}") returns ok
Thu Jun 25 14:55:53 2015 : Info: ++[exec] returns noop
Thu Jun 25 14:55:53 2015 : Info: [attr_filter.accounting_response]
expand: %{User-Name} -> lukup
Thu Jun 25 14:55:53 2015 : Debug: attr_filter: Matched entry DEFAULT at
line 12
Thu Jun 25 14:55:53 2015 : Info: ++[attr_filter.accounting_response]
returns updated
Sending Accounting-Response of id 181 to 192.168.1.1 port 42473
Thu Jun 25 14:55:53 2015 : Info:   WARNING: Empty pre-proxy section.  Using
default return values.
Thu Jun 25 14:55:53 2015 : Info:  ... adding new socket proxy address *
port 57709
Thu Jun 25 14:55:53 2015 : Info: ERROR: Failed to create a new socket for
proxying requests.
Thu Jun 25 14:55:53 2015 : Debug: ERROR: Failed to insert CoA request into
proxy list.
Thu Jun 25 14:55:53 2015 : Info: Do CoA Fail handler here
Thu Jun 25 14:55:53 2015 : Info: Finished request 2.
Thu Jun 25 14:55:53 2015 : Info: Cleaning up request 2 ID 181 with
timestamp +157
Thu Jun 25 14:55:53 2015 : Debug: Going to the next request
Thu Jun 25 14:55:53 2015 : Info: Ready to process requests.

Please advice.

Regards,
Randeep

On Thu, Jun 25, 2015 at 2:22 PM, Randeep <randeep123 at gmail.com> wrote:

> Hi all,
>
> Can anyone provide some useful links on implementing COA (Dynamic
> Bandwidth reduction) with FreeRadius and Mikrotik(Or procedure with some
> other NAS)
>
> TIA,
>
> --
> 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