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

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

By
Syed jahanzaib

FREERADIUS WITH MIKROTIK – Part #1

FREERADIUS WITH MIKROTIK – Part #2 > YOU ARE HERE

FREERADIUS WITH MIKROTIK – Part #3


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.

SCENARIO:

  • Mikrotik ver 6.34.x : 101.11.11.255
  • Mikrotik Radius Incoming : Enabled with Default Port 3799
  • Freeradius 2.1.10 : 101.11.11.245
  • 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) {
ok
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.

# CHECK OVER QUOTA USAGE
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}"
}
}
}

3- Enable ORIGINATINE-COA FILE

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


home_server localhost-coa {
type = coa
ipaddr = 101.11.11.255
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 101.11.11.255 {

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 = 101.11.11.255

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

 

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

1-2

 

3- Mikrotik received COA and re-acted properly.

2


DISCONNECT USER VIA DISCONNECT MESSAGE FROM FR TO MIKROTIK

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!

SYED JAHANZAIB

 

Advertisements

16 Comments »

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

    >

    Like

    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.

      Like

      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!

        Like

        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.

        Like

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

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

    Like

    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?

    Like

    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..

    Like

    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.

      Like

      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

        Like

        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

    >

    Like

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

  6. Hello sir, I’m implementing a billing system of my own just like DMA softlabs billing system, I just wanna configure the freeradius for authorization, I read your all three free radius blogs, I have some of doubts :
    1 – According to your blog post, There must an entry in the radreply table with radius attribute, but DMA softlabs radius manager uses php and also it doesn’t do an entry in radreply table instead it uses other tables like rm_users, rm_services etc to implement its billing system , how does it authorize in freeradius?

    2 – I’ve read your blog all the three of Free Radius topic but none of it corresponds to dma softlabs working, I have checked their radius directories and files but didn’t find any custom counters for daily basis , monthly and COA just like yours instead its all done by other tables like rm_services,rm_users,rm_managers etc…

    3 – Help me out to authorize like dma softlab radius manager billing system solution and freeradius authorizations

    Expecting a reply soon sir

    Like

    Comment by Anuj Upadhyay — January 24, 2017 @ 8:28 PM

  7. Thank you for your efforts, May Allah benefit you as you helped allot of people.
    i used your guidance and did some tweaks with the help of a friend,
    i am using daloradius VBOX enviroment.
    so i created a table named fub. and table contains the FUB speeds to downgrade customer to according to their profiles.
    so i can downgrade 8mbps customer to 2mbps , and downgrade 4mbps customer to 1mbps.
    also i added condition else after if . as COA dont get to customer’s that been downgraded after 12 midnight.
    also i am using different attributes as i have been using pfsense with daloradius. and mikrotik still supporting these attributes so i did not find a need to change them.
    now it is working on the flay as you said thank you again Syed.
    Code
    ###
    ##ACCOUNTING { ##

    # CHECK OVER QUOTA USAGE
    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 Actual QUOTA Allowed
    Tmp-Integer-1 := “%{sql: SELECT value FROM radgroupcheck WHERE attribute=’CS-Total-Octets-Daily’ AND groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    #Value of FUB download attribute
    Tmp-String-1 := “%{sql: SELECT value1 FROM fub WHERE groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    #Value of FUB upload attribute
    Tmp-String-2 := “%{sql: SELECT value2 FROM fub WHERE groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    #Value of normal download attribute
    Tmp-String-3 := “%{sql: SELECT value FROM radgroupreply WHERE attribute=’WISPr-Bandwidth-Max-Down’ AND groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    #Value of normal upload attribute
    Tmp-String-4 := “%{sql: SELECT value FROM radgroupreply WHERE attribute=’WISPr-Bandwidth-Max-Up’ AND groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    }
    if (“%{control:Tmp-Integer-0}” > “%{control:Tmp-Integer-1}”){
    # Update COA to Mikrotik with FUB attributes
    update coa {
    User-Name = “%{User-Name}”
    Acct-Session-Id = “%{Acct-Session-Id}”
    NAS-IP-Address = “%{NAS-IP-Address}”
    Framed-IP-Address = “%{Framed-IP-Address}”
    WISPr-Bandwidth-Max-Down = “%{control:Tmp-String-1}”
    WISPr-Bandwidth-Max-Up = “%{control:Tmp-String-2}”
    }
    }
    else {
    # Update COA to Mikrotik with Original attributes
    update coa {
    User-Name = “%{User-Name}”
    Acct-Session-Id = “%{Acct-Session-Id}”
    NAS-IP-Address = “%{NAS-IP-Address}”
    Framed-IP-Address = “%{Framed-IP-Address}”
    WISPr-Bandwidth-Max-Down = “%{control:Tmp-String-3}”
    WISPr-Bandwidth-Max-Up = “%{control:Tmp-String-4}”
    }
    }

    Like

    Comment by Mohammed Ismail — February 7, 2017 @ 7:17 PM

    • Thanks for your valuable contribution.

      Like

      Comment by Syed Jahanzaib / Pinochio~:) — February 8, 2017 @ 8:16 AM

      • Well i did some rush. with out enough knowledge.
        i think else { } section i added is wrong because sessions are cleared from server some how. and i don’t get traffic counted right.
        now i made a change on this to be;
        ##################
        elsif (“%{control:Tmp-Integer-0}” <= "%{control:Tmp-Integer-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}"
        WISPr-Bandwidth-Max-Down = "%{control:Tmp-String-5}"
        WISPr-Bandwidth-Max-Up = "%{control:Tmp-String-6}"
        }
        }
        ##################
        i don't know exactly what was happening but when i disabled that section things went right. so i guess this will work as i am testing right now. .

        Like

        Comment by Mohammed Ismail — February 9, 2017 @ 1:54 AM

  8. AT LAST.
    now combined daily and monthly quota are working together with COA .
    i had some trouble with numbers with fractions. but Ceiling option in mysql solve it.
    as i added an attribute that works with MB instead of Bytes to have the ability to count large numbers larger than 4 GB.
    i named the attribute WISPr-Max-All-MB and using mysql query to divide the sum on 1024*1024 to get the result in MB.
    now to the final working lines.

    # CHECK OVER QUOTA USAGE
    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 Actual QUOTA Allowed
    Tmp-Integer-1 := “%{sql: SELECT value FROM radgroupcheck WHERE attribute=’CS-Total-Octets-Daily’ AND groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    # Used Monthly QUOTA Value
    Tmp-Integer-2 := “%{sql: SELECT Ceiling(SUM(acctinputoctets)/(1024*1024)+SUM(acctoutputoctets)/(1024*1024)) AS Total FROM radacct where (acctstarttime between DATE_FORMAT(NOW() ,’%Y-%m-01′) AND NOW() AND acctstoptime between DATE_FORMAT(NOW() ,’%Y-%m-01′) AND NOW()) AND radacct.username=’%{User-Name}’}”
    #Value of Actual Monthly QUOTA Allowed
    Tmp-Integer-3 := “%{sql: SELECT value FROM radgroupcheck WHERE attribute=’WISPr-Max-All-MB’ AND groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    #Value of FUB download attribute
    Tmp-String-1 := “%{sql: SELECT value1 FROM fub WHERE groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    #Value of FUB upload attribute
    Tmp-String-2 := “%{sql: SELECT value2 FROM fub WHERE groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    #Value of normal download attribute
    Tmp-String-3 := “%{sql: SELECT value FROM radgroupreply WHERE attribute=’WISPr-Bandwidth-Max-Down’ AND groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    #Value of normal upload attribute
    Tmp-String-4 := “%{sql: SELECT value FROM radgroupreply WHERE attribute=’WISPr-Bandwidth-Max-Up’ AND groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    #Value of normal attribute1
    Tmp-String-5 := “%{sql: SELECT value FROM radgroupreply WHERE attribute=’WISPr-Bandwidth-Max-Down’ AND groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    #Value of normal attribute2
    Tmp-String-6 := “%{sql: SELECT value FROM radgroupreply WHERE attribute=’WISPr-Bandwidth-Max-Up’ AND groupname IN (SELECT groupname FROM radusergroup WHERE username=’%{User-Name}’)}”
    #Value of FUB Limited download attribute
    Tmp-String-7 := “%{sql: SELECT value1 FROM fub WHERE groupname is NULL}”
    #Value of FUB Limited upload attribute
    Tmp-String-8 := “%{sql: SELECT value2 FROM fub WHERE groupname is NULL}”

    }
    #Check for Monthly Usage
    if (“%{control:Tmp-Integer-2}” > “%{control:Tmp-Integer-3}”){
    # Update COA to Mikrotik with Limited 128K/64K
    update coa {
    User-Name = “%{User-Name}”
    Acct-Session-Id = “%{Acct-Session-Id}”
    NAS-IP-Address = “%{NAS-IP-Address}”
    Framed-IP-Address = “%{Framed-IP-Address}”
    WISPr-Bandwidth-Max-Down = “%{control:Tmp-String-7}”
    WISPr-Bandwidth-Max-Up = “%{control:Tmp-String-8}”
    }
    }
    #If Monthly Qouta were not exhausted, Then proceed with Daily Qouta
    elsif (“%{control:Tmp-Integer-0}” > “%{control:Tmp-Integer-1}”){
    # Update COA to Mikrotik with FUB attributes
    update coa {
    User-Name = “%{User-Name}”
    Acct-Session-Id = “%{Acct-Session-Id}”
    NAS-IP-Address = “%{NAS-IP-Address}”
    Framed-IP-Address = “%{Framed-IP-Address}”
    WISPr-Bandwidth-Max-Down = “%{control:Tmp-String-1}”
    WISPr-Bandwidth-Max-Up = “%{control:Tmp-String-2}”
    }
    }
    #If either Monthly or Daily Qouta are not exhausted
    else {
    # Update COA to Mikrotik with Original Customer’s attributes
    update coa {
    User-Name = “%{User-Name}”
    Acct-Session-Id = “%{Acct-Session-Id}”
    NAS-IP-Address = “%{NAS-IP-Address}”
    Framed-IP-Address = “%{Framed-IP-Address}”
    WISPr-Bandwidth-Max-Down = “%{control:Tmp-String-3}”
    WISPr-Bandwidth-Max-Up = “%{control:Tmp-String-4}”
    }
    }

    and for fub table
    id groupname value1 value2
    0 1MB-10GB-M 256000 64000
    1 1MB-10GB-SOL 256000 64000
    2 2MB-20GB-M 512000 96000
    3 2MB-20GB-SOL 512000 96000
    4 3MB-32GB-M 768000 128000
    5 3MB-40GB-M 768000 128000
    6 4MB-40GB-M 1024000 128000
    7 4MB-40GB-SOL 1024000 128000
    8 8MB-80GB-M 2048000 256000
    9 8MB-80GB-SOL 2048000 256000
    10 NULL 128000 64000

    Like

    Comment by Mohammed Ismail — February 11, 2017 @ 2:01 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:

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: