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