Syed Jahanzaib Personal Blog to Share Knowledge !

March 25, 2016

Mikrotik with Freeradius/mySQL – Change on the FLY with COA # Part-2

Filed under: freeradius — Tags: , , , , — Syed Jahanzaib / Pinochio~:) @ 4:58 PM

~ COA Implementation in Freeradius 2.x for Mikrotik ~
! A wild goose-chase! From the CORE of the FREERADIUS !

Syed jahanzaib




Personnel Note:

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not ROCKET SCIENCE as some PRO in the industry try to pose. You can do it as well, the only thing required is the ultimate passion to achieve the goal. And with the right search, reading, understanding logic’s, you can do all on your own. I strongly encourage to read the FR mailing list and Google



Recently I was doing some lab testing on the Freeradius System and got stuck with the configuration of COA (change of authority) so that any changes like disconnection OR bandwidth package change on the FLY without disconnecting the active user, example different bandwidth for day/night or slower speed package for over quota user.

I made some workaround like BASH scripts which checks mysql for quota usage vs used data and disconnect users or change package via radclient coa. But I didn’t wanted to get involved in bash scripting , although using bash script have many other benefits as well like sending sms to user, email, or any other customized action. B

But still exploring COA was essential for some instant functionality.

Finally after few days R&D and some FR mailing list digging, I have managed to make COA on FR to work with Mikrotik 🙂 Alhamdolillah.

I assume you have working setup of Freeradius with the Mikrotik.


Before going into details. READ THIS IF YOU DON’T WANT TO get involve in Wild-Goose chase !

  1. These rough posts are not for the beginners, If you are a beginner by any chance , read below…
  2. First read what Freeradius system really is. understand the logic’s and terminologies. Without proper understanding, you will be doing a wild-goose chase for sure.
  3. Take following guide as an example only. This is by no means a complete production-ready guide. But just to begin the journey only. LAB testing you can refer.
  4. Don’t just copy paste the code blindly, they are for reference purposes only.
  5. Read it again and again, try to understand.
  6. Take Help from ‘Uncle Google‘ if you stuck at any point.


  • Mikrotik ver 6.34.x :
  • Mikrotik Radius Incoming : Enabled with Default Port 3799
  • Freeradius 2.1.10 :
  • OS = Ubuntu 12.4 / 32bit with apt-get base freeradius installation

We have user on FR with following credentials.

  • ID = zaib
  • Bandwidth Allowed = 1024k/1024k
  • Daily Quota Allowed [Test] = 1 MB
  • Bandwidth for Over Quota Users [For the DAY) = 512k/512k
  • [Will revert back to 1024k on next date change]
 t1 t2

Now we want that when user ‘zaib‘ consumes 1 MB in a day, his bandwidth should drop to 512k for the rest of the day. and all of these changes should be done on the FLY, without disconnecting the user using the COA : )~ , yeah that’s what its made for. and when the date changes, he should revert back to 1024 k.

1- SQL Daily Counter

First make the counter for daily traffic

nano /etc/freeradius/modules/sqlcounter_expire_on_login

and add following code

