FR with Mikrotik - FUP Quota Bandwidth not changing auto

[Anirudh Malhotra]

If ("%{sql: SELECT SUM(AcctInputOctets)+SUM(AcctOutputOctets) FROM radacct
WHERE UserName='%{User-Name}'">"data limit") {
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"
}
}
You can put other conditions in sql query

BR,
Anirudh Malhotra
8zero2
Mail: 8zero2.in at gmail.com
Facebook: www.facebook.com/8zero2
Twitter: @8zero2_in
Blog: blog.8zero2.in

On 25 Mar 2016, 09:33 +0530, AACABLE / Mikrotik System<aacable79 at gmail.com>, wrote:
> Ok i have managed to set the COA and its working fine.
> Now I am stucked at query section. Example I want to match if daily qouta
> have reached, it should send the COA. One example is below I found in
> google.
> 
> accounting {
> 
> #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 this sends 256k every time to NAS , ignoring other. I understand there
> should be some IF statement to match the used counter and if it found
> greater then assigned quota, it should send COA to lower down the package
> as per defined in the dailyquota authorize section (that is 512k). can
> some1 please give me any example ?
> 
> On Thu, Mar 24, 2016 at 8:38 PM, Anirudh Malhotra<8zero2ops at gmail.com
> wrote:
> 
> > Hi,
> > 
> > So you have to define the query(unlang) in accounting(for checking whether
> > the limit is reached and sending the coa hence) on accounting packets.
> > 
> > CoA can be sent using update coa {...} check the docs of freeradius and
> > search the web more on it. You need session identifier and attributes to
> > modified in this section basically, secondly you have to add NAS as coa
> > server.
> > 
> > BR,
> > Anirudh Malhotra
> > 8zero2
> > Mail: 8zero2.in at gmail.com
> > Facebook: www.facebook.com/8zero2
> > Twitter: @8zero2_in
> > Blog: blog.8zero2.in
> > 
> > On 24 Mar 2016, 20:13 +0530, AACABLE / Mikrotik System<aacable79 at gmail.com>,
> > wrote:
> > > I have different counters like daily / weekly monthly so If I remove the
> > > counter, where I will be putting the query?
> > > How-to send the COA query via FR. Can you please give me any code example
> > > for COA ?
> > > 
> > > Accept my apology for asking dumb question :)
> > > 
> > > On Thu, Mar 24, 2016 at 7:37 PM, Anirudh Malhotra<8zero2ops at gmail.com
> > > wrote:
> > > 
> > > > Hi,
> > > > 
> > > > Yes exactly NAS is doing what freeradius has asked it to do. It is
> > > > disconnecting when the limit sent by radius is reached.
> > > > 
> > > > So in order to achieve what you want, remove the counter and use the
> > query
> > > > "SELECT SUM(AcctInputOctets)+SUM(AcctOutputOctets) FROM radacct
> > > > WHERE UserName='%{%k}'"
> > > > 
> > > > And if this goes greater than what is given in table for user then
> > send an
> > > > update coa.
> > > > 
> > > > Hope this helps
> > > > 
> > > > BR,
> > > > Anirudh Malhotra
> > > > 8zero2
> > > > Mail: 8zero2.in at gmail.com
> > > > Facebook: www.facebook.com/8zero2
> > > > Twitter: @8zero2_in
> > > > Blog: blog.8zero2.in
> > > > 
> > > > On 24 Mar 2016, 19:58 +0530, AACABLE / Mikrotik System<
> > aacable79 at gmail.com>,
> > > > wrote:
> > > > > Greetings,
> > > > > 
> > > > > I have FR 2.x with Mikrotik 6.x. I want that when user quota limit
> > end,
> > > > his
> > > > > bandwidth package should change from 1mb to 512k. I have made
> > counter and
> > > > > its working as expected. The only issue is that the changes are not
> > being
> > > > > done on the fly. Changes applies only when user disconnects, and
> > > > re-login.
> > > > > Same thing happens for users whose monthly quota limit finish but
> > they
> > > > are
> > > > > keep connected and gets denied only on re-login.
> > > > > 
> > > > > When I issue following command it works fine. (for the bandwidth COA
> > and
> > > > > POD)
> > > > > 
> > > > > echo User-Name := "zaib", Mikrotik-Rate-Limit = 512k/512k |
> > radclient -x
> > > > > 101.11.11.255:1700 coa 12345
> > > > > [Mikrotik accepts this command and change the bandwidth on the fly]
> > > > > 
> > > > > echo user-name=zaib | radclient -x 101.11.11.255:1700 disconnect
> > 12345
> > > > > [Mikrotik accepts this command and disconnects the user as
> > instructed]
> > > > > 
> > > > > How can I do this on the fly using FR? I know its the job of NAS but
> > NAS
> > > > > will act only when FR will tell him to do so. Kindly assist.
> > > > > 
> > > > > 
> > > > > *SQL *Counter:
> > > > > *=============*
> > > > > counter-name = Mikrotik-Total-Limit
> > > > > check-name = Mikrotik-Total-Limit
> > > > > reply-name = Mikrotik-Total-Limit
> > > > > sqlmod-inst = sql
> > > > > key = User-Name
> > > > > reset = daily
> > > > > query = "SELECT SUM(AcctInputOctets)+SUM(AcctOutputOctets) FROM
> > radacct
> > > > > WHERE UserName='%{%k}'"
> > > > > }
> > > > > *=============*
> > > > > *AUTHORIZE *Section:
> > > > > 
> > > > > dailyquota {
> > > > > reject = 1
> > > > > }
> > > > > if (reject) {
> > > > > ok
> > > > > update reply {
> > > > > Mikrotik-Rate-Limit := "512k/512k"
> > > > > Reply-Message := "You have reached your daily 300MB transfer limit.
> > > > > Enforcing 512k FUP Policy - zaib "
> > > > > }
> > > > > }
> > > > > *=============*
> > > > > 
> > > > > --
> > > > > Regard's
> > > > > JAY-Z
> > > > > -
> > > > > List info/subscribe/unsubscribe? See
> > > > http://www.freeradius.org/list/users.html
> > > > -
> > > > List info/subscribe/unsubscribe? See
> > > > http://www.freeradius.org/list/users.html
> > > 
> > > 
> > > 
> > > 
> > > --
> > > Regard's
> > > -
> > > List info/subscribe/unsubscribe? See
> > http://www.freeradius.org/list/users.html
> > -
> > List info/subscribe/unsubscribe? See
> > http://www.freeradius.org/list/users.html
> > 
> 
> 
> 
> --
> Regard's
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html