FR with Mikrotik - FUP Quota Bandwidth not changing auto
AACABLE / Mikrotik System
aacable79 at gmail.com
Fri Mar 25 11:31:55 CET 2016
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
More information about the Freeradius-Users
mailing list