sqlcounter dailyquota {
            counter-name = Mikrotik-Total-Limit
            check-name = Mikrotik-Total-Limit
            reply-name = Mikrotik-Total-Limit
            sqlmod-inst = sql
            key = User-Name
            reset = daily
# This query will take data for today DATE only, It caused 4 hours of RnD and HEADACHE to me. What kind of retard person I am ! Ughhh. ZAIB
            query = "SELECT SUM(acctsessiontime - GREATEST((%b-UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username='%{%k}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"

Save & Exit.

2- Adding Counter reference in AUTHORIZE + ACCOUNTING Section

Now we will add the counter in AUTHORIZE { section and also add the UNLAG statement in the ACCOUNTING{ section to match if the quota is above or less and take action depend on the result.

nano /etc/freeradius/sites-enabled/default

Add following under AUTHORIZE { section.

authorize {

dailyquota {
reject = 1
if (reject) {
update reply {
Mikrotik-Rate-Limit := "512k/512k"
Reply-Message := "You have reached your transfer limit. Enforcing FUP Package - zaib"

Now add following under ACCOUNTING { section (in same file) UNLAG Query for matching True or False.

update control {

# Used QUOTA Value
Tmp-Integer-0 := "%{sql:SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) AS Total FROM radacct where acctstarttime >= CURDATE() AND radacct.username='%{User-Name}'}"

# Value of FUP Bandwidth limit that is 512k, It is stored in a separate table 
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}'}"

Tmp-String-3 := "%{sql:select calledstationid from radacct where acctsessionid='%{Acct-Session-Id}'}"

if ("%{control:Tmp-Integer-0}" > "%{control:Tmp-String-1}"){
# Update COA to Mikrotik
    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}"


Edit file /etc/freeradius/sites-available/originate-coa and add/modify  following

home_server localhost-coa {
type = coa
ipaddr =
port = 3799
secret = 12345
coa {
irt = 2
mrt = 16
mrc = 5
mrd = 30

4- Add COA HOME Server Entry in clients.conf

Add your Mikrotik in clients.conf as well.

 nano /etc/freeradius/clients.conf

and add following.

This example have Mikrotik as NAS, and COA Separately.

# Mikrotik as NAS entry  / zaib

client {

port            = 1700

secret          = 12345

shortname       = Mikrotik }

# COA Section / zaib , what a headache it was to enable COA, I am Quite a DUFFER for Sure : D

home_server example-coa {

type = coa

ipaddr =

port = 3799

secret = 12345

coa {

irt = 2

mrt = 16

mrc = 5

mrd = 30



DEBUG is the KEY to SUCCESS ! Allah Shuker

Now Restart FREERADIUS in DEBUG mode by

freeradius -X

Now you will see that when user zaib connected, his package was intially 102k. as soon he crosses his quota , FR will send COA (by using the update-control script) to mikrotik and package will be changed dynamically instantly.


See following snapshots.


1- When user first connected, his package was 1024k.



1- When user consumes his quota, FR counted and send COA to mikrotik to drop the package to 512k on the FLY.



3- Mikrotik received COA and re-acted properly.



In above example we only changed the bandwidth package. If you want to disconnect user, you can simply use the disconnect message as well. [in accounting section]

Example Code:

if ("%{control:Tmp-Integer-0}" > "%{control:Tmp-String-1}"){

# Update COA, If above statement matches, then execute below ...

#    update coa {

update disconnect {

User-Name = "%{request:User-Name}"





Salam Alykum!




  1. i want to do automatic bandwidth allocation (time based) using freeradius


    Comment by George Midia — March 26, 2016 @ 4:44 PM

    • If you are new to freeradius, I suggest to use some fronted like RADIUS MANAGER which have many nice features to do crazy stuff.

      Comment by Syed Jahanzaib / Pinochio~:) — March 26, 2016 @ 4:51 PM

      • you can do it with crontab , i have the full php script which is working 100% i can share it if Syed Jahanzaib dont mind!

        Comment by Khouzam Yaghi — April 3, 2016 @ 8:24 PM

      • please share the info. I made this blog to share knowledge, so any information, tips n tricks as contribution would be highly appreciable. or email me at aacable at hotmail dot com . and i will add it to blog as well.

        Comment by Syed Jahanzaib / Pinochio~:) — April 4, 2016 @ 8:11 AM

  2. […] FREERADIUS WITH MIKROTIK – Part #2 > […]

    Pingback by Mikrotik with Freeradius/mySQL # Part-1 | Syed Jahanzaib Personal Blog to Share Knowledge ! — March 28, 2016 @ 4:27 PM

  3. wnat if you want to slow down the user in another way, i.e user with 2MB after 1GB drop down to 1.5M and after 1.5GB drop down to 1MB etc.., what will you do in this case?

    Comment by Khouzam Yaghi — April 3, 2016 @ 8:20 PM

  4. what if you want to slow down the speed more than once i.e 2MB after 1GB will drop down to 1.5MB and after 1.5GB will drop down to 1MB etc..

    Comment by Khouzam Yaghi — April 3, 2016 @ 8:32 PM

    • I guess not possible with FR. but yes maybe possible with the customized scripting or php. i am sure its possible.

      Comment by Syed Jahanzaib / Pinochio~:) — April 4, 2016 @ 8:10 AM

      • I did it already using php script, what i did is that i am calling a php script on every accounting packet, but i am thinking to change to unlang since performance wise its better not to call an external script at real time, i already did my radius system with about 10K users and its working perfectly, with checkrad implemented for simultaneous-use and also a ping feature , which will ping each user without the need to be on the same local network. If you need any help contact me, i can show you a sample of what i did

        Comment by khouzam yaghi — April 4, 2016 @ 12:13 PM

  5. aoa sir main mikrotik main user manager use krna cha raha hon mager us m admin login karta hoon tu login nae ho raha error unknown customer show kr raha h aur jb tarminal m jaker admin create krta h us m error databases is not acceptable, yet kindly help Sent from my iPhone


    Comment by imran ali — April 15, 2016 @ 8:49 AM

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: