Syed Jahanzaib Personal Blog to Share Knowledge !

March 31, 2016

ISP Ticket Support System for HelpDesk

Filed under: Linux Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 12:44 PM

helpdesk

Recently an OP was required to deploy an Help-Desk ticket system so that user can log there complains / suggestions and they must get ticket number in order to track the complain. [& generally it is also required by the PTA / Licensing Authorities].

Ideally the best approach is to create your own helpdesk complain system like in PHP as frontend and mySQL as backend because customized solutions are good and to the point according to the network requirements like you can integrate email + sms functions as well to let user / admin know about the complain status.Such systems are very common in almost every sophisticated networks.

But not every admin have good skills in PHP and mySQL, and in most cases OP usually don’t have enough budget to hire the resources to done the job in proper fashioned way.
To overcome the issue at lower level, I deployed ‘OS-Ticket‘  ISP Support Ticket System at a local network, so I thought to share it with all.

For beginners , you can use use already available “OSTICKET” system which is FREE for your local hosting requirements. Above all you can modify its code to add functions or change the theme as required. OSTICKET is very good and feature rich ticketing system. Installation of this application is very easy and you can modify it at certain levels. There is a support page for user end, admin and agents. read about it on google.

Writing this post to share the application deployment idea only, for complete guide, you should visit there Wiki and forums.


Installation:

Following components are used in the installation of OSTICKET system.

  1. Ubuntu 12.4 / 32bit
  2. mySQL with PHP modules
  3. GMAIL account to be used to send email alerts to admin / agents / users

Note: I assume you already have the mysql/php in working condition.

Lets Begin …

First install imap support in php which will be used later for email support.

sudo apt-get -y install php5-imap
# Restart Apache web service so changes can take effect.
service apcahe2 restart

Now download the osticket (the latest release is 1.9.12 at the time of writing this post 31st/mar/2016)

mkdir /temp
cd /temp
wget http://osticket.com/sites/default/files/download/osTicket-v1.9.12.zip
unzip osTicket-v1.9.12.zip
cd upload

Now copy it to web folder so that we can access the setup wizard via browser.

mkdir /var/www/support
cp -vr * /var/www/support/
cp /var/www/support/include/ost-sampleconfig.php /var/www/support/include/ost-config.php
chmod 0666 /var/www/support/include/ost-config.php

Execute the configuration wizard via Browser !

Open browser and access following URL.

http://your_server/support

As showed in the image below …

1

on the next page, fill up the data as required. Pay attention to mySQL section and make sure you enter correct information for mysql and DB. You can take example as showed in below image …

2- sql setting
After config done and you dont see any error continue below …

change the permission back to default for the config file.

chmod 0644 /var/www/support/include/ost-config.php

rm -fr /var/www/support/setup/

 

OSTICKET Support /Admin Panel !

Now login to ADMIN panel to continue the rest of configuration.

Admin Panel:

http://your_server/scp

End-User Panel:

http://your_server/support/

Go ahead, make changes, add your information, add your custom logo which will be showed to admin/user.

The important part is the EMAIL alerts. Take example as showed here in the image below for email.

Goto Emails and add new EMAIL Address which will be used to send or receive email. You can configure different emails for different support staff as well. example support / outdoor field staff etc.

 

1- email-config

 

4-EMAIL CONFIG

 

autorespond


Log First Ticket From User-End ! TEST …

Open panel from user end. by default , user end panel opens.

5- user access

 

6- log ticket

 

7


 

Once the ticket is locked, Admin and user will be informed via email.

USER EMAIL ALERT:

ticket log ok

ADMIN EMAIL ALERT:

adminmail alert

You can change logo as well.

customlogo


 

OSTICKET system is very good, feature rich to create basic ticket system. You can make your own bash scripts which can send SMS upon locking ticket by query mysql tables and act accordingly. You can also add this ticket system with your existing RADIUS billing system depend on the billing code.

Regard’s

Syed Jahanzaib

Advertisements

March 28, 2016

Mikrotik with Freeradius/mySQL – Change IP Pool After Expiration # Part-3

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

expire

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s>

FREERADIUS WITH MIKROTIK – Part #2  – COA

FREERADIUS WITH MIKROTIK – Part #3 – Expiration YOU ARE HERE

FREERADIUS WITH MIKROTIK – Part #4 – Auto Mac Binding

FREERADIUS WITH MIKROTIK – Part #5 – Stale Sessions

FREERADIUS  WITH MIKROTIK – Part # 6 – External Auth Script & RADPOSTAUTH

