Freeradius 3.0.15 Fair Usage Policy doesn't work

[Ali Arslan]

 Hi Görkem,
First write an sql scalar function that returns RateLimit string for your NAS; For example for Mikrotik 8M/3M.Lets assume that you created function GetRateLimit(@UserName) 
Then as Alan said, read sites-available/originate-coa and make your changes if you need and enable.
Under sited-enable/default accounting section add following unlang code:
                update coa {
                        &User-Name := "%{User-Name}"
                        &Mikrotik-Rate-Limit = "%{sql:SELECT GetRateLimit('%{User-Name}')}"
                }
If you don't want send CoA request always and if you have records in radreply for user RateLimit you can check if current radreply RateLimit is not equal to the value of "%{sql:SELECT GetRateLimit('%{User-Name}')}" and send request in that condition.
Not to run same query multiple times you can store the value in a variable
ali arslan    On ‎Wednesday‎, ‎October‎ ‎10‎, ‎2018‎ ‎06‎:‎52‎:‎54‎ ‎PM‎ ‎+03, Görkem İnanç Korkmaz <gorkeminanckorkmaz at gmail.com> wrote:  
 
 Thanks for your comment Ali, but sqlcounter already didn't work

I have been working on freeradius over 4 month, where I am looked at which
web sites describes their solution is working about fair usage policy
didn't worked. Than I am confused.
So I decide to ask the freeradius list to solve this problem. If you have
an example could share with me.

Also would you like contact on Linkedin

Görkem İnanç KORKMAZ
Junior IT Engineer


Görkem İnanç Korkmaz <gorkeminanckorkmaz at gmail.com>, 10 Eki 2018 Çar, 16:17
tarihinde şunu yazdı:

> I am running on the freeradius 3.0.15 on Ubuntu 16.04 almost over 4 month.
> But still I couldn't figure out how to deploy fair usage policy.
>
> I just want to reply Mikrotik-Rate-Limit to user when they hit the quota
> for daily.
>
> 1- nano /etc/freeradius/mods-enabled/sqlcounter
>
> I added the following counter
>
>    sqlcounter dailyquota{
>    count_attribute = Acct-Input-Octets
>    counter_name = My-Total-Data-Limit
>    check_name = My-Total-Limit
>    sqlmod-inst = sql
>    key = User-Name
>    reset = never
>    query = "SELECT ((SUM(AcctInputOctets)+SUM(AcctOutputOctets))) FROM
> radacct WHERE UserName='%{User-Name}'"
>    }
>
> 2- nano /etc/freeradius/sites-enabled/default
>
>    nano /etc/freeradius/sites-available/default
>
> In authorize section added the counter name
>
>    authorize {
>    ......
>    dailyquota
>    ......
>    }
>
> In accounting section added the counter query and reply message
>
>    accounting {
>    ......
>    if ( "%{sql: SELECT ((SUM(AcctInputOctets)+SUM(AcctOutputOctets)))
> FROM radacct WHERE UserName='%{User-Name}'}" > "%{sql: SELECT value from
> radcheck where username='%{User-Name}' and attribute='My-Total-Limit'}" ) {
>    update disconnect {
>    Mikrotik-Rate-Limit := "2M/2M"
>    Reply-Message := "You have reached your transfer limit. Limited
> bandwidth"
>    }
>    }
>    ......
>    }
>
> 3- Define the My-Total-Limit attribute on dictionary
>
>    ATTRIBUTE      My-Total-Limit          3001    integer64
>
> 4- Add the user with the attribute that 10 MB quota
>
>    INSERT INTO radcheck (id ,username ,attribute ,op ,value) VALUES (NULL
> , 'gorkem', 'Cleartext-Password', ':=', '123');
>    INSERT INTO radcheck (id ,username ,attribute ,op ,value) VALUES (NULL
> , 'gorkem', 'My-Total-Limit', ':=', '10000000');
>
> 5- Restart the freeradius service, try to authenticate then look your
> accounting data.
>
> Result is interesting, when user pass over his quota (10MB), user can
> still keep going to use internet. Nothing changed while user is connected.
>
> But If the user logout, He try to login again reply message deployed when
> he logged in. After than user can get the new speed limit for his session.
> I don't want to do this. I think it must be automatically.
>
> I want to send CoA reqeust to Mikrotik NAS client for change the user
> speed limit automatically. How can I achive that?
>
> Is there anyone figure out, that how to do ?
>
> Here is my debug output:
> https://drive.google.com/open?id=1ClT3D291tO6GXf9L6VPJ1h1ZTsizFUhu
>
>
> Also there one more question, in my debug output that i realized. When I
> add the dailyquota freeradius, it automatically add the
> reply_name=Session-Timeout to counter. So I looked on the Mikrotik router
> of the users session timeout is deployed My-Total-Limit value as the time
> 10000000 seconds (almost 116 day)
>
> How can I solve this problem, Is there anyone figure out, how it should be
> done?
>
>
> Görkem İnanç KORKMAZ
> Junior IT Engineer
>
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html