Syed Jahanzaib Personal Blog to Share Knowledge !

July 11, 2018

June 26, 2018

BASH Script with time & single execution per day checks

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

 

repeat

Scenario:

We have a bash script that performs various functions related to Freeradius, including expiration checkup & various groups updates functions.

Problem:

We have schedules this script to run daily at 1700 hours, What will happen if server was powered off  at 1700 hours? Script will missed its schedule resulting in expired users account will not be disabled for that day and incorrect display of users status in front gui. Alternatively we can schedule it to run hourly but it will increase the server load because of repeated task , which is supposed to be executed only once per day

Requirements:

We need to make some checks and balances so that if script must have some intelligent IF ELSE criteria in order to check following steps …

  • Script is scheduled to run hourly, so that if it misses the 1700 hour it will be relaunched next hour,
  • If time is 1700 hours or above then execute the script,
  • If the script executes successfully , then save this result, and on next run (within current date) it should detect last run status and dont repeat the code to avid recurrence,
  • If the last run was not executed for any reason, & the time is 1700 or above then execute the script and save its run status in file,
  • Once the data changes, it should re-run the script only if the time is 1700 or above.

Solution:

the SCRIPT ! Sample Purpose only

#!/bin/bash
# Bash Script to make sure script runs at specific time, and should not run again for the same date
# record date in local file, to avoic repeating running the code in todays date, once the date is changed,
# then re-run the code and match time again and so on
# Syed Jahanzaib / 26-Jun-2018
# set -x

# Setting variables
DATE=$(date +%d-%m-%Y)
FULL_DATE=`date`
FILE=/temp/1.txt
touch $FILE
CURR_HOUR=$(date +%H)

# Set time for script execution
SCR_SCHEDULED_TIME="11"
H=$(date +'%-H')

CHK_GREP=`grep -c $DATE $FILE`
echo "Current Date time is $FULL_DATE"

# If script is executed successfully then dont re-run and exit now
if grep -q $DATE $FILE >/dev/null 2>&1
then
echo "It seems the script was executed successfully today $DATE, It will run on next date change.... Exiting now."
exit 1
fi

# Check if time is matched that is greater or equals to $SCR_SCHEDULED_TIME and also check if script hae ran successfully or not previously
echo "
Stage-1: Checking if current time is equals or greater then '$SCR_SCHEDULED_TIME hours' ..."
if [ "$CURR_HOUR" -ge "$SCR_SCHEDULED_TIME" ] && [ "$CHK_GREP" == "0" ]; then
echo "
Stage-2: Time matched that is equals or greater then $SCR_SCHEDULED_TIME"
# If all matches, then run the code ! and add time stamp in file to avoid repeatingo/re-running the following script code on next RUN
echo "Finally: All conditions time + this day first execution matched, Now running the script code ...."
date +%d-%m-%Y >> $FILE
exit 1
fi

# If time have not come like its before the $SCR_SCHEDULED_TIME, then give error and exit now
echo "
Warning !
Scheudled time is equals or greater then $SCR_SCHEDULED_TIME hours : Current hour is $CURR_HOUR
Time have not came yet ! wait for your turn ..."

This way I was able to achieve the task I was thiking of.


# Actual Script which performs various functions with time checkups etc

Just for personnel reference …

#!/usr/bin/env bash
####!/bin/sh
#set -x
#trap "set +x; set -x" DEBUG
# BASH base script to discopnnect users whose expiry is today & update there GROUPS as well and update log table.
# the simple logic can be applied for about any other task as well. I tried to make it as simple as it can be
# also check if the scrpit ran successfully then dont re-run today,
# By Syed Jahanzaib
# CREATED on : 16th July, 2015
# Modified on 25h June 2018

# Local Variables
# Mysql credentials
SQLID="root"
SQLPASS="ROOTPASS"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
DB="radius"
#Table which contain main users information
TBL="users"
#Rad user group in which we will update user profile like from 1mb to expired or likewise
GROUP="radusergroup"
NEXTSRV="expired"
TBL_LOG="log"

