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

~ 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 Click here to read more on FR tutorials …

For better approach on COA read following

https://aacable.wordpress.com/2018/06/27/freeradius-with-mikrotik-part-14-dynamic-bandwidth-change-on-the-fly-using-coa-with-radclient/

---

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

---

NOTE: AS of 30-January 2018, I wrote another post which described the COA working more precisely, Click here to route to that post …

---

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]

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

This section, may not relevant, read more at

FREERADIUS WITH MIKROTIK – Part #11 – Prepaid Hourly Accounts along-with the mighty COA !

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

---

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}"

}

}

}

---

RADCLIENT COA CODE:

To change bandwidth speed for already connected users ON THE FLY using RADCLIENT utility, means without disconnecting him. Use following code. Its well tested with Freeradius 2.x and Mikrotik 6.4x.x

101.11.11.255 is Mikrotik NAS IP, 1700 is Radius incoming port on NAS, 12345 is SECRET in RADIUS in NAS.

For PPPoE

echo User-Name=$USERNAME,Acct-Session-Id=$ACCTSESID,Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit="128k/128k" | radclient -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET

For HOTSPOT

echo Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit="128k/128k" | radclient -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET

Salam Alykum!

SYED JAHANZAIB