FR with Mikrotik - FUP Quota Bandwidth not changing auto

Anirudh Malhotra 8zero2ops at gmail.com
Sat Mar 26 06:17:02 CET 2016 

You can so that by changing the accounting update query, adding a column in accounting table and create a variable in dictionary and set that variable whenever coa is sent insert it into accounting table.

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

On 26 Mar 2016, 10:38 +0530, AACABLE / Mikrotik System<aacable79 at gmail.com>, wrote:
> Ok I was able to make all things work in lab testing. only part remains is
> to add the COA Sent column and use it accordingly so that COA should be
> sent once only, otherwise its sending one every update packets. here are
> short notes of it I documented dueto ST memory loss problem.
> https://aacable.wordpress.com/2016/03/25/mikrotik-with-freeradiusmysql-change-on-the-fly-with-coa-part-2/
> 
> On Fri, Mar 25, 2016 at 3:31 PM, AACABLE / Mikrotik System<
> aacable79 at gmail.com>wrote:
> 
> > Ok I will see for the column addition. Hopefully I will be able to make it.
> > 
> > I want to query user quota for current date. I am using following code
> > 
> > SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) AS Total FROM radacct
> > where (acctstarttime between DATE_FORMAT(NOW(),'%Y-%m-%d') AND NOW() AND
> > acctstoptime between DATE_FORMAT(NOW() ,'%Y-%m-%d') AND NOW()) AND
> > radacct.username='%{User-Name}'
> > 
> > It works fine if there is acctstoptime value in table. BUT if user have
> > not disconnected yet (and have no previous session for the day) it returns
> > NULL.
> > 
> > So how can i can get the user today used quota value even if user
> > acttstoptime is null?
> > 
> > 
> > On Fri, Mar 25, 2016 at 12:27 PM, Anirudh Malhotra<8zero2ops at gmail.com
> > wrote:
> > 
> > > For that you can insert something in accounting table(perhaps a column
> > > coa_sent), whenever you send a coa and check that in subsequent accounting
> > > packets
> > > 
> > > 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, 11:18 +0530, AACABLE / Mikrotik System<
> > > aacable79 at gmail.com>, wrote:
> > > > Finally after reading your example, I managed to make it working with
> > > > another approach, but something a little bit advance. Its working now.
> > > COA
> > > > is being sent Ok.
> > > > 
> > > > update control {
> > > > 
> > > > # Used QUOTA / zaib
> > > > Tmp-Integer-0 := "%{sql:SELECT
> > > (SUM(acctinputoctets)+SUM(acctoutputoctets))
> > > > AS Total FROM radacct where radacct.username='%{User-Name}'}"
> > > > 
> > > > #Value of FUP Bandwidth limit that is 512k
> > > > Tmp-String-5 := "%{sql: SELECT value FROM fup WHERE
> > > > attribute='Mikrotik-Rate-Limit' AND username='%{User-Name}'}"
> > > > 
> > > > #Value of Actual QUOTA Allowed
> > > > Tmp-String-1 := "%{sql: SELECT value FROM radcheck WHERE
> > > > attribute='Mikrotik-Total-Limit' AND username='%{User-Name}'}"
> > > > 
> > > > # User Session ID
> > > > Tmp-String-3 := "%{sql:select calledstationid from radacct where
> > > > acctsessionid='%{Acct-Session-Id}'}"
> > > > }
> > > > 
> > > > if ("%{control:Tmp-Integer-0}">"%{control:Tmp-String-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 = "%{control:Tmp-String-5}"
> > > > }
> > > > }
> > > > }
> > > > 
> > > > Just want to clear one thing more, FR keep sending COA request to NAS on
> > > > every accounting update. Is this normal? my understanding was that once
> > > FR
> > > > send COA to NAS, it should not send it again, I am seeing COA Update
> > > > requests on every accounting packet update (for test i have set 1 mnt
> > > > interval)
> > > > 
> > > > On Fri, Mar 25, 2016 at 9:25 AM, Anirudh Malhotra<8zero2ops at gmail.com
> > > > wrote:
> > > > 
> > > > > 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
> > > > > -
> > > > > 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
> > 
> > 
> > 
> 
> 
> --
> Regard's
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

More information about the Freeradius-Users mailing list