# Date Time Variables
DATE=$(date +%d-%m-%Y)
FULL_DATE=`date`
CURR_HOUR=$(date +%H)
TODAY=$(date +"%Y-%m-%d")
WEEK=`date -d "-1000 days" '+%Y-%m-%d'`
BEGIN="1970-01-01"
H=$(date +'%-H')
################################
## Set time for script execution
## IMPORTANT, donot use 0 in it
SCR_SCHEDULED_TIME="12"
################################

#Network Related
hostname=`hostname`
IP=`ip route get 8.8.8.8 | awk '{print $NF; exit}'`

# Gmail Data
GMAILID="MYGMAIL@gmail.com"
GMAILPASS="MYGMAILPASS"
SMTP="64.233.184.108:587"
ADMINMAIL1="aacableAThotmail.com"
COMPANY="ZAIB"
MAILSUB="$COMPANY INFO -$HOSTNAME-$IP- List of account expired on $TODAY"

##################################################
##################################################
# Temp folder and files setup for various actions
##################################################
##################################################
TEMP="temp"
# Temp holders to store users list
ALLEXPLIST=/$TEMP/all_time_expired_users_list.txt
TODEXPLIST=/$TEMP/only_today_expired_users_list.txt
FILE=/$TEMP/check_user_expiration_exec.txt

# Checking if /temp folder is previously present or not , if not create one ...
{
if [ ! -d "/$TEMP" ]; then
echo
mkdir /$TEMP
fi
}

# remove and recreate users list to avoid any Duplication or issue
{
if [ -f $ALLEXPLIST ]; then
rm $ALLEXPLIST
touch $ALLEXPLIST
fi
}
{
if [ -f $TODEXPLIST ]; then
rm $TODEXPLIST
touch $TODEXPLIST
fi
}
# File Holder to store last execution date
{
if [ ! -f $FILE ]; then
touch $FILE
fi
}

############################
############################
###### START thE script cOdE
############################
############################

CHK_GREP=`grep -c $DATE $FILE`
echo "Current Date time is $FULL_DATE
"
# If script is executed successfully in current date, then dont re-run and exit now
echo "Stage-1:
Checking if the script have already ran successfully for today, by getting current date from the $FILE"
if grep -q $DATE $FILE >/dev/null 2>&1
then
echo "
Result:
It seems the script was executed successfully today $DATE, It will run on next date change. Exiting now ..."
exit 1
fi

# Check if time is matched that is greater or equals to $SCR_SCHEDULED_TIME and also check if script hae ran successfully or not previously by getting date from $FILE
echo "
Stage-2: Checking if current hour is equals or greater then '$SCR_SCHEDULED_TIME hours' & previous run is not done yet ..."
if [ "$CURR_HOUR" -ge "$SCR_SCHEDULED_TIME" ] && [ "$CHK_GREP" == "0" ]; then
echo "
Stage-2:
Time matched that is equals or greater then $SCR_SCHEDULED_TIME"
# If all matches, then run the code ! and add time stamp in file to avoid repeatingo/re-running the following script code on next RUN
echo "Finally:
All conditions time + this day first execution matched, Now running the script code ..."
date +%d-%m-%Y >> $FILE

# Pull users that are expiring from beginning till TODAY, to avoid missing any part
$CMD "use $DB; select username from $TBL where expiration between '$BEGIN' AND '$TODAY';" |sort > $ALLEXPLIST
# Pull user list that are expiring today only, for email purposes
$CMD "use $DB; select username from $TBL where expiration ='$TODAY';" |sort > $TODEXPLIST

# IF no user found , show error and exit - zaib
CHK=`wc -m $ALLEXPLIST | awk {'print $1}'`
if [ "$CHK" -eq 0 ]
then
echo "No user found expiring today, exiting ..."
exit 1
fi

# Apply formula
num=0
cat $ALLEXPLIST |while read data
do
num=$[$num+1]
USERNAME=`echo $data`

# Update user status in RADgroup & users table so that he will rejected on next login
IS_EXPIRED=`$CMD "use $DB; select is_expired from users where username ='$USERNAME';"`
if [ "$IS_EXPIRED" = "N" ]; then
echo "user_IS EXIPRED is NO *************************************************************************************************"
$CMD "use $DB; update $GROUP set groupname='$NEXTSRV' where username='$USERNAME';"
$CMD "use $DB; update $TBL set is_expired='Y' where username='$USERNAME';"
$CMD "use $DB; update $TBL set is_days_expired='Y' where username='$USERNAME';"
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User reached Expiration, Group udpated.');"
fi

# CHECK ONLINE AND KICK
# Pull account session id from radacct table, which will be used to COA OR user disconnection
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
# If user is not Online , just give info that he is not online
if [ -z "$ACCTSESID" ]; then
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User reached Expiration, Group udpated but its already offline');"
echo "$USERNAME - User reached Expiration, Group udpated but its already offline."
else
# Kick user by getting his NAS ip, secret and other info and log
# Mikrotik NAS IP and Radport and Shared Secret
NAS_IP=`$CMD "use $DB; select nasipaddress from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
NAS_SECRET=`$CMD "use $DB; select secret from nas where nasname ='$NAS_IP';"`
NAS_COA_PORT=`$CMD "use $DB; select nas_coa_port from nas where nasname ='$NAS_IP';"`
# Disconnect users now using RADCLIENT with username adn Account session ID taken from radacct table
echo user-name=$USERNAME,Acct-Session-Id=$ACCTSESID | radclient -x $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
# LOG into LOG TABLE in radius DB
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User reached Expiration, kicked & updated,.');"
echo "$USERNAME - User reached Expiration, kicked & updated."
fi
done

#TOT=`cat $TODEXPLIST | wc -l`
#echo "
#-----------
#-----------

#$COMAPNY _ List of TODAY $TODAY expired users, Total = $TOT"
#cat $TODEXPLIST

# Send Email to all admin - currently 4 users
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
fi

Regard’s
Syed Jahanzaib

June 19, 2018

NTP Server configuration in Ubuntu

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

ntp show

Task:

We want to create a local NTP server so that our local devices like switches / routers / systems can syn time with it without requiring internet access. The NTP daemon allows a machine on your network (if you would like) to operate as an NTP time server. Doing so will allow other machines on your local network to synchronize with your LAN time server in a very quick and accurate manner, since network latency is minimized. In this way, the differences in clocks between machines on your network is kept as minimal as possible. Mac, and even Windows boxes are also able to synchronize with an NTP server.

Note: If your windows workstation is part of domain, you will not be able to see INTERNET TIME setup in DATE TIME because workstation will sync time with the Domain.


OS: Ubuntu 16.04.4 LTS with internet access

First, Install the NTP daemon

sudo aptitude remove ntpdate
sudo aptitude install ntpd

Note: Once you have found one good syncing peer,  add it to the list, putting ‘iburst’ after the most promising one. For instance:

server 91.189.91.157 iburst

This will cause ntpd to synchronize very quickly with this server after starting up.

Now restart NTPD daemon …

sudo /etc/init.d/ntp restart

It may take few seconds or up to 15-20 minutes for the initial time sync.

Next, monitor your system log to see if you synchronize with a time server:

tail -f /var/log/syslog

root@linux:~# ntpq -c lpeer
remote refid st t when poll reach delay offset jitter
==============================================================================
+alphyn.canonica 132.246.11.231 2 u 109 1024 377 195.030 -3.184 1.820
-jiro.paina.net 131.113.192.40 2 u 55 1024 377 285.680 55.800 15.483
*ntp5.mobinnet.n 85.199.214.98 2 u 1026 1024 377 208.593 -2.359 3.459
+118.140.184.98 223.255.185.2 2 u 1207 512 174 128.292 2.083 30.041
-103.47.76.177 193.0.0.229 2 u 757 1024 17 110.355 7.986 75.054

You can test if NTP Server is working or not, just download NTPTOOL and Query.
As showed in the image below …

ntp.JPG


Configure Cisco Switch to sync with ubuntu time server

enable
configure terminal
# Timezone for Asia/Karachi, you may adjust it according to your local TZ
clock timezone PST +5
service timestamps log datetime localtime
# change NTP Server IP Address
ntp server 192.168.100.1
end
show ntp associations

Before vs After (on cisco 3850 switch)

BEFORE

Switch#sh clock
*11:36:14.321 UTC Tue Jun 19 2018

enable
configure terminal
clock timezone PST +5
service timestamps log datetime localtime
ntp server 101.11.11.240
end

AFTER

Switch#sh clock
*16:37:16.103 PST Tue Jun 19 2018
Switch#show ntp associations

address ref clock st when poll reach delay offset disp
~101.11.11.240 91.189.91.157 3 11 64 1 1002.8 283436. 7937.9
* sys.peer, # selected, + candidate, - outlyer, x falseticker, ~ configured
Switch#

Done.


 

June 8, 2018

Disabling Email for CRON Job’s

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

If you have configured many cronjob’s to run every minute or so on, & also using Gmail as mail relay system on the same box, you might see following in ​​/var/log/mail.log

Jun 8 10:21:10 radius postfix/smtp[5192]: 6DA0D16E0118: to=, relay=smtp.gmail.com[74.125.71.108]:587, delay=3.3,
delays=0.01/0/2.9/0.41, dsn=5.4.5, status=bounced (host smtp.gmail.com[74.125.71.108] said:
550 5.4.5 Daily user sending quota exceeded. e188-v6sm2329623wmf.21 - gsmtp (in reply to DATA command))

gmail error.JPG
gmail error

CRON will only email you if there is some output from you job. With everything redirected to null, there is no output and hence cron will not email you.

Using > /dev/null 2>&1 will redirect all your command output (both stdout and stderr) to /dev/null, meaning no outputs are printed to terminal.

If you have configured an emailing system which is using gmail (which limits 500 messages per day) as mail relay then put the following command at the end of the cron job line that always output the result.

Example:

Before:

*/5 * * * * /temp/mybashscript.sh

After:

*/5 * * * * /temp/mybashscript.sh  >/dev/null 2>&1

Regard’s
Syed Jahaznaib

June 1, 2018

Phpmyadmin – Short Notes

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


1# ROOT access error in phpmyadmin v 4.6.6deb5 with Ubuntu 18.0.4

If you have installed phpmyadmin in Ubuntu 18, & trying to login with root, you may see following error,

phpmyadmin root error

It’s recommended to add another user & use this ID to login in phpmyadmin.

Create User in Mysql:

Login to mysql & issue below commands, make sure to change user name and passwords

CREATE USER 'NEWUSER'@'%' IDENTIFIED BY 'NEWPASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'NEWUSER'@'%' WITH GRANT OPTION;
exit;

2# When viewing Tables in phpmyadmin, Popup Error Appears ‘some errors have been detected on the server, please look at the bottom of this window’

When viewing tables in Phpmyadmin v4.6.6deb5 [Ubuntu 18.0.4] , below error appears …

phpmyadmin view table error.JPG

FIX:

It seems that phpMyAdmin v4.6.6 is not fully compatible with PHP 7.x. Upgrade to new version v4.8 or above which will fix these compatibility issues.

OR following method.

Warning:

Make sure to backup sql.lib.php before any modification

cp /usr/share/phpmyadmin/libraries/sql.lib.php /usr/share/phpmyadmin/libraries/sql.lib.php.bak

Edit sql.lib.php

sudo vi /usr/share/phpmyadmin/libraries/sql.lib.php

Press CTRL + W and search for (count($analyzed_sql_results['select_expr'] == 1)

Replace it with ((count($analyzed_sql_results['select_expr']) == 1)

Save file and Exit.

Hopefully you will not see the above errors any more 🙂


Regard’s
Syed Jahanzaib

March 8, 2018

Short Notes on LibreNMS !

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

libre dashboard

This post contains short notes & step by step guide on open source network omonitoring system called LIBRENMS , it is based on Php/MySQL/Snmp.


STEP BY STEP iNSTALLATION FOR LIBRENMS

OS: Ubuntu 16 (Server Edition/64bit)

First install required support packages

apt-get update
# Now install all the required components in a single GO ...
apt install -y apache2 composer fping git graphviz imagemagick libapache2-mod-php7.0 mariadb-client mariadb-server mtr-tiny nmap php7.0-cli php7.0-curl php7.0-gd php7.0-json php7.0-mcrypt php7.0-mysql php7.0-snmp php7.0-xml php7.0-zip python-memcache python-mysqldb rrdtool snmp snmpd whois curl acl

Add local user for librenms

useradd librenms -d /opt/librenms -M -r
usermod -a -G librenms www-data
systemctl restart mysql

Create user & DB

mysql -uroot -pzaib1234
CREATE DATABASE librenms CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE USER 'librenms'@'localhost' IDENTIFIED BY 'zaib1234';
GRANT ALL PRIVILEGES ON librenms.* TO 'librenms'@'localhost';
FLUSH PRIVILEGES;
exit

Now edit maria-db config file

nano /etc/mysql/mariadb.conf.d/50-server.cnf

& add following

[Within the [mysqld] section please add]

innodb_file_per_table=1
sql-mode=""
lower_case_table_names=0
systemctl restart mysql

Save & Exit.

Web Server Config

Edit PHP files & setup TIMEZONEs

nano /etc/php/7.0/apache2/php.ini
nano /etc/php/7.0/cli/php.ini

Search for ‘date.timezone‘ & change it to

date.timezone = Asia/Karachi

Save & EXit.

Configure Apache

nano /etc/apache2/sites-available/librenms.conf

Add the following config,



DocumentRoot /opt/librenms/html/
ServerName librenms.localhost
AllowEncodedSlashes NoDecode

Require all granted
AllowOverride All
Options FollowSymLinks MultiViews



Save & Exit

a2dissite 000-default
a2ensite librenms.conf
a2enmod rewrite
systemctl restart apache2

Configure snmpd

cp /opt/librenms/snmpd.conf.example /etc/snmp/snmpd.conf

Now edit SNMPD config file and add our SNMP community string like public or other

nano /etc/snmp/snmpd.conf

Edit the text which says RANDOMSTRINGGOESHERE and set your own community string.
save and exit.

curl -o /usr/bin/distro https://raw.githubusercontent.com/librenms/librenms-agent/master/snmp/distro
chmod +x /usr/bin/distro
systemctl restart snmpd

CRONJOBS for librenms

cp /opt/librenms/librenms.nonroot.cron /etc/cron.d/librenms
cp /opt/librenms/misc/librenms.logrotate /etc/logrotate.d/librenms
chown -R librenms:librenms /opt/librenms
setfacl -d -m g::rwx /opt/librenms/rrd /opt/librenms/logs
setfacl -R -m g::rwx /opt/librenms/rrd /opt/librenms/logs

Install LIBRE-NMS via composer command

cd /opt
composer create-project --no-dev --keep-vcs librenms/librenms librenms dev-master

Example:

1- install libre via cmd

Now access libre-nms via browser …

http://yourip/install.php

& follow onscreen steps…

2- libre first step of gui

Make sure to add id password which is authorized to change DB , as required …

 

 

 

 

AT one point, You have to create the config.php manually as showed here.

nano /opt/librenms/config.php

& paste the data as showed in the config window of gui.

<?php
## Have a look in defaults.inc.php for examples of settings you can set here. DO NOT EDIT defaults.inc.php!

### Database config
$config['db_host'] = 'localhost';
$config['db_port'] = '3306';
$config['db_user'] = 'librenms';
$config['db_pass'] = 'zaib1234';
$config['db_name'] = 'librenms';
$config['db_socket'] = '';

// This is the user LibreNMS will run as
//Please ensure this user is created and has the correct permissions to your install
$config['user'] = 'librenms';

### Locations - it is recommended to keep the default
#$config['install_dir'] = "/opt/librenms";

### This should *only* be set if you want to *force* a particular hostname/port
### It will prevent the web interface being usable form any other hostname
#$config['base_url'] = "http://librenms.company.com";

### Enable this to use rrdcached. Be sure rrd_dir is within the rrdcached dir
### and that your web server has permission to talk to rrdcached.
#$config['rrdcached'] = "unix:/var/run/rrdcached.sock";

### Default community
$config['snmp']['community'] = array("public");

### Authentication Model
$config['auth_mechanism'] = "mysql"; # default, other options: ldap, http-auth
#$config['http_auth_guest'] = "guest"; # remember to configure this user if you use http-auth

### List of RFC1918 networks to allow scanning-based discovery
#$config['nets'][] = "10.0.0.0/8";
#$config['nets'][] = "172.16.0.0/12";
#$config['nets'][] = "192.168.0.0/16";

# Update configuration
#$config['update_channel'] = 'release'; # uncomment to follow the monthly release channel
#$config['update'] = 0; # uncomment to completely disable updates 

Save & Exit.

and click on FINISH on GUI.


librenms install done

 

Make sure to remove COMPOSER & set permissions when all is OK. Else it will give error in VALIDATION part.

chown -R librenms:librenms /opt/librenms
apt-get remove composer

 


Some more snaps

  • Add Device

 

add device

librenms add device with snmp

Devices Overview

device view

libre dashboard.JPG

February 26, 2018

Power of Open Source / Traffic Controlling with TC

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

speed meter

 

pwoerful pawn

Living in the Dark & Playing  & with the Open Source is Fun & Amazingly Powerful ! z@iB


Scenario:

We have few Linux base FTP/HTTP base media file sharing server setup. User can access these server by bypassing the router as we donot want to put load on the router itself & user should access these servers from switch level by intervlan routing setup.

Problem:

Since the FTP data is in many terabytes and hundreds of users are accessing it, this is creating bottleneck for server’s network/storage.

Solution:

On Linux we can use TRAFFIC CONTROLLER (TC) to limit bandwidth on per user IP basis.  We will create a simple bash script which will do the following in sequence …

  • FTP Server IP: 101.11.11.254
  • Clear any existing shaping rules
  • Create 4 bandwidth packages as following
    – 1024kbps  [for General Users subnet 101.11.11.0/24]
    – 2048kbps  [for Support Staff  subnet 101.11.50.0/24]
    – 512kbps     [for ICMP, so that ping should work fine , Protocol base]
    – 128kbps    [for any unknown IP series, that is no define above, for ALL 0.0.0.0/0.0.0.0]
  • Assign ip subnet to these packages (/24 and it will create per ip base rules via LOOP statement)
  • In this example, we are limiting Bandwidth in one way only, means packets sending from server to client will be applied limitation .. For both ways just duplicate rules and use src as well.

Create , the script!

mkdir /temp
touch /temp/shaper.sh
chmod +x /temp/shaper.sh

Now Edit

nano /temp/shaper.sh

& paste following,

#!/bin/bash
# Traffic Shaping Using `TC` in Linux / This was made for specific requirements.
# ITs customzied designed to fulfill some specific requirements & it went well.
# Following Script will limit outgoing bandwidth for specific ip range series.
# It will also allow ICMP more bandwidth so that ping to local server may not delay or imtout.
# It will then limit all other ips that are not listed to minimum bandwidth that is 128kbps.
# Adjust is as per your requirements.
# Syed Jahanzaib / aacable @ hotmail . com / https://aacable.wordpress.com :~)
# 23-Febraury-2018

# Enable following to see debug
# set -x

# Local Network Interface name
INT="ens33"
INT_SPEED="100Mbit"

# First IP Range & its package with class
IP1="101.11.11"
IP1_PKG="1mbit"
IP1_CLASSID="1:10"

# Second IP Range & its package with class
IP2="101.11.50"
IP2_PKG="2mbit"
IP2_CLASSID="1:20"

# Separate Bandwidth for ICMP protocol to minimze delay in PING/ICMP packets
ICMP_PKG="512kbit"
ICMP_CLASSID="1:200"

# Other Default Package Line, means all other unmarked IP's or traffic
UNKNOWN_PKG="128kbit"
UNKNOWN_CLASSID="1:201"

if [[ "$1" != "start" && "$1" != "stop" && "$1" != "status" ]]; then
echo "Usage:

./shaper.sh start
./shaper stop
./shaper status

by Jz!"
exit 1
fi

if [ $1 == "stop" ]; then
echo "Clearing all existing Queues on user request ...

Done!"
tc qdisc del dev $INT root 2> /dev/null > /dev/null
exit 1
fi

if [ $1 == "status" ]; then
echo "Showing Current TC Class and Filter Status
=========================================="
tc class show dev $INT
echo "

"
tc -s -d class show dev $INT
echo "=========================================="

exit 1
fi

if [ $1 == "start" ]; then
echo "Starting Shaper ...
Clearing all existing Queues for re-implementation ...
=========================================="
"

# clean existing down- and uplink qdiscs, hide errors
tc qdisc del dev $INT root 2> /dev/null > /dev/null

echo "
Adding Queues ...."
# Create egress shaping for all required classes
tc qdisc add dev $INT root handle 1: htb default 20 r2q 50
tc class add dev $INT parent 1: classid 1:1 htb rate $INT_SPEED ceil $INT_SPEED
tc class add dev $INT parent 1:1 classid $IP2_CLASSID htb rate $IP1_PKG ceil $IP1_PKG prio 100
tc class add dev $INT parent 1:1 classid $IP1_CLASSID htb rate $IP2_PKG ceil $IP2_PKG prio 100
tc class add dev $INT parent 1:1 classid $ICMP_CLASSID htb rate $ICMP_PKG ceil $ICMP_PKG prio 100
tc class add dev $INT parent 1:1 classid $UNKNOWN_CLASSID htb rate $UNKNOWN_PKG ceil $UNKNOWN_PKG prio 100

# ICMP reply Adjustment so ping may not timeout or high delay may not occur at client end for this server local IP
tc filter add dev $INT parent 1: protocol ip prio 100 u32 match ip protocol 1 0xff flowid $ICMP_CLASSID

# IP-1 series limitation applies here ...
for (( i = 1 ; i <= 255; i++ ))
do
b=$i
tc filter add dev $INT parent 1: protocol ip prio 100 u32 match ip dst $IP1.$i flowid $IP1_CLASSID
done

# IP-2 series limitation applies here ...
for (( i = 1 ; i <= 255; i++ ))
do
b=$i
tc filter add dev $INT parent 1: protocol ip prio 100 u32 match ip dst $IP2.$i flowid $IP2_CLASSID
done

# Send every unknown IP to classid UNKNOWN_CLASS that is lowest. so that any IP that is not listed above will get htis lowest bandwidth
tc filter add dev $INT parent 1: protocol ip prio 100 u32 match ip dst 0.0.0.0/0.0.0.0 flowid $UNKNOWN_CLASSID

echo "
Done!"
fi

Result:

linux TC down-result.jpg

 

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 users names who are quote base 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. once they will cross limit, script will disconnect them, change there group, and remove entry from users_qt 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(32) NOT NULL,
`uptime_limit` varchar(32) 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
) 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;

--

Trigger for RADACCT

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

# By Syed Jahanzaib
# Last Modified 22-Jun-2018
#BEGIN
#SET @ctime = (select current_time());
#SET @srvid = (select srvid from users where username =New.Username);
#SET @st = (select free_qt_start_time from services where srvid=@srvid);
#SET @et = (select free_qt_end_time from services where srvid=@srvid);
#if (@ctime > @st)
#AND (@ctime < @et)
#then
#else
#insert into log (data,msg) values ('zaib','datetimenotmatched');
#END IF;
BEGIN
if (NEW.acctoutputoctets != OLD.acctoutputoctets)
OR (NEW.acctinputoctets != OLD.acctinputoctets)
THEN
SET @fout = (NEW.acctoutputoctets - OLD.acctoutputoctets);
SET @fin = (NEW.acctinputoctets - OLD.acctinputoctets);
UPDATE users set qt_used = qt_used + @fout + @fin WHERE username = New.username;
END IF;
END

trigger.JPG


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') VALUES (NULL, 'zaib');

Now check the record.

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

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.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 disconnect 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 $QTDB;" > $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 qt_total from $TBL 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 should not repeat 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 name entry in a separate table, the script will check users from this table only and if any user is over quota it will 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.

I am sure that there are more appropriate methods to achieve the task , but I just managed to go through it 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

Older Posts »

%d bloggers like this: