Hi,
We are implementing Mikrotik+FreeRadius+Mysql
We are planning to implement a network pack that let the user use 30G @4mbps and after 30G the speed will be reduced to 1mbps. We are trying to do it dynamically. We are using PPPOE as well as Hotspot.
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 #Do we need to open this port in Mikrotik?
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