FREERADIUS WITH MIKROTIK – Part #7  – Quota Limit

 

 


 

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.


Scenario:

It is required that when user account expires, he can still login but he should get IP from ‘expired pool’ so that we can redirect him to payment reminder page or route/manage in some other customized way via NAS.

1- FREERADIUS Section:

For this purpose we will modify the EXPIRATION module. as mentioned below

nano /etc/freeradius/sites-available/default

Add or modify

expiration{
userlock = 1
}
if(userlock){
# Let him connect with EXPIRED pool in reply
ok
update reply {
Reply-Message := "Your account has expired, %{User-Name} / Reason: DATE LIMIT REACHED / zaib"
Framed-Pool := "expired-pool"
}
}

Save & Exit.


Now add the user ‘expiration‘ in radcheck table using mysql

Note: If you add expiration check in RADCHECK section, the NAS will auto disconnect that user after the time reaches (session timeout) , or if you want to DC him using script, see the last section of this guide. ZAIB~

INSERT INTO `radius`.`radcheck` (
`id` ,
`username` ,
`attribute` ,
`op` ,
`value`
)
VALUES (
NULL , 'zaib', 'Expiration', ':=', '28 Mar 2016 15:35'
);

TIP: If we will add the expiration check in radcheck table for specific user, then FR will provide session timeout value for that user to the NAS, therefore NAS will disconnect the USER automatically & you dont need to disconnect him manually or by script. Session timeout provided is done automatically by FR if this check is present for that user. However in some cases if we want to manually disconnect those users (example without expiration value in radcheck attribute), then we have to use the script that should check for expired users today and Disconnect them & update group. see the last script in this pot i shared.

Note that I am using Expiration Date along with exact time as well. This can also be used to reduce receiving annoying customer call whose account expires default in night at 00:00 hours as default expiration is done in night and user will have no where to contact as in late night no one pick help support calls in DCN.

OR PHPMYADMIN as showed below …

1- exp


2- Mikrtoik Section

Add new IP pool with the name of ‘expired-pool‘ (or as mentioned in Framed-Pool section)

/ip pool
add name=expired-pool ranges=192.168.100.1-192.168.100.255

2-exp pool


Now try to login with user (which expiry date(time) have passed. and you will observe that user will still be able to login but he will get ip from the expired pool, and NAS will handle the request afterwards, either to redirect to expired pool, or some other customized action : )

Result:

3- exp radclient

win-login


# TIP:

Script to disconnect user expiring today & change there group in RADGROUP section, and email as well to admin

You can cron this script which may run daily. This will create list of EXPIRATION (expiration date will be pick from USERS table). It will compare user expiration ate with TODAY date, if matched, it will add update this user Group membership in radgroupreply to name DISABLED. Also it will kick user from both NAS ( i ad two nases)

#!/bin/sh
#set -x
# # # # QUICK AND DIRTY MADE IN 2 MINUTES. YOU MAY ADD VARIOUS CHECKS AND CONTROL IN THIS SCRIPT
# BASH base script to disconnect users whose expiry is today. ( i added function to include users from last week till date , just in case radius misses any*)
# the simple logic can be applied for about any other task as well. I tried to make it as simple as it can be
# By Syed Jahanzaib
# CREATED on : 16th July, 2015 / Modified on 16th Nov, 2015

# Local Variables

# Mysql credentials
SQLID="root"
SQLPASS="SQLPASSWORD"

# Mikrotik NAS IP and Radport and Shared Secret
NAS2="10.0.0.1:1700"
NAS1="10.0.0.2:1700"
NAS1_SECRET="RADSECRET"
NAS2_SECRET="RADSECRET"

# Temp holder for user list
TMP="/tmp/7days_till_today_expired_users_list.txt"
TODEXPLIST="/tmp/only_today_expired_users_list.txt"
#TODAY=$(date +"%Y-%m-%d")
TODAY=`date -d "yesterday" '+%Y-%m-%d'`
WEEK=`date -d "-7 days" '+%Y-%m-%d'`

# Gmail Data
GMAILID="YOURGMAILID@gmail.com"
GMAILPASS="GMAIL-PASS"
SMTP="64.233.184.108:587"
ADMINMAIL1="ADMIN1@hotmail.com"
ADMINMAIL2="ADMIN2@hotmail.com"
COMPANY="ZAIB-COMPANY"
MAILSUB="$COMPANY - List of account expired on $TODAY"

# Pull users that are expiring TODAY
mysql -u$SQLID -p$SQLPASS --skip-column-names -s -e "use radius; select username from user_status_info where card_expire_on between '$WEEK' AND '$TODAY';" > $TMP
mysql -u$SQLID -p$SQLPASS --skip-column-names -s -e "use radius; select username,card_expire_on from user_status_info where card_expire_on ='$TODAY';" > $TODEXPLIST
# Apply formula
num=0
cat $TMP | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk '{print $1}'`
ACCTID=`mysql -u$SQLID -p$SQLPASS --skip-column-names -s -e "use radius; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime IS NULL;"`

# Update user status in RAD group so that he will get ip from DISABLED pool or be rejected
mysql -u$SQLID -p$SQLPASS --skip-column-names -s -e "use radius; update radusergroup set groupname='DISABLED' where username='$USERNAME';"

# Disconnect users now using RADCLIENT with username adn Account session ID taken from radacct table
echo user-name=$USERNAME,Acct-Session-Id=$ACCTID | radclient -x $NAS1 disconnect $NAS1_SECRET
echo echo user-name=$USERNAME,Acct-Session-Id=$ACCTID | radclient -x $NAS2 disconnect $NAS2_SECRET
done

TOT=`cat $TMP | wc -l`
echo "
-----------
-----------

From $WEEK till $TODAY - Expired Users = $TOT"

sendemail -t $GMAILID -u "$MAILSUB" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$TODEXPLIST -o message-content-type=text
sendemail -t $GMAILID -u "$MAILSUB" -o tls=yes -s $SMTP -t $ADMINMAIL2 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$TODEXPLIST -o message-content-type=text 

CRON Example: to run it daily at 5pm

# Run @ 5 pm daily in evening
0 17 * * * /temp/dc_expire.sh

Regard’s

Syed Jahanzaib

 

 

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


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

This section, may not relevant, read more at

https://aacable.wordpress.com/2018/01/30/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

 

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

 

March 11, 2016

Mikrotik with Freeradius/mySQL # Part-1

Filed under: freeradius — Tags: , , , , , — Syed Jahanzaib / Pinochio~:) @ 3:42 PM

fre


 

 


Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand logic & create your own solution as per your network scenario. Just dont follow copy paste.

If anybody here thinks I am an expert on this stuff, I am NOT certified in anything Mikrotik/Cisco/Linux or Windows. However I have worked with some core networks and I read , research & try stuff all of the time. So I am not speaking/posting about stuff I am formerly trained in, I pretty much go with experience and what I have learned on my own. And , If I don’t know something then I read & learn all about it.

So , please don’t hold me/my-postings to be always 100 percent correct. I make mistakes just like everybody else. However – I do my best, learn from my mistakes and always try to help others

Regard's
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


Make your own Billing system in Linux with Freeradius 2.1.10 / MySQL 5.5.47
# Part-1

[This Guide will be updated with many further supporting posts)

The aim of writing this post was that there are number of radius products available on the internet with lots of features, each have some unique features. But this is also true that none of them is 100% perfect for every type of ISP. The reason is that every ISP/Network have different sort of local requirements and billing mode. If you ahve searched on google you will find that there are tons of guides for freeradius implementation, but most of them have either incomplete data , or difficult explanation, or does not meet the practical requirements of Desi ISP. Thats why I started this guide so that info that is not common on the net can be shared here. plus most important you can learn on your own using this baby step.

In this post I have made some quick guide to install a very basic level of billing system by using Freeradius/mysql on UBUNTU 12.4 [32bit]. Mikrotik is being used as NAS to connect user and freeradius will be used for authentication/accounting billing system.

Quick Code to get started.

Radius IP = 101.11.11.245
Mikrotik IP = 101.11.11.255

Let’s Rock …


 

First Update Ubuntu (12.4  32bit) and install the required modules

# Update Ubuntu First
apt-get update
# Install Required pre requisites modules
apt-get -y install apache2 mc wget rcconf make gcc mysql-server mysql-client curl
apt-get -y install phpmyadmin
apt-get install freeradius freeradius-mysql freeradius-utils

This may take some moments as average of 100+MB will be downloaded from the net and will be installed automatically. Sit back and relax.

After update/installation of components done, Proceed to MYSQL configuration below …

TIP: Use phpmyadmin, it will be much easier for you to add/edit/delete records from DB using its GUI …



MYSQL  CONFIGURATION:

Create Freeradius Database in MYSQL

Now create Freeradius Database in mySQL.

Login to mysql (use mysql root password that you entered in above steps)

mysql -uroot -pzaib1234
create database radius;
grant all on radius.* to radius@localhost identified by "zaib1234";

Import Freeradius Database Scheme in MYSQL ‘radius’ DB

Insert the freeradius database scheme using the following commands, Make sure to change the password ####

mysql -u root -pzaib1234 radius < /etc/freeradius/sql/mysql/schema.sql
mysql -u root -pzaib1234 radius < /etc/freeradius/sql/mysql/nas.sql

Create new user in MYSQL radius database (For Testing Users)

User id = zaib
Password = zaib
Rate-Limit = 1024k/1024k

mysql -uroot -pzaib1234
use radius;
INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES ( NULL , 'zaib', 'Cleartext-Password', ':=', 'zaib');
INSERT INTO radreply (username, attribute, op, value) VALUES ('zaib', 'Mikrotik-Rate-Limit', '==', '1024k/1024k');
exit

Note:
You can skip the Framed-IP-Address part or modify it as per required.


FREERADIUS CONFIGURATION:

SQL.CONF

Edit following file  /etc/freeradius/sql.conf

nano /etc/freeradius/sql.conf file

Change the password to zaib1234 (or whatever you set in mysql if required) and Uncomment the following

readclients = yes

So some portion of the file may look like following, after modifications

# Connection info:
server = "localhost"
#port = 3306
login = "radius"
password = "zaib1234"
readclients = yes

sql-mod

Save and Exit the file


/etc/freeradius/sites-enabled/default

Now edit the /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

Uncomment the sql option in the following sections

accounting

# See “Authorization Queries” in sql.conf

sql

session

# See “Authorization Queries” in sql.conf

sql

Post-Auth-Type

# See “Authorization Queries” in sql.conf

sql

[/sourcecode]

Save and Exit the file


RADIUSD.CONF

Now edit /etc/freeradius/radiusd.conf file

nano /etc/freeradius/radiusd.conf

#Uncomment the following option

$INCLUDE sql.conf

Save and exit the file


/etc/freeradius/sites-available/default

Last but no least , edit /etc/freeradius/sites-available/default

nano /etc/freeradius/sites-available/default

Search for LINE

#  See “Authorization Queries” in sql.conf

and UN-COMMENT the SQL word below it.

Example After modification

#  See “Authorization Queries” in sql.conf

sql

Save and exit.


ADDING ‘NAS’ [Mikrotik] in CLIENTS.CONF

To accept connectivity of Mikrotik with the Freeradius, we need to add the mikrotik IP and shared secret in clients.conf

Edit  /etc/freeradius/clients.conf

nano /etc/freeradius/clients.conf

and add following lines at bottom

client 101.11.11.255 {
secret          = 12345
shortname       = Mikrotik
}

Note: Change the IP /Secret according to your Mikrotik Network Scheme.


Last but not least, download mikrotik dictionary from

https://wiki.mikrotik.com/wiki/Manual:RADIUS_Client/vendor_dictionary

and copy it in /usr/share/freeradius folder

If freeradius is already running, stop it and restart it.


TESTING USER AUTHENTICATION ON FREERADIUS:

Now stop the free radius server

/etc/init.d/freeradius stop

and start in DEBUG mode so that we can monitor for any errors etc

freeradius -X

Now OPEN another TERMINAL/CONSOLE window and issue following command to TEST USER AUTHENTICATION

radtest zaib zaib localhost 1812 testing123

and you should ACCESS-ACCEPT MESSAGE as below …

root@ubuntu:~#  radtest zaib zaib localhost 1812 testing123

Sending Access-Request of id 38 to 127.0.0.1 port 1812
User-Name = "zaib"
User-Password = "zaib"
NAS-IP-Address = 101.11.11.245
NAS-Port = 1812
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=38, length=39
Mikrotik-Rate-Limit = "1024k/1024k"

mt

Another method

echo "User-Name = zaib, Password = zaib, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123

root@apnaradius:~# echo "User-Name = zaib, Password = zaib, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123
Received response ID 101, code 3, length = 56
Reply-Message = "zaib - Your account has expired. \r\n"

Total approved auths: 0
Total denied auths: 1
Total lost auths: 0

:~) Alhamdolillah


 

MIKROTIK SECTION:

I assumed you already have pppoe server configured and running.

Add Radius Entry as showed in the images below …

nas1

nas2


 

TEST FROM CLIENT WINDOWS PC:

Create pppoe dialer at client end, and test the user ID created in earlier steps.

c1

Once it will be connected, you can see entries in Mikrotik LOG / Active Users Session.
As showed in the image below …

ml1

and dynamic queue of 1mb will also be created (that we added in attributes section in radius/mysql)

queue


DISCONNECT Active USER : COMMAND FROM RADIUS

If you want to disconnect a single active connected user , use following command (many other methods available as well)

echo user-name=zaib | radclient -x 101.11.11.255:1700 disconnect 12345

Result

discon command

dc

disconnect user

Another Method with better approach.

First check active user Accounting Session ID in RADACCT table.

 mysql -uroot -pzaib1234 -s --skip-column-names -e "use radius; select acctsessionid from radacct where username ='zaib' AND acctstoptime is NULL;"

Now issue disconnect command [You may fill up variables with actual values, following is an example only]

echo user-name=$USERNAME,Acct-Session-Id=$ACCTSESID | radclient -x $NAS disconnect $RADSECRET

 


Preventing Simultaneous Use by using simultaneous-Use attribute

To LIMIT USER SIMULTANEOUS SESSION: [command is phpMyadmin base format]

INSERT INTO `radcheck` (`id` ,`username` ,`attribute` ,`op` ,`value` )
VALUES (NULL , 'zaib', 'MD5-Password', ':=', MD5( 'zaib' ) ),
(NULL , 'zaib', 'Simultaneous-Use', ':=', '1');

NOTE: For sim-use i had to disable (comment) the “radutmp” entry in /etc/freeradius/sites-enabled/default .

ACCOUNTING SECTION
SESSION SECTION

Now modify the  /etc/freeradius/sql/mysql/dialup.conf file

nano /etc/freeradius/sql/mysql/dialup.conf

& UNCOMMENT following

# Uncomment simul_count_query to enable simultaneous use checking
simul_count_query = "SELECT COUNT(*) \
FROM ${acct_table1} \
WHERE username = '%{SQL-User-Name}' \
AND acctstoptime IS NULL"

NOTE:
YOU MUST RESTART FREERADIUS SERVER IN ORDER TO TAKE CHANGES EFFECT. SO DO IT.

Result of above attributes:

already


Add Calling-Station-Id attribute to restrict mac CALLED ID

If we want to restrict bind user name with specific mac address, first edit

nano /etc/freeradius/sites-enabled/default

and un comment following attribute “checkval“, Example is below …

checkvalsave and restart radius.

Now login to mysql , select radius database, and use below command to add user, with mac address.

INSERT INTO `radius`.`radcheck` (`id` ,`username` ,`attribute` ,`op` ,`value`)
VALUES (
NULL , 'zaib', 'Calling-Station-Id', ':=', '12:34:56:78:70:00'
);

If user uses different station to connect with this ID he will be rejected as showed in the image below …

phpadmin

 

reject-mac-wrong

 


Add Static IP Address and Pool in radreply group.

To Assign user FIX IP Address, use following …

INSERT INTO radreply ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Framed-IP-Address', '==', '1.2.3.4');

To Assign user IP from POOL, use following …

INSERT INTO radreply ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Framed-Pool', '==', '512k-pool');

 


Adding Expiration Date for user

If you want to Expire the Account after XX days, you can use following

INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Expiration', ':=', '13 Mar 2016');

In above Example User will expires on 13th March, 2016 at 00:00 [Midnight].

If you want to EXPIRE user at some other specific Time, use following format in time

INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Expiration', ':=', '13 Mar 2016 08:00');

ZAIB 🙂 GOT IT


Limit User Total Online time (Access by Period) Started from first login

If you want to start user online time (like in hours) but it should be calculated from first access, then use following.

edit the file /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

and add following under “authorize { section

accessperiod

so that it may look like below …

default

now edit file /etc/freeradius/modules/sqlcounter_expire_on_login

nano /etc/freeradius/modules/sqlcounter_expire_on_login

and add following

sqlcounter accessperiod {
counter-name = Max-Access-Period-Time
check-name = Access-Period
sqlmod-inst = sql
key = User-Name
reset = never
query = "SELECT IF(COUNT(radacctid>=1),(UNIX_TIMESTAMP() - IFNULL(UNIX_TIMESTAMP(AcctStartTime),0)),0) FROM radacct WHERE UserName = '%{%k}' AND AcctSessionTime >= 1 ORDER BY AcctStartTime LIMIT 1"
}

now add user attribute in radchceck table (Following is 1 hour Uptime limit example, and it will start after first login)

INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Access-Period', '=', '3600');

Once the time period is over, user will be disconnected.


 

Limit User Total Online time , Example one hour, which can be used in parts as well.

If we want to allow user one hour which user can use in parts as well, like ten minutes now, then next day he can use rest of his available time.  Use following

edit the file /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

and add following under “authorize { section

Max-All-Session

now edit file /etc/freeradius/modules/sqlcounter_expire_on_login

nano /etc/freeradius/modules/sqlcounter_expire_on_login

and add following

sqlcounter timelimit {
counter-name = Max-All-Session-Time
check-name = Max-All-Session
sqlmod-inst = sql
key = User-Name
reset = never
query = "SELECT SUM(AcctSessionTime) FROM radacct where UserName='%{%k}'"
}

Save and Exit.

Now add user attribute in radchceck table (Following is 1 hour Uptime limit example, which can be used in parts as well no first login applied here)

INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Max-All-Session', ':=', '3600');

QUOTA LIMIT FOR USER with CUSTOM MEANINGFUL REJECT REPLY MESSAGE

To limit user data volume limit (either daily, weekly or monthly) use below code.

edit the file /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

and add following under “authorize { section

totalbytecounter{
reject = 1
}
if(reject){
update reply {
Reply-Message := 'ZAIB-RADIUS-REPLY - You have reached your bandwidth limit'
}
reject
}

now edit file /etc/freeradius/modules/sqlcounter_expire_on_login

nano /etc/freeradius/modules/sqlcounter_expire_on_login

and add following

sqlcounter totalbytecounter {
                counter-name = Mikrotik-Total-Limit
                check-name = Mikrotik-Total-Limit
                reply-name = Mikrotik-Total-Limit
                sqlmod-inst = sql
                key = User-Name
                reset = never
                query = "SELECT ((SUM(AcctInputOctets)+SUM(AcctOutputOctets))) FROM radacct WHERE UserName='%{%k}'"
}

Save and Exit.

Now add user attribute in radchceck table (Following is 1 MB total data limit example, which can be used in parts as well )

Note: Value is in bytes, so use it accordingly

INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES (NULL , 'zaib', 'Mikrotik-Total-Limit', ':=', '1000000');

Once the user quota over, he will get access deny message, and in radius log, you can see following 🙂

radreply

Note:

There is a problem with above attribute. Radius will not AUTO disconnect user once he reaches his limit. he will continue to use his account. he will only be denied further login on his next login attempt.

[later I found that if you will add expiration check in radcheck section, NAS will auto DC the user, no need to disconnect the user manually 🙂 ) Look the next article which have EXPIRATION post.

Following is an workaround for it.

Make the following bash script. It will check for online users, and will check if those users have quota limit using ‘Mikrotik-Total-Limit’ attribute. Then it will check there usage against quota limit. If it will found above quota, it will simply disconnect users, else ignore. You can add this script in crontab to run every X minutes.

#!/bin/bash
#set -x
# HEADER -----------
# SCRIPT to fetch data of active radius users into file, then check there quota limit against there usage.
# if quota is over , disconnect them.
# Syed Jahanzaib / aacable@hotmail.com / https://aacable.wordpress.com
# 17-MAR-2016

# Setting FILE Variables
TMPFILE="/tmp/activeusers"
FINALFILE="/tmp/finalfile"

# Make list of ONLINE USERS using radwho command, very handy 🙂
radwho  | awk '{print $2}' | sed '1d' > $TMPFILE
# if you fail to configure radwho, then use following
# mysql -uroot -pSQLPASS --skip-column-names -e "use radius; SELECT username FROM radacct WHERE acctstoptime IS NULL;" | cut -f1 -d/ 

# Mikrotik NAS Details
NAS="101.11.11.255"
NASPORT="1700"
SECRET="12345"
CURDATE=`date`

# MYSQL user credentials
SQLUSER="root"
SQLPASS="zaib1234"

# Apply Formula to get QUOTA limit data for each user in $FINALFILE (EXCLUDING USER WHO DONT HAVE ANY QUOTA LIMIT USING MIKROTIK-TOTAL-LIMIT ATTRIBUTE)
num=0
cat $TMPFILE | while read users
do
num=$[$num+1]
ACTIVEID=`echo $users | awk '{print $1}'`
mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT username,value FROM radcheck WHERE attribute='Mikrotik-Total-Limit' AND username='$ACTIVEID';" > $FINALFILE
done

# Apply Formula to get username and QUOTA LIMIT from $FINALFILE and check there usage againts assigned quota
num=0
cat $FINALFILE | while read users
do
num=$[$num+1]
username=`echo $users | awk '{print $1}'`
QLIMIT=`echo $users | awk '{print $2}'`
QUSED=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT ((SUM(AcctInputOctets)+SUM(AcctOutputOctets))) FROM radacct WHERE UserName='$username'"`

# PRINT GENERAL INFO
echo "------ $CURDATE"
echo "$username QUOTA LIMIT= $QLIMIT"
echo "$username QUOTA USED= $QUSED"

# IF QUOTA IS ABOVE LIMIT, DISCONNECT USER USING RADCLIENT OR YOU CAN CHANGE THE USER SERVICE AS WELL 🙂 / zaib
if [ $QUSED -gt $QLIMIT ]
then
echo "QUOTA REACHED! Disconnecting $username from NAS $NAS"
echo user-name=$username | radclient -x $NAS:$NASPORT disconnect $SECRET

# ELSE JUST SHOW USER USED DATA WHICH IS IN LIMIT AT A MOMENT / zaib
else
echo "$username quote is under Limit"
echo "------"
fi
done

> $TMPFILE
> $FINALFILE
# SCRIPT END / Syed Jahanzaib

script-quota

Allah Shuker 🙂


BANDWIDTH CHANGE ON THE FLY – CHANGE OF AUTHORITY (COA) _for pppoe_

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

Change the User Name / Rate Limit/ Mikrotik IP  and PORT/SECRET as per network.

echo User-Name := "zaib", Mikrotik-Rate-Limit = 512k/512k | radclient -x 101.11.11.255:1700 coa 12345

cOA


CHANGE BANDWIDTH PACKAGE TO LOWER AFTER DAILY QUOTA REACH

If you want to enforce FUP (fair usage policy) like if 1mb speed allowed user consumed X MB in a day, then his bandwidth package should DROP to lower speed, e.g: 512k for that day.

Add the COUNTER for daily counting

nano /etc/freeradius/modules/sqlcounter_expire_on_login


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

Now add the action for the above counter in sites-available (or enable) file

nano /etc/freeradius/sites-available/default


dailyquota {
reject = 1
}
if (reject) {
ok
update reply {
Mikrotik-Rate-Limit := "512k/512k"
Reply-Message := "You have reached your transfer limit. Limited bandwidth"
}
}

Get Online User Names

mysql -uroot -pSQLPASS --skip-column-names -e "use radius; SELECT username FROM radacct WHERE acctstoptime IS NULL;" | cut -f1 -d/ | sort | uniq -d

Sample of sites-enabled/default file

authorize {
### ZAIB Section-1 Start Here ##
preprocess
chap
mschap
digest
# If user name not found, print error
sql{
notfound = 1
}
if(notfound){
update reply {
Reply-Message = 'Username not found'
}
reject
}

# Check mac, if invalid, then give this user ip from expired-pool
checkval{
reject = 1
}
if(reject){
ok
update reply {
Reply-Message := "Incorrect MAC!"
Framed-Pool := "expired-pool"
Mikrotik-Rate-Limit := "1k/1k"
}
}

# If user is expired by date, then provide him from expired pool
expiration{
userlock = 1
}
if(userlock){
ok
update reply {
Reply-Message := 'Exp-Mod-Reply: Your account has expired.'
Framed-Pool := "expired-pool"
Mikrotik-Rate-Limit := "1k/1k"
}
pap
}
}

authenticate {
Auth-Type PAP {
pap
}
Auth-Type CHAP {
chap
}
Auth-Type MS-CHAP {
mschap
}
digest
unix
}

preacct {
preprocess
acct_unique
suffix
}

accounting {
detail
unix
sql
exec

}
session {
sql
}

### ZAIB Section-2 Start Here ## Default error
post-auth {
exec
Post-Auth-Type REJECT {
update reply {
Reply-Message = 'Wrong Password'
}
sql
attr_filter.access_reject
}
}
### ZAIB Section-2 ENDS Here ##

pre-proxy {
}

post-proxy {
eap
}

USERS file

DEFAULT Auth-Type := PAP

 

Regard’s

Syed Jahanzaib

March 7, 2016

Ubuntu 12 ‘apt-get update not working’ / Failed to Fetch

Filed under: Linux Related — Tags: — Syed Jahanzaib / Pinochio~:) @ 11:12 AM

If you receive following error on running apt-get update on UBUNTU 12.x

As showed in the image below …

ubuntu error

then issue following commands for a quick fix

QUICK FIX Code


sudo rm /var/lib/apt/lists/* -vf

sudo apt-get clean

sudo apt-get autoremove

sudo apt-get dist-upgrade

sudo apt-get update --fix-missing

sudo apt-get update

Done.

apt-get ok


 

Regard’s
Syed Jahanzaib

 

March 4, 2016

Lets manipulate ! Part-1 / Traffic base priority via Queue Tree in Mikrotik

Filed under: Mikrotik Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 2:34 PM

 


manipulateBandwidth Management Fast-Track ! No theory, Just coding …

Requirement:

We want to restrict  client with 128 kbps. BUT also want to prioritize it based on traffic type.

Example …

Client-A IP = 101.11.14.1 (zaib-desktop)

Bandwidth Allowed = 128k Total

Priority 1 = ICMP Traffic

Priority 2 = HTTPS Traffic

Priority 3 = HTTP Traffic

Priority 4 = All Other Traffic


Marking traffic from Client-A in MANGLE

First Mark User Traffic in Mangle Section.


/ip firewall mangle

# Mark HTTP 80Traffic Connections/Packets
add action=mark-connection chain=forward comment="Zaib-Desktop - Mark HTTP Port 80" dst-port=80 new-connection-mark=Zaib_Desktop_http_80_Conn protocol=tcp src-address=101.11.14.1
add action=mark-packet chain=forward connection-mark=Zaib_Desktop_http_80_Conn new-packet-mark=Zaib_Desktop_http_80_pkts passthrough=no 

# Mark HTTPS 443 Traffic Connections/Packets
add action=mark-connection chain=forward comment="Zaib-Desktop - Mark HTTPS Port 443" dst-port=443 new-connection-mark=Zaib_Desktop_https_443_Conn protocol=tcp src-address=101.11.14.1
add action=mark-packet chain=forward connection-mark=Zaib_Desktop_https_443_Conn new-packet-mark=Zaib_Desktop_httsp_443_pkts passthrough=no add action=mark-connection chain=forward comment="Zaib Desktop - ICMP" new-connection-mark=Zaib_Desktop_ICMP_Conn protocol=icmp src-address=101.11.14.1

# Mark ICMP TRAFFIC Connections/Packets 
add action=mark-connection chain=forward comment="Zaib Desktop - ICMP" new-connection-mark=Zaib_Desktop_ICMP_Conn protocol=icmp src-address=101.11.14.1
add action=mark-packet chain=forward connection-mark=Zaib_Desktop_ICMP_Conn new-packet-mark=Zaib_Desktop_ICMP_Pkts passthrough=no 

# Mark ALL OTHER Traffic Connections/Packets 
add action=mark-connection chain=forward comment="Zaib Desktop - All Other Traffic" connection-mark=no-mark new-connection-mark=Zaib_Desktop_All_Other_Traffic src-address=101.11.14.1 
add action=mark-packet chain=forward connection-mark=Zaib_Desktop_All_Other_Traffic new-packet-mark=Zaib_Desktop_All_Other_Pkts passthrough=no 

Creating QUEUE TREE to restrict and Prioritize traffic for above marked packets

Now we we will create Parent Queue Tree to restrict 128k then other child queues to prioritize his traffic base on marked packets

# Mark QUEUE TREE
/queue tree 
add limit-at=128k max-limit=128k name="Zaib Dekstop - 128k" parent=global queue=default

# 1st Priority to ICMP Traffic from above 128k Parent Queue 
add name="PRIO 1 - ICMP" packet-mark=Zaib_Desktop_ICMP_Pkts parent="Zaib Dekstop - 128k" queue=default  priority=1

# 2nd Priority to HTTPS 443 Traffic from 128k Parent Queue
add name="PRIO 2 - HTTPS" packet-mark=Zaib_Desktop_httsp_443_pkts parent="Zaib Dekstop - 128k" queue=default priority=2

# 3rd Priority to HTTP Port 80 Traffic from 128k Parent Queue
add name="PRIO 3 - HTTP" packet-mark=Zaib_Desktop_http_80_pkts parent="Zaib Dekstop - 128k" queue=default  priority=3

# 4th Priority to All Other Traffic from 128k Parent Queue
add name="PRIO 4 - All Other Traffic" packet-mark=Zaib_Desktop_All_Other_Pkts parent="Zaib Dekstop - 128k" queue=default priority=8

RESULT # 1

When ICMP have low priority over other protocols

BEFORE ICMP PRIORITY

1- before prio

 


 

RESULT # 2

When ICMP have high priority over others

***   A F T E R   ***

2- after prio


 

Regard’s
Syed Jahanzaib

%d bloggers like this: