Syed Jahanzaib Personal Blog to Share Knowledge !

January 15, 2018

Mikrotik with Freeradius/mySQL – Trimming & Archiving RADACCT # Part-8

Filed under: freeradius — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 2:40 PM

fre


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

FREERADIUS WITH MIKROTIK – Part #2  – COA

FREERADIUS WITH MIKROTIK – Part #3 – Expiration

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

FREERADIUS WITH MIKROTIK – Part #8  – RADACCT – Trimming & Archiving < You are here


Personnel Note:

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not an ROCKET SCIENCE. 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

OS: Ubuntu 16.04.3 LTS / 64bit


Disclaimer:
There are some other neat-to-Perfect methods like archive users usage data from radacct table to the archive data , per user ONE row only.  This will take much lesser size , but I need to track some data therefore I duplicated the whole table. Else you can use TRIGGER on RADACCT too which can update other user table for the usage only. All depend son the scenario and requirements.



Scenario:

slow data.jpg

Radius accounting is stored in radacct table which contains the user usage related data . Over the period of time this data can take lot of space as time passes. It can also create performance related issues as well if you donot take care of it like slow or timeout queries because of hundreds of thousands of entries.

I received complains from few networks that radius is giving timeout because they were installed few years ago and there radacct table have grown enormously in size thus resulting ins slow queries and timeout in authentication specially in Radius Manager which uses external auth script to validate the user request.

Solution:

  • Use SSD disks (or RAID10), they are more performance oriented storage,
  • Adding more RAM is plus point for any database server.

We will create another table named  radacct_archive  and move accounting data older then 6 months from the radacct table to radacct_archive table.

This way queries will work faster dueto less data in radacct. Also we will delete data older then 12 months from `radacct_archive` table too so that it may not grow large later.

First we will clone the radacct table structure into new radacct_archive table.

# This is one time step.
mysql -uroot -pzaib1234 -s -e "use radius; create table radacct_archive LIKE radacct;"

Now you can create a bash script and schedule it to run DAILY in less busy timings like 5 am in the morning.

# --- Copy data from CURRENT radacct table to new radacct_archive table (for archive purposes)
mysql -uroot -pSQLPASS -s -e "use radius; INSERT INTO radacct_archive SELECT * FROM radacct WHERE acctstoptime > 0 AND date(acctstarttime) < (CURDATE() - INTERVAL 6 MONTH);"

# --- Now Delete data from CURRENT RADACCT table so that it should remain fit and smart ins size
mysql -uroot -pSQLPASS -s -e "use radius; DELETE FROM radacct WHERE acctstoptime > 0 AND date(acctstarttime) < (CURDATE() - INTERVAL 6 MONTH);"

# --- Now Delete data from RADACCT_ARCHIVE table so that it should not grow either more then we required i.e 1 Year - one year archived data is enough IMO
mysql -uroot -pSQLPASS -s -e "use radius; DELETE FROM radacct_archive WHERE date(acctstarttime) < (CURDATE() - INTERVAL 12 MONTH);"

Regard’s
Syed Jahanzaib

Advertisements

January 8, 2018

Mikrotik with Freeradius/mySQL – Quota Limit # Part-7

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

FreeRadius Core info

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

FREERADIUS WITH MIKROTIK – Part #2  – COA

FREERADIUS WITH MIKROTIK – Part #3 – Expiration

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  < You are Here


Personnel Note:

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not an ROCKET SCIENCE. 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

OS: Ubuntu 16.04.3 LTS / 64bit


qt-logo.jpg

Scenario:

Assuming, we have already configured freeradius and user can authenticate & all is working fine. Now we want to provide users with Quota limit. But we donot want to use RADACCT table to calculate there usage & we also don’t want to check there quota on REAL time using freeradius UNLAG query. we will simply cron a script that will run every 10 minutes and will check for quota used for each user from the USERS table. We will make a separate table where we will add the QUOTA user and there limit to avoid re checking for same user. and in USERS table we will simply update there USED data record (in qt_used column in users table) using TRIGGER on radacct table.


USERS table Example.

Where all users information like name/mobile etc will be saved. Also it will contain a column qt_used which we will update dynamically using trigger on radacct table.

----------------------------------------------------------
-- Table structure for table `users`

CREATE TABLE `users` (
`id` int(10) NOT NULL,
`username` varchar(128) NOT NULL,
`password` varchar(32) NOT NULL,
`firstname` text NOT NULL,
`lastname` text NOT NULL,
`email` text NOT NULL,
`mobile` text NOT NULL,
`cnic` text NOT NULL,
`srvname` text NOT NULL,
`srvid` int(3) NOT NULL,
`expiration` date DEFAULT NULL,
`mac` varchar(30) NOT NULL,
`bwpkg` varchar(256) NOT NULL,
`pool` varchar(128) DEFAULT 'other',
`is_enabled` int(1) NOT NULL,
`is_days_expired` int(1) NOT NULL,
`is_qt_expired` int(1) NOT NULL,
`is_uptime_expired` int(1) NOT NULL,
`qt_total` varchar(32) NOT NULL,
`qt_used` varchar(20) NOT NULL,
`uptime_limit` varchar(20) NOT NULL,
`uptime_used` varchar(32) NOT NULL,
`owner` text NOT NULL,
`createdon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `username` (`username`) USING BTREE;

ALTER TABLE `users_qt`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

USERS Quota table Example!

If user is added in this product (username & qoota). then bash script will only check these users against there used quota (qt_used column in USERS table)

------------------------------------------------------------
-- Table structure for table `users_qt`

CREATE TABLE `users_qt` (
`id` int(11) NOT NULL,
`username` varchar(32) NOT NULL,
`quota` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users_qt`
ADD PRIMARY KEY (`id`);

LOG Table Example!

We will use this table to log any disconnect message.

-- Table structure for table `log`
CREATE TABLE `log` (
`id` int(11) NOT NULL,
`data` varchar(32) NOT NULL,
`msg` varchar(128) NOT NULL,
`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `log`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

Trigger for RADACCT

This trigger will simply update the qt_used column for all users (which are enabled, not expired, not over-quota already)

DELIMITER $$
CREATE TRIGGER `radacct_after_update` AFTER UPDATE ON `radacct` FOR EACH ROW BEGIN
#if (NEW.acctterminatecause ='Session-Timeout')
#then
#UPDATE users set is_days_expired ='1' WHERE username =OLD.username;
#UPDATE radusergroup set groupname ='expired' WHERE username =OLD.username;
#ELSE
if (NEW.acctoutputoctets > 1)
OR (NEW.acctinputoctets > 1)
then
UPDATE users set qt_used = qt_used + NEW.acctoutputoctets WHERE is_enabled ='1' AND is_qt_expired ='0' AND is_uptime_expired ='0' AND is_days_expired ='0';
END IF;
#END IF;
END
$$
DELIMITER ;

Now insert a record in users_qt table for a user in this table. (quota limit 300 bytes testing only)

INSERT INTO 'users_qt' ('id', 'username', 'quota') VALUES (NULL, 'zaib', '300');

Now check the record.

root@radius:/temp# mysql -uroot -pPASS -s --skip-column-names -e "use radius; select * from users_qt;"

1 zaib 300

Ok now we simply connect the user and will do some data download or browse , since we have defined very small bytes limit it will fill in seconds.

Now to check the over-quota user we will use a small bash script (which will be cron to run every 10 minutes)


BASH script to check over quota user.

qc_check.sh

root@radius:/temp# cat qc.sh
#!/bin/bash
##set -x
SQLID="root"
SQLPASS="SQLPASS"
export MYSQL_PWD=$SQLPASS
DB="radius"
#Table which contain main users information
TBL="users"
#Table which contains users name which will be scanned for quota
QTDB="users_qt"
#Log table in which few actions will be logged
TBL_LOG="log"
#Rad user group in which we will update user profile like from 1mb to expired or likewise
GROUP="radusergroup"
#What is the Next pool name which will be
NEXTSRV="overquota"
# Temp file where user list will be saved
TMP="/tmp/qt_users.txt"
# NAS IP & port to send COA or disconenct pkt
NAS="10.0.0.1:3799"
# Radius Secret on NAS
RADSECRET="testing123"
CMD="mysql -u$SQLID --skip-column-names -s -e"
$CMD "use $DB; select username from $QTBL;" > $TMP
if [ ! -s $TMP ]
then
echo "No user found to check for QUOTA in table, exit"
exit 1
fi

# Run loop forumla to run CMD for single or multi usernames
num=0
cat $TMP | while read users
do
num=$[$num+1]
USERNAME=`echo $users |awk '{print $1}'`
TOTQT=`$CMD "use $DB; select quota from users_qt where username ='$USERNAME';"`
USEDQT=`$CMD "use $DB; SELECT qt_used FROM $TBL WHERE UserName='$USERNAME';"`

# If quota exceeds then perform multiple actions
if [ "$USEDQT" -gt "$TOTQT" ]; then

# Pull account session id from radacct table, which will be used to send to NAS for user COA OR Disconnection
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
echo "Warning ! $USERNAME user quota reached limits. Allowed = $TOTQT Used = $USEDQT Disconnectig him now by sendin POD to NAS and adding LOG as well"

#Delete user from Quota check table, so it may reapt checking every time for same user
$CMD "use $DB; delete from users_qt where username ='$USERNAME';"

# Update User group to NEXT Service , like expired pool
$CMD "use $DB; update $GROUP set groupname='$NEXTSRV' where username ='$USERNAME';"

# If user is Online , Kick him using session id with RADCLIENT
if [ ! -z "$ACCTSESID" ]; then
echo user-name=$USERNAME,Acct-Session-Id=$ACCTSESID | radclient -x $NAS disconnect $RADSECRET

#Add in LOG table about kicked action
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User Kickd dueto Quota limit exceeded');"
else
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User quota found exceeded, but could not found its online session in Radius');"
fi
fi
done

Done.

Now run the Quota checking script …

root@radius:/temp# ./qc.sh

Warning ! zaib user quota reached limits. Allowed = 300 Used = 400 Disconnectig him now by sending POD to NAS and adding LOG as well

Once script found user Over Quota, it will update the user group in RADGROUPREPLY  so user should get OVER QUOTA pool …

group_updated

Entry in LOG table will be added …

over-qt.JPG

(in above pic, user was not online but he reached the quota, therefore the log informed about this too 🙂 )

Ok lets test user with radclient.

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

RESPONSE:

Received response ID 218, code 2, length = 89
Reply-Message = "Exp-Mod-Reply: Your account has expired."
Framed-Pool = "expired-pool"
Mikrotik-Rate-Limit = "1k/1k"

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

 

Disclaimer:

I might be missing any part, message me if any one required further info in it.

In the end I would like to thanks Mr. Khazoum Yaghi to give me hints for the separate table for quota checking. I was stucked on the script which keep checking for over-quota users again and again but after adding user user quota in separate table, the script now delete user entry from this table, so next time the script will run, it will not check that user because of missing entry in the quota table.

There are some other proper methods as well, but I just managed to achieve my task this way.

Regard’s

Syed Jahanzaib

December 25, 2017

Freeradius External Authentication Script & log reject request only in radpostauth with customized REPLY message

Filed under: freeradius, Linux Related — Syed Jahanzaib / Pinochio~:) @ 11:49 PM

FreeRadius Core info

  1. FREERADIUS WITH MIKROTIK – Part #1
  2. FREERADIUS WITH MIKROTIK – Part #2 
  3. FREERADIUS WITH MIKROTIK – Part #3
  4. FREERADIUS WITH MIKROTIK – Part #4
  5. FREERADIUS WITH MIKROTIK – Part #5
  6. FREERADIUS EXTERNAL AUTH SCRIPT & RADPOSTAUTH – Part #6 >>> YOU ARE HERE

Note: This post is for reference purposes only. This post may change rapidly as at the time of writing , frequent changes are being made to adjust many parameters to fulfill local OP requirements. So you may see some display text difference as compared in the code itself … worry not 🙂 Just read and Enjoy, Pick your desired section.

Also you may want to read this reply came from ALAN (freeradius) when I asked on external auth script.

FreeRadius users mailing list (freeradius-users@lists.freeradius.org)

________________________________
From: Freeradius-Users <freeradius-users-bounces+aacable=hotmail.com@lists.freeradius.org> on behalf of Alan DeKok <aland@deployingradius.com>
Sent: Saturday, January 13, 2018 8:30:02 PM
To: FreeRadius users mailing list
Subject: Re: External Auth Script or local Auth

On Jan 13, 2018, at 8:40 AM, JAHANZAIB SYED <aacable@hotmail.com> wrote:
> I am using Freeradius ver 2.1.10

You should upgrade to 2.2.10. There’s just no reason to use a version which is almost 10 years old

> I have noticed that some commercial radius servers (with freeradius backend) using External script (php/perl or C code) to authenticate users. I just wanted to know that what are the additional benefits of using external auth script over freeradius own authentication (via rad-groups) ?

So that they can avoid GPL licensing issues.

> I made my own bash script that runs fine for authentication by checking user status in my_users table like expiry, disable/enable, quota, uptime etc , but for heavy load network like 20-30k users, what is recommended?

Use the features in FreeRADIUS. They work. They’re also MUCH faster than forking an external program.

i.e. FreeRADIUS can do 10’s of 1000’s of DB queries per second, and 10’s of 1000’s of authentications per second. When you use shell script, that number can drop by 10x to 100x.

On top of that, why re-implement features which already work? It doesn’t make any sense to write a shell script to do something, when FreeRADIUS can already do it.
Most people who are re-packaging FreeRADIUS are figuring this out. Some have taken the source, and hacked it up… at which point they have their own magic server that no one understands.
And a few years later, FreeRADIUS has more / better features than their hacked-up version, and they can no longer sell decent features to their customers.

Alan DeKok.

z@ib

Following is an BASH script which we can use to authenticate users in freeradius. Using external auth script have several benefits over traditional attributes. Example we can provide NAS with radreply based on various attributes in users table like expiration, quota, disabled/expired users & other crazy stuff 🙂

It took many hours for a duffer like me to accomplish this task. Some says BASH is not suitable for servers under heavy load, therefore you may look for C/C++ approach for faster fetching results. I have tested this with 1000 + loop auth requests & it worked fine without putting any load on the server & without any invalid  or missing any single request !

Regard’s
Syed Jahanzaib


Scenario:

  • OS: Ubuntu 12.4 Server Edition
  • FreeRADIUS Version 2.1.10, for host i686-pc-linux-gnu
  • Mysql Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (i686) using readline 6.2

>>> OP Requirements :

  1. If users not found, It should display error message in debug log & REJECT user request
  2. If user password does not match with the radcheck table entry, REJECT the user request
  3. If user is Disabled by OP, let him login with disabled-pool (for redirection purposes)
  4. If user is Expired, let him login with expired-pool (for redirection purposes)
  5. If user is logged in for the first time, his MAC should update in USERS table for AUTO MAC Binding purposes. So only this device should be able to connect in future
  6. If user try to login from other device, let him login with invalidmac-pool (for redirection purposes)
  7. Check for Over Quota users (not required at a moment)
  8. LOG requests in mysql RADPOSTAUTH table for REJECT actions

So in short we donot want to REJECT user login request if it matches above criteria.

Yes I know its a kind of weird stuff, but still its good for learning !

>>> Mysqle ‘USERS’ Table example

In mysql we have following users table [Just an example, its not fine tuned]

#### USERS TABLE
root@testradius:/temp# mysql -uroot -pROOTPASS -e "use radius; describe users;"
+------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(128) | NO | MUL | NULL | |
| password | varchar(32) | NO | | NULL | |
| firstname | text | NO | | NULL | |
| lastname | text | NO | | NULL | |
| email | text | NO | | NULL | |
| mobile | text | NO | | NULL | |
| cnic | text | NO | | NULL | |
| srvname | text | NO | | NULL | |
| srvid | int(11) | NO | | NULL | |
| expiration | datetime | YES | | NULL | |
| mac | varchar(30) | NO | | NULL | |
| bwpkg | varchar(1000) | NO | | NULL | |
| pool | varchar(128) | YES | | other | |
| enabled | varchar(1) | NO | | NULL | |
| owner | text | NO | | NULL | |
| createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+---------------+------+-----+-------------------+-----------------------------+
17 rows in set (0.00 sec)

root@testradius:/temp# mysql -uroot -pROOTPASS -e "use radius; select * from users;"
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+---------------------+-------------------+-------------+----------------------+---------+--------+---------------------+
| id | username | password | firstname | lastname | email | mobile | cnic | srvname | srvid | expiration | mac | bwpkg | pool | enabled | owner | createdon |
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+---------------------+-------------------+-------------+----------------------+---------+--------+---------------------+
| 1 | zaib | | syed | jahanzaib | aacableAThotmail.com | 03333021909 | 1234567890-1-1 | | 0 | 2018-12-23 00:00:00 | 00:0C:29:35:F8:2F | 1024k/1024k | public-pool | 1 | galaxy | 2017-12-23 16:25:09 |
| 7 | test | | test firstname | test lastname | test@test.com | 13434234234 | 242342420424-42-2 | | 0 | 2017-12-24 00:00:00 | 00:0C:29:35:F8:2F | 2048k/2048k | public-multinet-pool | 1 | zaib | 2017-12-25 16:27:39 |
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+---------------------+-------------------+-------------+----------------------+---------+--------+---------------------+

FREERADIUS config to accommodate external authentication script [auth.sh] execution

We need to add following directives in FR config files in order to execute the bash script for auth purpose once the username/password is found ok by FR.


>>> Edit freeradius/USERS file in FR

nano /etc/freeradius/users

& Add following in the end of this file …

DEFAULT Auth-Type := PAP
Exec-Program-Wait = "/temp/auth.sh %{User-Name} %{User-Password} %{Calling-Station-Id}"

SAVE & EXIT !


>>> Now edit sites-enabled/default file …

nano /etc/freeradius/sites-enabled/default

& use following … [my working sample file, you must modify it as per your requirements]

authorize {
preprocess
chap
pap
mschap
digest
# suffix
# eap {
# ok = return
# }
files
### ZAIB Section-1 Start Here ##
sql{
notfound = 1
}
if(notfound){
update reply {
Reply-Message = 'Username not found'
}
reject
}
### ZAIB Section-1 Ends Here ##
# checkval
# expiration
# logintime
# pap
}

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

preacct {
preprocess
acct_unique
suffix
files
}

accounting {
detail
unix
radutmp
sql
exec
attr_filter.accounting_response
}

session {
# radutmp
sql
}

### ZAIB Section-2 Start Here ##
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
}

SAVE & EXIT !


 


AUTH.SH [bash script as external auth for FR]

Create auth.sh file

  • mkdir /temp
  • touch /temp/auth.sh
  • chmod+x /temp/auth.sh
  • nano /temp/auth.sh

& paste following data [after necessary modifications]

#!/bin/bash
# Bash script for Freeradius / external auth script
# By Syed Jahanzaib / aacable at hotmail dot com
# https://aacable.wordpress.com
# 22-DEC-2017
# Last modified on 27-DEC-2017
# You may want to disable few sections in it like user validation, password validation
# because both are now handled by FR with our custom reply message hurrahhhhh zaib.
#set -x
USERNAME=$1
PASS=$2
MAC=$3
MYSQL_USR="root"
MYSQL_PASS="ROOT-OR-SQL-PASS"
DB="radius"
TBL="users"
MSG=""
USRISVALID=`mysql -u$MYSQL_USR -p$MYSQL_PASS --skip-column-names -s -e "use $DB; select id from $TBL where username ='$USERNAME';" | tr "'" " "`
if [ -z "$USRISVALID" ];then
echo "$USERNAME >>>> User Not Found."
exit 1
fi
CHKPASS=`mysql -u$MYSQL_USR -p$MYSQL_PASS --skip-column-names -s -e "use $DB; select password from users where username='$USERNAME';"`
if [ "$PASS" != "$CHKPASS" ];then
echo "$USERNAME / $PASS >>> Incorrect Password."
exit 1
fi
ENORDIS=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select enabled from $TBL where username ='$USERNAME';" | tail -n 1`
if [ "$ENORDIS" == "0" ];then
echo 'Mikrotik-Rate-Limit="'1k/1k'",Framed-Pool="'disabled-pool'",Session-Timeout="'0'"'
exit 0
fi
CHECKMAC=`mysql -u$MYSQL_USR -p$MYSQL_PASS --skip-column-names -s -e "use $DB; select mac from $TBL where username ='$USERNAME';" | tail -n 1`
if [ -z "$CHECKMAC" ];then
mysql -u$MYSQL_USR -p$MYSQL_PASS -e "use $DB; UPDATE $TBL SET mac ='$MAC' WHERE users.username ='$USERNAME' LIMIT 1;"
fi
if [ "$MAC" != "$CHECKMAC" ];then
echo 'Mikrotik-Rate-Limit="'1k/1k'",Framed-Pool="'invalidmac-pool'",Session-Timeout="'0'"'
exit 0
fi
SECONDS=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select time_to_sec(TIMEDIFF (expiration, NOW() ) ) as secs from users where username ='$USERNAME';" | tail -n 1`
if [ "$SECONDS" -lt 0 ]; then
echo 'Mikrotik-Rate-Limit="'1k/1k'",Framed-Pool="'expired-pool'",Session-Timeout="'0'"'
exit 0
fi
POOL=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select pool as pool from $TBL where username ='$USERNAME';" | tail -n 1`
BWPKG=`mysql -u$MYSQL_USR -p$MYSQL_PASS -s -e "use $DB; select bwpkg as bwpkg from $TBL where username ='$USERNAME';" | tail -n 1`
echo 'Framed-Pool="'$POOL'",Session-Timeout="'$SECONDS'",Mikrotik-Rate-Limit="'$BWPKG'",'
exit 0
fi

EXAMPLE:

After all is configured properly, we can see the results as following.

>>> To test with the script it self

root@testradius:/temp# ./auth.sh MR.PONKA PASS 00:0C:29:35:F8:2F
MR.PONKA >>>> User Not Found... *****

root@testradius:/temp# ./auth.sh zaib PASS 00:0C:29:35:F8:2F
Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",

root@testradius:/temp# ./auth.sh zaib PASS 11:11:11:11:11:11
Mikrotik-Rate-Limit="1k/1k",Framed-Pool="invalidmac-pool",Session-Timeout="0"

root@testradius:/temp# ./auth.sh test PASS 00:0C:29:35:F8:2F
Mikrotik-Rate-Limit="1k/1k",Framed-Pool="expired-pool",Session-Timeout="0"

>>> To test from RADCLIENT ,

we can use the following radclient syntax to test username/pass/mac.

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

If user not found

Sending delayed reject for request 2
Sending Access-Reject of id 32 to 127.0.0.1 port 57901
 Reply-Message = "Username not found"
Waking up in 4.9 seconds.

If user is connecting from other device [mac binding scenario]

+- entering group post-auth {...}
Exec-Program output: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="invalidmac-pool",Session-Timeout="0"
Exec-Program-Wait: value-pairs: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="invalidmac-pool",Session-Timeout="0"
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 113 to 127.0.0.1 port 50894
Mikrotik-Rate-Limit = "1k/1k"
Framed-Pool = "invalidmac-pool"
Session-Timeout = 0

If user account is expired…

+- entering group post-auth {...}
Exec-Program output: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="expired-pool",Session-Timeout="0"
Exec-Program-Wait: value-pairs: Mikrotik-Rate-Limit="1k/1k",Framed-Pool="expired-pool",Session-Timeout="0"
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 176 to 127.0.0.1 port 36544
Mikrotik-Rate-Limit = "1k/1k"
Framed-Pool = "expired-pool"
Session-Timeout = 0

& IF all is OK, & account is valid …. then user will be able to connect with his assigned profile present in the users table …

+- entering group post-auth {...}
Exec-Program output: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program-Wait: value-pairs: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 0 to 127.0.0.1 port 37090
Framed-Pool = "public-pool"
Session-Timeout = 3020399
Mikrotik-Rate-Limit = "1024k/1024k"

RADPOSTAUTH mysql query section

Following is RADPOSTAUTH Table to accommodate modified message.

mysql>describe radpostauth;
+--------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | | | |
| pass | varchar(64) | NO | | | |
| mac | varchar(18) | NO | | NULL | |
| nasipaddress | varchar(16) | NO | | NULL | |
| reply | varchar(200) | NO | | | |
| authdate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| reason | varchar(100) | NO | | NULL | |
+--------------+--------------+------+-----+-------------------+-----------------------------+

MYSQL QUERY:

This is mysql query which will be executed on every login attempt. Either success or failed. Replace existing or modify as required.

cat /etc/freeradius/sql/mysql/dialup.conf
postauth_query = "INSERT into ${postauth_table} (username, pass, mac, nasipaddress, reply, authdate, reason) values ('%{User-Name}', '%{User-Password:-Pap-Password}', '%{Calling-Station-Id}', '%{NAS-IP-Address}', '%{reply:Packet-Type}', NOW(), '%{reply:Reply-Message}')"

Now restart your freeradius service in DEBUG mode

freeradius -X

& issue following commands in other terminal window

# echo "User-Name = zaib1, Password = wrongpass, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123
Received response ID 30, code 3, length = 40
Reply-Message = "Username not found"

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

# echo "User-Name = zaib, Password = wrongpass, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123
Received response ID 77, code 3, length = 47
Reply-Message = "Wrong Password"

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

# echo "User-Name = zaib, Password = zaib, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123
Received response ID 121, code 2, length = 58
Framed-Pool = "public-pool"
Session-Timeout = 3020399
Mikrotik-Rate-Limit = "1024k/1024k"

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

Now look at the debug window

& you will get

# debug for USERNAME not found request

# debug for WRONG Username

Sending delayed reject for request 13
Sending Access-Reject of id 30 to 127.0.0.1 port 46089
Reply-Message = "Username not found"

# debug for WRONG Password

Sending Access-Reject of id 77 to 127.0.0.1 port 41764
Reply-Message = "Wrong Password"
Waking up in 4.9 seconds.
Cleaning up request 14 ID 77 with timestamp +666
Ready to process requests.

# debug for SUCCESSFUL request

[pap] login attempt with password "zaib"
[pap] Using clear text password "zaib"
[pap] User authenticated successfully
++[pap] returns ok
# Executing section post-auth from file /etc/freeradius/sites-enabled/default
+- entering group post-auth {...}
Exec-Program output: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program-Wait: value-pairs: Framed-Pool="public-pool",Session-Timeout="3020399",Mikrotik-Rate-Limit="1024k/1024k",
Exec-Program: returned: 0
++[exec] returns ok
Sending Access-Accept of id 121 to 127.0.0.1 port 46954
Framed-Pool = "public-pool"
Session-Timeout = 3020399
Mikrotik-Rate-Limit = "1024k/1024k"
Finished request 15.
Going to the next request
Waking up in 4.9 seconds.
Cleaning up request 15 ID 121 with timestamp +675
Ready to process requests.

Now look @ the radpostauth table


Alhamdolillah !

 

December 15, 2017

Daily report for backup folder via email

Filed under: Linux Related — Tags: — Syed Jahanzaib / Pinochio~:) @ 9:23 AM

This is just for my personnel reference, If you like to use it, just modify whatever is required.

I made this script to get a daily report for support staff so that they can monitor if backup’s are being performed properly & regularly. The backup scheduler make backup after every 2 hours. so there should be 12 backup files in /backup folder per day. This script just query them. I used somewhat some unusual method to acquire the files date, BUT there are other proper methods to get the same , dueto some urgency i just made whatever worked for me. Will update it if any changes will be required.


the Script !

mkdir /temp
touch /temp/bkpreport.sh
chmod +x  /temp/bkpreport.sh
nano /temp/bkpreport.sh

& paste following …


#!/bin/bash
# This bash script is made to send report for files that were created in /backup folder by another scheduler
# I made this to ensure that backups are done properly with automated reports including some other system level information.
# 12-DEC-2017
# Syed Jahanzaib / aacable @ hotmail . com
# https://aacable . wordpress . com
#set -x

#MYSQL Related Details
HOSTNAME=`hostname`
MYSQLID="root"
MYSQLPASS="SQLPASS"
SQL_ACCOUNTING_TABLE="radacct"
GMAILID="YOURGMAIL@gmail.com"
GMAILPASS="GMAILPASS"
ADMINMAIL1="TO1@hotmail.com"
COMPANY="ZAIB Ltd."
#GMAILS MTP
SMTP="64.233.184.108:587"

# Script will search this folder for the files
BKPFOLDER="/backup"
MAILFILE="/tmp/bkpdir.log"
#Set Human Friendly Date Format for yesterday & Display
DATE=`date`
TODAY=`date +"%d-%b-%Y"`
YESTERDAY=`date +"%d-%b-%Y" -d '-1 days'`
MAILSUB="$COMPANY RADIUS Backup Rerport for $YESTERDAY"
# Count number of files
LIST=`ls -lh $BKPFOLDER --time-style=+"%d-%b-%Y" |grep $YESTERDAY`
TOTFILES=`ls -lh $BKPFOLDER --time-style=+"%d-%b-%Y" |grep $YESTERDAY |wc -l`

# disk we want to monitor, make sure to change this
# You need to adjust this , to match your drive, use df -h & modify this
DISK="/dev/mapper/vg_myradius-lv_home"

# Get DB size in MB
MYSQLDBSIZE=`mysql -u$MYSQLID -p$MYSQLPASS --skip-column-names -e "SELECT table_schema "radius", sum(data_length + index_length)/1024/1024 FROM information_schema.TABLES WHERE table_schema='radius' GROUP BY table_schema;" | cut -f1 -d"." | sed 's/[^0-9]*//g'`
DISKTOT=`df -h $DISK |awk '{print $2}'| sed -n 3p`
DISKUSED=`df -h $DISK |awk '{print $3}'| sed -n 3p`
DISKAVA=`df -h $DISK |awk '{print $4}'| sed -n 3p`
DISKUSEPER=`df -h $DISK |awk '{print $5}'| sed -n 3p`
MEMTOT=`free -m |awk '{print $2}'| sed -n 2p`
MEMUSED=`free -m |awk '{print $3}'| sed -n 2p`
MEMAVA=`free -m |awk '{print $4}'| sed -n 2p`
MEMUSEDPER=`free -m | grep Mem | awk '{print $3/$2 * 100.0}'`
MEMAVAPER=`free -m | grep Mem | awk '{print $4/$2 * 100.0}'`
IPADD=`ifconfig | awk -v RS="\n\n" '{ for (i=1; i<=NF; i++) if ($i == "inet" && $(i+1) ~ /^addr:/) address = substr($(i+1), 6); if (address != "127.0.0.1") printf "%s\t%s\n", $1, address }'`
SESSIONS=`mysql -u$MYSQLID -p$MYSQLPASS --skip-column-names -e "use radius; SELECT username FROM $SQL_ACCOUNTING_TABLE WHERE acctstoptime IS NULL;" |wc -l`
FOOTER="This automated report is generated on $DATE
Powered by $COMPANY
Coding by Syed.Jahanzaib / aacable at hotmail dot com"

echo "Daily report for $HOSTNAME System @ COMPANY.

$TOTFILES backup sets were made with intervale of 2 hours on $YESTERDAY

List of files that were backuped $YESTERDAY...

$LIST

OTHER DETAILS: JUST FYI,

===============
NETWORK DETAILS:
===============
Hostname = $HOSTNAME
Network IP Details
$IPADD

==========================
RADIUS SERVER MYSQL REPORT:
==========================
MYSQL 'RADIUS' DB SIZE = $MYSQLDBSIZE MB
RADIUS Online Users = $SESSIONS Users / [at the time of this script running, generaly 00:00 Hours / Midnight]

====================
DIS / STORAGE REPORT:
====================
Total Disk Space = $DISKTOT
Total Disk Space Used = $DISKUSED
Total Disk Space Available = $DISKAVA
Total Disk Space = $DISKUSEPER

==============
MEMORY REPORT:
==============
Total RAM = $MEMTOT MB
Total RAM Used = $MEMUSED MB
Total RAM Available = $MEMAVA MB
Total RAM Used Percent = $MEMUSEDPER %
Total RAM Available Percent = $MEMAVAPER %

$FOOTER" >$MAILFILE

#Display Results in terminal
cat $MAILFILE

# Send results via email to $ADMINMAIL1
sendemail -t $GMAILID -u "$MAILSUB" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$MAILFILE -o message-content-type=text

Sample Email !

Click to enlarge

Regard’s
Syed Jahanzaib

November 27, 2017

Automating Centralized backup for Cisco Switches

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

cisco_backup.JPG

Another day to day troubleshooting short notes:

Disclaimer: There are many other proper techniques to achieve the centralized backup task, like Linux base scripting or FTP base route, But dueto my laziness I made this method according to the already available resources with minimum work required . 


Scenario:

Let’s assume:

Operator have dozen’s of manageable Cisco switches installed at various indoor/outdoor locations. Switches configuration are dynamic & the OP make changes on almost daily basis. For backup of switches configs, OP uses TFTP server on local windows server /& perform backup commands on all switches manually once a month. We want to automate this by having centralized backup server & all switches should run schedule backup task which should upload backup to the TFTP server automatically on daily or weekly basis.


Solution: [for personnel referenec]

1- Install TFTP Server in Windows to receive backup files from devices

Download & install Solarwind free TFTP Server application. Its quite and simple application which can receive backups from your switches/routers/devices and store them in c:\tftp-root folder (default location, you can change it)

tftp.JPG

At Cisco switch issue following command to test if TFTP is working fine. Your switch must be able to ping/communicate with the tftp server. Login tot switch via termina or ssh and issue this command to test the TFTP connectivity from switch to tftp server …

en
show running-config | redirect tftp://10.0.0.1/cisco_sw/sw_10.0.0.20.txt

Note: I create new folder name cisco_sw so that all switches configurations should store in a separate folder for easy identification.

received.JPG


2 – Add Backup Schedule Job / Task on Cisco Switches

Network Details:

  1. TFTP SERVER IN WINDOWS IP : 10.0.0.1
  2. SWITCH IP : 10.0.0.20

Use following commands to add schedule task to run backup daily in midnight

en
conf t
kron policy-list daily-backup
cli show startup-config | redirect tftp://10.0.0.10/cisco_sw/sw_10.0.0.20.txt
exit
kron occurrence daily-backup at 00:00 recurring
policy-list daily-backup
exit
wr

 

  • Verify the kron configuration by using the show command.
show kron schedule
  • Result:
Kron Occurrence Schedule
daily-backup inactive, will run again in 0 days 12:58:04 at 0 :00 on

End Result:

files


Tips:

(for specific day and time, use following:

kron occurrence daily-backup at 23:00 Sun recurring)

To run task every 2 minute

kron occurrence daily-backup in 2 recurring
policy-list daily-backup

Regard’s
~Syed Jahanzaib

November 22, 2017

Error: (30)Read-only file system: apache2

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 9:25 AM

Fix Read Only Error


Problem:

OS Ubuntu 12.4 x86 / Server Edition

Getting error when restarting apache2 service ..

root@radius:~# service apache2 restart
* Restarting web server apache2 (30)Read-only file system: apache2: could not open error log file /var/log/apache2/error.log.
Unable to open logs
Action 'start' failed.
[fail]
The Apache error log may have more information.

Reason:

Could be many depend on related situation. Usually Linux puts your file system in read only when errors occur, especially errors with the disk or the file-system itself, errors like a wrong journal entry for example.

You better check your dmesg for disk related errors.

My personnel observations are these error comes specially on system which have busy mysql and it gets unexpected shutdown, or the system got reboot un unexpectedly. IT also occurs dueto bad or about to get faulty disk / Zaib


Solution:

Run following command

sudo fsck.ext4 -f /dev/sda1

It will start running FSCK, It will ask you either to FIX or not, simply press Y to fix the entries …


e2fsck 1.42 (29-Nov-2011)
/dev/sda1: recovering journal
Pass 1: Checking inodes, blocks, and sizes
Deleted inode 6029328 has zero dtime. Fix<y>? yes

Pass 2: Checking directory structure

Pass 3: Checking directory connectivity
Pass 4: Checking reference counts
Pass 5: Checking group summary information
Free blocks count wrong for group #465 (25232, counted=25231).
Fix<y>? yes

Free blocks count wrong (40843516, counted=40843515).
Fix<y>? yes

Inode bitmap differences: -6029328
Fix<y>? yes

Free inodes count wrong for group #736 (8184, counted=8185).
Fix<y>? yes

Free inodes count wrong (10409101, counted=10409102).
Fix<y>? yes

/dev/sda1: *** FILE SYSTEM WAS MODIFIED ***
/dev/sda1: *** REBOOT LINUX ***
/dev/sda1: 68466/10477568 files (0.1% non-contiguous), 1045765/41889280 blocks

Once its done, it will ask you to reboot, simply reboot the system.

root@radius:~# shutdown -r now

After the system reboots, it may ask you to fix more , simply press Y to proceed.
once all done, hopefully the system will be booted in normal mode.

 

Note: If you want to force your root filesystem to remount as rw, you can do the following. (It works sometimes in rare cases.

mount -o remount,rw /

Regard’s
Syed Jahanzaib

November 10, 2017

Centralized Syslog-ng logging to MySql DB

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

configure-centralized-syslog-server-2.jpg


Part # 1 – Howto Save Mikrotik/Cisco Logs to Remote SYSLOG Server

Part # 2 – Centralized Syslog-ng logging to MySql DB<< You are here

 


In continuation to existing post related to syslog-ng, Following post illustrates how you can push syslog logs entries to mysql DB for easy access and search functions.

We all know that if you have dozen’s of switches / routers / Linux systems to manage, its not an easy task to look at each device’s log for inspection and health check. This is surely an Daunting task for any administrator or support personnel. Recently I was facing some difficulty in troubleshooting remote switch. therefore I made an syslog server and made all switches/routers info logging to this syslog which then put a copy of log in mysql DB as well as in local file too.

This post is not made for likes, dislikes or sharing purposes. Its just simple knowledge sharing on how I managed to achieve the task that looks difficult in the beginning but actually was easy when it got deployed finally.

First make sure you have an working syslog-ng installation. for more information look at syslog-ng part#1

Once you have working syslog-ng, then use the following sample /etc/syslog-ng/syslog-ng.conf


OS : Ubuntu 14 / 64bit
Syslog-NG:  3.5.3 [using default apt-get install package]


syslog-ng sample file

Note: Make sure to modify/add/remove entries as  per your requirements.

@version: 3.5
@include "scl.conf"
@include "`scl-root`/system/tty10.conf"
options { chain_hostnames(off); flush_lines(0); use_dns(no); use_fqdn(no);
owner("root"); group("adm"); perm(0640); stats_freq(0);
bad_hostname("^gconfd$");
};
source s_src {
system();
internal();
};
destination d_auth { file("/var/log/auth.log"); };
destination d_cron { file("/var/log/cron.log"); };
destination d_daemon { file("/var/log/daemon.log"); };
destination d_kern { file("/var/log/kern.log"); };
destination d_lpr { file("/var/log/lpr.log"); };
destination d_mail { file("/var/log/mail.log"); };
destination d_syslog { file("/var/log/syslog"); };
destination d_user { file("/var/log/user.log"); };
destination d_uucp { file("/var/log/uucp.log"); };
destination d_mailinfo { file("/var/log/mail.info"); };
destination d_mailwarn { file("/var/log/mail.warn"); };
destination d_mailerr { file("/var/log/mail.err"); };
destination d_newscrit { file("/var/log/news/news.crit"); };
destination d_newserr { file("/var/log/news/news.err"); };
destination d_newsnotice { file("/var/log/news/news.notice"); };
destination d_debug { file("/var/log/debug"); };
destination d_error { file("/var/log/error"); };
destination d_messages { file("/var/log/messages"); };
destination d_console { usertty("root"); };
destination d_console_all { file(`tty10`); };
destination d_xconsole { pipe("/dev/xconsole"); };
destination d_ppp { file("/var/log/ppp.log"); };
filter f_dbg { level(debug); };
filter f_info { level(info); };
filter f_notice { level(notice); };
filter f_warn { level(warn); };
filter f_err { level(err); };
filter f_crit { level(crit .. emerg); };
filter f_debug { level(debug) and not facility(auth, authpriv, news, mail); };
filter f_error { level(err .. emerg) ; };
filter f_messages { level(info,notice,warn) and
not facility(auth,authpriv,cron,daemon,mail,news); };
filter f_auth { facility(auth, authpriv) and not filter(f_debug); };
filter f_cron { facility(cron) and not filter(f_debug); };
filter f_daemon { facility(daemon) and not filter(f_debug); };
filter f_kern { facility(kern) and not filter(f_debug); };
filter f_lpr { facility(lpr) and not filter(f_debug); };
filter f_local { facility(local0, local1, local3, local4, local5,
local6, local7) and not filter(f_debug); };
filter f_mail { facility(mail) and not filter(f_debug); };
filter f_news { facility(news) and not filter(f_debug); };
filter f_syslog3 { not facility(auth, authpriv, mail) and not filter(f_debug); };
filter f_user { facility(user) and not filter(f_debug); };
filter f_uucp { facility(uucp) and not filter(f_debug); };
filter f_cnews { level(notice, err, crit) and facility(news); };
filter f_cother { level(debug, info, notice, warn) or facility(daemon, mail); };
filter f_ppp { facility(local2) and not filter(f_debug); };
filter f_console { level(warn .. emerg); };
log { source(s_src); filter(f_auth); destination(d_auth); };
log { source(s_src); filter(f_cron); destination(d_cron); };
log { source(s_src); filter(f_daemon); destination(d_daemon); };
log { source(s_src); filter(f_kern); destination(d_kern); };
log { source(s_src); filter(f_lpr); destination(d_lpr); };
log { source(s_src); filter(f_syslog3); destination(d_syslog); };
log { source(s_src); filter(f_user); destination(d_user); };
log { source(s_src); filter(f_uucp); destination(d_uucp); };
log { source(s_src); filter(f_mail); destination(d_mail); };
log { source(s_src); filter(f_news); filter(f_crit); destination(d_newscrit); };
log { source(s_src); filter(f_news); filter(f_err); destination(d_newserr); };
log { source(s_src); filter(f_news); filter(f_notice); destination(d_newsnotice); };
log { source(s_src); filter(f_debug); destination(d_debug); };
log { source(s_src); filter(f_error); destination(d_error); };
log { source(s_src); filter(f_messages); destination(d_messages); };
log { source(s_src); filter(f_console); destination(d_console_all);
destination(d_xconsole); };
log { source(s_src); filter(f_crit); destination(d_console); };
@include "/etc/syslog-ng/conf.d/*.conf"
source s_net { udp (); };

# Add Mikrotik 1 Host
filter f_mikrotik { host( "10.0.0.1" ); };
# Add 2nd Mikrotik 2 Host
filter f_mikrotik2 { host( "10.0.0.2" ); };
# Add Cisco Switch
filter f_ciscoswnoc { host( "10.0.0.5" ); };
# Add 2nd Cisco Switch
filter f_ciscosw2 { host( "10.0.0.4" ); };
# Define local files where log for this specific host will be created
destination d_mikrotik { file("/var/log/mikrotik/$HOST.mikrotik.${YEAR}.${MONTH}.${DAY}.log"); };
# Define local files where log for this specific host will be created
destination d_mikrotik2 {
file("/var/log/mikrotik/$HOST.mikrotik.${YEAR}.${MONTH}.${DAY}.log"); };
# Define local files where log for this specific host will be created
destination d_ciscoswnoc { file("/var/log/mikrotik/$HOST.ciscosw.${YEAR}.${MONTH}.${DAY}.log"); };
# Define local files where log for this specific host will be created
destination d_ciscosw2 { file("/var/log/mikrotik/$HOST.ciscosw.${YEAR}.${MONTH}.${DAY}.log"); };

# Define Action to Log file 'from - to '
log { source(s_net); filter(f_mikrotik); destination(d_mikrotik); };
log { source(s_net); filter(f_mikrotik2); destination(d_mikrotik2); };
log { source(s_net); filter(f_ciscoswnoc); destination(d_ciscoswnoc); };
log { source(s_net); filter(f_ciscosw2); destination(d_ciscosw2); };

# Define mysql
source s_mysql {
udp(port(514));
tcp(port(514));
};
destination d_mysql {
sql(type(mysql)
host("localhost")
username("root")
password("MYSQL_PASSWORD")
database("syslog")
table("logs")
columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg")
values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")
indexes("datetime", "host", "program", "msg")
);
};

# Define logging to mysql , like from - to mysql
log {
source(s_net);
filter(f_mikrotik);
destination(d_mysql);
};

# Define logging to mysql , like from - to mysql
log {
source(s_net);
filter(f_ciscoswnoc);
destination(d_mysql);
};

# Define logging to mysql , like from - to mysql
log {
source(s_net);
filter(f_ciscosw2);
destination(d_mysql);
};
# Syed Jahanzaib / aacable at hotmail dot com

Save & Exit.


MYSQL DB to store syslog-ng logs

Create DB in mysql where are logs will be stored.

LOGIN to mysql & create DB

mysql-uroot -pMYSQL_OR_ROOT_PASS
#Create DB / Syed Jahanzaib
create syslog;
USE 'syslog';

#Create tables in syslog db
CREATE TABLE `logs` (
`host` varchar(32) DEFAULT NULL,
`facility` varchar(10) DEFAULT NULL,
`priority` varchar(10) DEFAULT NULL,
`level` varchar(10) DEFAULT NULL,
`tag` varchar(10) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`program` varchar(15) DEFAULT NULL,
`msg` text,
`seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`seq`),
KEY `host` (`host`),
KEY `program` (`program`),
KEY `datetime` (`datetime`),
KEY `priority` (`priority`),
KEY `facility` (`facility`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
exit;

Bash script to make mysql mysql.pipe

Create a file name mysql-syslog.sh and make sure its start with system startup

mkdir /temp
touch /temp/mysql-2-syslog.sh
chmod +x /temp/mysql-2-syslog.sh
nano /temp/mysql-2-syslog.sh
#!/bin/bash
if [ ! -e /var/log/mysql.pipe ]
then
mkfifo /var/log/mysql.pipe
fi
while [ -e /var/log/mysql.pipe ]
do
mysql -u root --password=MYSQL_OR_ROOT_PASSWORD syslog < /var/log/mysql.pipe >/dev/null
done

Save & Exit.

Add it in startup like /etc/rc.local (before exit line)
or run manual like mysql-syslog.sh &

Now perform any activity @ mikrotik like open new terminal or login to winbox, or plug-unplug any cable from Cisco switch, and then issue following command from mysql cli (or use phpmyadmin)


Fetch information from MYSQL

Now get info from table

mysql> select * from logs;
+---------------+----------+----------+--------+------+---------------------+-----------------+----------------------------------------------------------------------------------------------------------------+-----+
| host | facility | priority | level | tag | datetime | program | msg | seq |
+---------------+----------+----------+--------+------+---------------------+-----------------+----------------------------------------------------------------------------------------------------------------+-----+
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:23:04 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 1 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 2 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 3 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 4 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 5 |
| 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:25:09 | 050112 | .Nov 9 10:24:04: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to up | 6 |
| 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:25:09 | 050113 | .Nov 9 10:24:05: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to up | 7 |
| 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:25:28 | 050114 | .Nov 9 10:24:23: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to down | 8 |
| 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:25:29 | 050115 | .Nov 9 10:24:24: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to down | 9 |
| 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:26:28 | 050116 | .Nov 9 10:25:23: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to up | 10 |
| 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:26:28 | 050117 | .Nov 9 10:25:24: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to up | 11 |
| 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:27:20 | 050118 | .Nov 9 10:26:15: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to down | 12 |
| 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:27:21 | 050119 | .Nov 9 10:26:16: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to down | 13 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:27 | system,info,acc | user admin logged in from 101.11.11.161 via winbox | 15 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 16 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 17 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 18 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 19 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 20 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 |
| 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 11:15:52 | 050120 | .Nov 9 11:14:47: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/12, changed state to down | 22 |
| 10.0.0.3 | local7 | err | err | bb | 2017-11-10 11:15:52 | 050121 | .Nov 9 11:14:48: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/12, changed state to down | 23 |
+---------------+----------+----------+--------+------+---------------------+-----------------+----------------------------------------------------------------------------------------------------------------+-----+
23 rows in set (0.00 sec)
  • Some examples of logs fetching command

Show all log files
select * from logs;

OR to look for speicific HOST
select * from logs where host='10.0.0.1';

mysql> select * from logs where host='10.0.0.1';
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| host | facility | priority | level | tag | datetime | program | msg | seq |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:23:04 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 1 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 2 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 3 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 4 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 5 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:27 | system,info,acc | user admin logged in from 101.11.11.161 via winbox | 15 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 16 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 17 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 18 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 19 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 20 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
13 rows in set (0.00 sec)

OR to look for speicific HOST between specific dates
select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:00:26' and '2017-11-10 10:50:26' ;

mysql> select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:40:00' and '2017-11-10 10:50:26' ;
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| host | facility | priority | level | tag | datetime | program | msg | seq |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:27 | system,info,acc | user admin logged in from 101.11.11.161 via winbox | 15 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 16 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 17 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 18 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 19 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 20 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
8 rows in set (0.00 sec)

Or look for particular error on Mikrotik log for incorrect login in specific date time range
select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:40:00' and '2017-11-10 10:50:26' and program='system,error,cr';

mysql> select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:40:00' and '2017-11-10 10:50:26' and program='system,error,cr';
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| host | facility | priority | level | tag | datetime | program | msg | seq |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 |
| 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 |
+--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+
2 rows in set (0.00 sec)

 


– Simple PHP page to show results from mysql table

Following is a php page I made to fetch results from the mysql table and display it in browser.

<?php
// by Syed Jahanzaib
$host="localhost";
$username="root";
$password="PASSWORD"; // Mysql password
$db_name="syslog"; // Database name
$tbl_name="logs"; // Table name
// Connect to server and select databse
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
//$sql="SELECT * FROM $tbl_name ORDER BY `LOGS` datetime LIMIT 0 , 10";
$sql = "SELECT * FROM `logs`\n"
. "ORDER BY `logs`.`datetime` DESC LIMIT 0, 50 ";
$result=mysql_query($sql);
// Define $host_column=1
echo '
<table width="1400" border="1" align="left" cellpadding="1" cellspacing="1">';
echo '
<tr>
<th>ID</th>
<th>Host</th>
<th>Date</th>
<th>Info</th>
<th>Type</th>
<th>Messgae</th>
</tr>
';
while($rows=mysql_fetch_array($result)){
//$host_column assign here from result
$host_column=$rows['host'];
$priority_column=$rows['priority'];
if($priority_column=='err'){
echo "
<tr bgcolor='#FFA07A'>
<td>".$rows['seq']."</td>
<td>".$rows['host']."</td>
<td>".$rows['datetime']."</td>
<td>".$rows['priority']."</td>
<td>".$rows['program']."</td>
<td>".$rows['msg']."</td>
</tr>
";
}else if($host_column=='101.11.11.36'){
echo "
<tr bgcolor='#bbbbbb'>
<td>".$rows['seq']."</td>
<td>".$rows['host']."</td>
<td>".$rows['datetime']."</td>
<td>".$rows['priority']."</td>
<td>".$rows['program']."</td>
<td>".$rows['msg']."</td>
</tr>
";
}else if($host_column=='101.11.12.225'){
echo "
<tr bgcolor='#cccccc'>
<td>".$rows['seq']."</td>
<td>".$rows['host']."</td>
<td>".$rows['datetime']."</td>
<td>".$rows['priority']."</td>
<td>".$rows['program']."</td>
<td>".$rows['msg']."</td>
</tr>
";
}
}
echo '</table>
';
mysql_close();
?>

resulkt


– Another PHP / Mysql & AJAX base example

to show record with pagination , Date selection and Live Search button option.

http://PHP MYSQL AJAX – WITH PAGINATION / DATE / SEARCH OPTION – BY SYED JAHANZAIB

Sample Output

sample


TIP

Query log from multiple tables in syslog DB

Sent by by Mr. Faryad

Q:
For example if your SYSLOG creates daily date wise table on daily basis then how we will write query to search in multiple tables?

In this case simple one line query will not work. For Example you saves 3 days data in 3 tables i.e. `2017-11-10`, `2017-11-11` and `2017-11-12`, and you need to search in these 3 tables, in this case you should need UNION method of MYSQL. I am giving little code here:

select * from `2017-11-10` where host=’10.0.0.1′ AND datetime between ‘2017-11-10 10:00:26’ and ‘2017-11-10 10:50:26′ UNION
select * from `2017-11-11` where host=’10.0.0.1’ AND datetime between ‘2017-11-11 10:00:26’ and ‘2017-11-11 10:50:26′ UNION
select * from `2017-11-12` where host=’10.0.0.1’ AND datetime between ‘2017-11-12 10:00:26’ and ‘2017-11-12 10:50:26’ ;

this multi line query will fetch records from 3 tables just like one line query.


Regard’s
Syed Jahanzaib


dua.jpg

November 8, 2017

TikTik – Script to disconnect hotspot user if its already active in pppoe

Filed under: Mikrotik Related — Syed Jahanzaib / Pinochio~:) @ 1:27 PM

its a weird world we live in !

Fix the root cause that is making issue , dont always go for workarounds

WORKAROUND :

Add this in in Hotspot > User Profile > Default > Scripts > On Login)

# Check if this hotspot user is already logged in on PPPOE on same mikrotik, then kick HOTSPOT
:local uname $user;
:local u;
:foreach u in=[/ppp active find name=$user ] do={
:log warning "$user ID is already active in pppoe. Now disconnecting from HotSpot ... Done!"
:foreach i in=[/ip hotspot active find user=$uname] do= {
/ip hotspot active remove numbers=$i;
}
}

123

.

With some modification you can add script in pppoe login profile as well, which will check if user is already active in hotspot then kick pppoe or hs user.

regard’s
J.

November 1, 2017

Mikrotik with Freeradius/mySQL – Dealing with STALE sessions in FR – Part 5

Filed under: freeradius — Tags: , — Syed Jahanzaib / Pinochio~:) @ 1:26 PM

~ Dealing with STALE session in Freeradius 2.x ~
! From the CORE of FREERADIUS !
By
Syed jahanzaib

FREERADIUS WITH MIKROTIK – Part #1

FREERADIUS WITH MIKROTIK – Part #2 

FREERADIUS WITH MIKROTIK – Part #3

FREERADIUS WITH MIKROTIK – Part #4

FREERADIUS WITH MIKROTIK – Part #5 > You are here

Part-6


Scenario:

Mikrotik as acting as NAS (pppoe server) along with Freeradius as AAA. in the event of light or hardware failure or in a situation where NAS cannot update the FR about user is being disconnected & not active any more, the FR will consider user ACTIVE in radacct table, therefore on next dialup attempt by the user (once every thing is restored), he will get access denied because

  1. There is Simltanous-Use attribute to prevent multiple login from same user
  2. user accctsoptime is NULL because FR have not receive any update from the NAS about user is not online any more.

To remove such stale sessions, there are various methods, you can make your own bash script & schedule it to run every x minutes (example every 5 minutes). Or you can use IF query in authorize session so that when user tries to reconnect & his sessions have NULL  then in this case the query should put stop entry in acctstoptime and allow user new login. or make a PHP program that can be scheuled to run every 5 minutes and then query the radacct session for users whose account update have not received from the NAS.

First you need to add lastupdate column in your RADACCT table (in radius db) . Infact all solutions posted in this guide relies on it. so add it

ALTER TABLE 'radacct' ADD 'lastupdate' TIMESTAMP NOT NULL AFTER 'xascendsessionsvrkey';

Solution#1 [Using PHP program, good if u want to encrypt your code, ELSE bash is good]

 

Create a PHP file name fr_stale.php in your /var/www (for ubuntu)

/var/www/fr_stale.php

<?php
date_default_timezone_set('Asia/Karachi');
include ("/var/www/db.php");
$check_stale = mysqli_query($db,"UPDATE `radacct` SET acctstoptime = NOW() WHERE acctstoptime IS NULL AND lastupdate < DATE_SUB(NOW(), INTERVAL 1 MINUTE)");
?>

Save & exit.

Create a database details file that will be called by above php file in order to get DB connection details.

/var/www/db.php

<?php
$db_username = 'radius';
$db_password = 'PASSWORD';
$db_name = 'radius';
$db_host = '127.0.0.1';
$db=mysqli_connect($db_host, $db_name, $db_password, $db_name);
$mysqli = new mysqli($db_host, $db_username, $db_password, $db_name);
// If unable to communicate with MYSQL, print ERROR
if ($mysqli->connect_error) {
die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
?>>

Schedule it in CRON

*/5 * * * * /usr/bin/php  /var/www/fr_stale.php

Solution # 3
Using BASH in CRON

You can use following bash script to run every 5 minutes which will check for any stale session by matching last update time with current.

mysql -uroot -pSQLPASS -s --skip-column-names -e "use radius; UPDATE radacct SET acctstoptime = NOW() WHERE acctstoptime IS NULL AND lastupdate < DATE_SUB(NOW(), INTERVAL 2 MINUTE)";

Solution#3
This will update the radacct acctstoptime only if user will try to re-connect, tested it in old times]

Edit /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

in AUTHORIZE { section add following query

if (User-Name){
if("%{sql:UPDATE radacct set AcctStopTime=ADDDATE(AcctStartTime,INTERVAL AcctSessionTime SECOND), AcctTerminateCause='Clear-Stale Session' WHERE UserName='%{User-Name}' and CallingStationId='%{Calling-Station-Id}' and AcctStopTime is NULL}"){
}
}

Save & restart freeradius server.

This way if NAS goes out, the session will still show online in radacct table, but when user will relogin next time, his session on radacct table will update and new entry will be created.

 

October 31, 2017

Mikrotik with Freeradius/mySQL – Auto MAC Binding on 1st Login – Part 4

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

mac_auth_radius_mysql

~ Auto Mac Binding via EXEC / PHP in Freeradius 2.x ~
! From the CORE of FREERADIUS !
By
Syed jahanzaib

FREERADIUS WITH MIKROTIK – Part #1

FREERADIUS WITH MIKROTIK – Part #2 

FREERADIUS WITH MIKROTIK – Part #3

FREERADIUS WITH MIKROTIK – Part #4 > You are here 

There are others parts too, look at part-1 for listing, i will update only part-1 listing


Personnel Note:

[At end of this guide I used TRIGGER method to auto insert the mac address of user if there is no mac entry in the radcheck table for his username, Trigger are more efficient method in my opinion.]

This post is just for demonstration purposes. in production environment you should make your own module and add it in proper relevant places. This post contains just minimalist working config to begin with. Make sure to refine it in prd environment.

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not ROCKET SCIENCE. The only thing required is the ultimate passion to achieve the goal & with the proper googling , reading a LOT, understand logic’s, then you can do all on your own. Just wanted to break the image that most of professionals don’t like to share there knowledge. I strongly encourage to read the FR mailing list and Google


OP Requirements:

[ Sort of Wired one 😉 ]

We have a working Freeradius installation. All users can login to mikrotik which verify user account authentication requests via this radius. All working fine. Now OP wants to add Auto MAC binding feature so that when user first time login to NAS, his MAC should auto binds with his account, so next time if he tries to login from another workstation, he must get access denied.


Components used in this guide:

  • Ubuntu 12.4 / x86
  • Freeradius 2.1.10 [Default apt-get installation]
  • MySQL 5.5.47 [Default apt-get installation]

SOLUTION:

To fulfill such weird requirements, we have to use external program example PHP program (via exec) which will be executed when user gets connect successfully. It will then look in RADCHECK table for this specific user MAC address value name “Calling-Station-Id”. If it’s unable to find it, then it will add the entry so that next time user will login his MAC will be verified by the CHECKVAL module in freeradius to match the mac address. If there is mac address entry, it will simply ignore and process further , will also print message that “MAC Entry already found – z@iB”

First enable the CHECKVAL module in following file > /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

Search & uncomment the checkval module. Save & Exit.

Now edit EXEC module file by

nano /etc/freeradius/modules/exec

Remove all previous contents (if its lab testing otherwise be careful editing this file) & paste following

exec {
wait = yes
program = "/usr/bin/php /temp/checkmac.php %{User-Name} %{Calling-Station-Id}"
input_pairs = request
}

Save & Exit.

Now create the php program which will be executed by above module.

mkdir /temp
mkdir /temp/checkmac.php
touch /temp/checkmac.php
nano /temp/checkmac.php

and use following to paste make sure to modify relevant details …

>

checkmac.php contents

<?php
// PHP page to check if MAC is not aleady there for the user, then INSERT it for MAC VALIDATION,
// it will add mac for 1st time login user only
// Syed Jahanzaib / aacable at hotmail dot com
// https://aacable . wordpress . com
// 31-OCT-2017

$link = mysql_connect('localhost', 'root', 'MYSQL-ROOT-PASSWORD');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
// Default DB is radius
mysql_select_db('radius');
// Look for MAC entry for this user
$result=mysql_query("select * FROM radcheck WHERE `UserName`='$argv[1]' AND attribute='Calling-Station-Id' order by Username limit 1");
$val = mysql_num_rows($result);
if ($val > 0) {
printf ("MAC Entry already found by ZAiBBBBBBBBBBBBBBBB");
}
else {
printf ("Seems to be New User, adding its MAC address in table ...");
mysql_query("INSERT into radcheck (UserName, Attribute, op, Value) values ('$argv[1]', 'Calling-Station-Id', ':=', '$argv[2]')");
}
?>

TESTING ….

Start FR in debug mode by freeradius -X and try to login with the test ID from your workstation (or use the radtest or ntradping)


rad_recv: Access-Request packet from host 192.168.0.1 port 42449, id=45, length=188
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 15728851
NAS-Port-Type = Ethernet
User-Name = "zaib"
Calling-Station-Id = "0C:84:DC:1E:0B:8D"
Called-Station-Id = "service1"
NAS-Port-Id = "ether10"
MS-CHAP-Challenge = 0x49c4549501e07fad5e6dae708bc815ed
MS-CHAP2-Response = 0x0100acaa712e29adad9abb681c5ef666e69300000000000000003cd5a092d7c816de798b7f5d09acba6f04eeed208cd6c19b
NAS-Identifier = "MIKROTIK"
NAS-IP-Address = 192.168.0.1
# Executing section authorize from file /etc/freeradius/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
[mschap] Found MS-CHAP attributes. Setting 'Auth-Type = mschap'
++[mschap] returns ok
++[digest] returns noop
[suffix] No '@' in User-Name = "zaib", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
[exec] expand: %{User-Name} -> zaib
[exec] expand: %{Calling-Station-Id} -> 0C:84:DC:1E:0B:8D
Exec-Program output: Seems to be New User, adding its MAC address in table ...
Exec-Program-Wait: plaintext: Seems to be New User, adding its MAC address in table ...
Exec-Program: returned: 0
++[exec] returns ok

As you CAN SEE

“Exec-Program output: Seems to be New User, adding its MAC address in table …”

Now see the difference …

RADCHECK TABLE, Before Login …

1- before login

RADCHECK TABLE, After Login …

2- after login ok

When user will login again, radcheck table will be searched, if the mac found it will simply skip the add part and print the statement

[exec] expand: %{User-Name} -> zaib
[exec] expand: %{Calling-Station-Id} -> 0C:84:DC:1E:0B:8D
Exec-Program output: MAC Entry already found
Exec-Program-Wait: plaintext: MAC Entry already found
Exec-Program: returned: 0
++[exec] returns ok

& If the user will login from any other mac/workstation, he will be denied access.


Method #2
Trigger approach to add MAC address
🙂 ~ ZAIB

Use following TRIGGER on radacct table. It will add the MAC address for the user in RADCHECK table. (or you can modify it as well)

--
-- Triggers `radacct`
--
DELIMITER $$
CREATE TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct` FOR EACH ROW BEGIN
SET @mac = (SELECT count(*) from radcheck where username=New.username and attribute='Calling-Station-ID');
IF (@mac = 0) THEN
INSERT into radcheck (username,attribute,op,value) values (NEW.username,'Calling-Station-ID',':=',NEW.callingstationid);
UPDATE users SET mac = NEW.callingstationid where username = NEW.username;
END IF;
END
$$
DELIMITER ;

trigger for mac add.JPG


Regard’s
Syed Jahanzaib ~

Older Posts »

%d bloggers like this: