FR with Mikrotik - FUP Quota Bandwidth not changing auto

Sat Mar 26 06:07:55 CET 2016

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