Syed Jahanzaib Personal Blog to Share Knowledge !

May 24, 2016

Disconnect deleted user from the NAS ACTIVE list using RADCLIENT

Filed under: Linux Related, Radius Manager — Tags: , — Syed Jahanzaib / Pinochio~:) @ 5:17 PM

disconnectimage


SCENARIO:


Problem:

[As required by an specific OP]

When the OP deleted any user account from the Radius Billing system (example Radius manager) AND if his session is ACTIVE on the NAS , he will not disconnect automatically from the active users list [on the NAS] and he will continue to use the internet as long as his old session is connected. If the network is stable enough, the user can continue to use internet for days . So ultimately the user will become blood sucking vampire : ) ~


Solution:

We can schedule following script to run every 5 minutes. It will fetch the deleted users from the rm_syslog events, and will display the list, and then sends DISCONNECT request to the NAS to remove those users.

We can also use SSH or API method [preferred] , but it requires additional steps and skills. and It largely depends on the OP requirements and his skills to manage things as desired.

If there are multiple paths to reach the destination,
Select one with the least complications !
/ zaiB


Requirements:

radclient , utility which will send the disconnect requests.


the SCript !

 


#!/bin/bash
# set -x
# SCRIPT to fetch data of users removed manually from teh radius and disconnect them from the mikrotik active list.
# Syed Jahanzaib / aacable @ hotmail.com / https://aacable.wordpress.com
# 24-MAY-2016

# Setting FILE Variables
TMPFILE="/tmp/disconusers.txt"
> $TMPFILE

# Mikrotik NAS Details
NAS="192.168.0.1"
NASPORT="1700"
SECRET="PUT_RADIUS_SECRET_HERE"
CURDATE=`date`

#MYSQL INFO
SQLUSER="root"
SQLPASS="zSQL_PASSWORD"

#Interval in minutes
INTERVAL="5"

# Mysql query to fetch users whoes accounts are deleted from radius database.

# Print info
#mysql -u$SQLUSER -p$SQLPASS -e "use radius; select data1 from rm_syslog where eventid = '2' AND datetime >= NOW() - INTERVAL $INTERVAL MINUTE;"
# store in file
mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select data1 from rm_syslog where eventid = '2' AND datetime >= NOW() - INTERVAL $INTERVAL MINUTE;" > $TMPFILE
# Check if no user is deleted in DEFINED interval
# Echo this info for admin info purposes.

if [ -s $TMPFILE ]
then
echo "Following Users have Found for disconnection at Mikrotik $NAS..."

echo "DATE | USERNAME | NAS"

# Apply Formula to read the file in which dismissed users list and act accordingly.
num=0
cat $TMPFILE | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk '{print $1}'`

# Send Disconnection Packet to Mikrotik/NAS in order to disconnect user now
echo "$CURDATE | $USERNAME | $NAS"
done
echo ""
echo "Holding 10 seconds so you can review the list then it will start disconnecting the users from NAS $NAS"
sleep 10

# Applying Formula again to DISCONNECT users from the NAS
num=0
cat $TMPFILE | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk '{print $1}'`

# SEND DISCONNECT REQUEST TO NAS FOR SPECIFIC USERS
echo user-name=$USERNAME | radclient -x $NAS:1700 disconnect $SECRET
done

else

echo "No user have found deleted. Nothing to do..."
fi

# Script End
# Regard's / zaib


Results:

disc

 


Regard’s
Syed Jahanzaib

Mikrotik: Using Firewall Filters to Acquire Wan Data Usage Report via Email

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

monitoring_reportLast Update: 10-April-2019


Task:

An Operator [from Amsterdam] wanted to receive email on a daily basis for the Daily usage of WAN link on the Mikrotik router.

The script should send daily email with reporting as shown below

  • Daily usage report for specific interface
  • Monthly usage report for specific interface
  • Use human friendly values based on the values, auto convert values in KB/MB/GB/TB
  • Download breakup details in KB/MB/GB/TB
  • Daily Download Limit Alert , e.g: if daily download crosses X MB’s, warning should be added
  • Monthly Download Limit Alert , e.g: if daily download crosses X MB’s, warning should be added
  • Reset Daily/Monthly counters as per date/month change
  • Most importantly: the statistics should be saved in a file so that reboot should not affect any reporting.

Solution:

Use following Script. All sections are well explained in comments sections. Make sure you read it carefully line by line, and modify items required.

In this example I used FILTER rules approach to collect the bytes. Surely there are more elegant ways to fetch the data like … Linux base MRTG, NMS like DUDE, above all SNMP  and many others. I just used this method. You may select other as it suits you. This one worked neatly for me 🙂

You can customize it as per your requirements, like multi wan report, monthly report etc.

Note: This is unfinished script and requires improvement in many section, example addition of array is required to minimize coding in various section specially in month / bits byte selection algorithm. Tiem is short, will write more on it later.


Script Requirements:

Tested with Mikrotik 6.4x

  1. Schedule this script to run after 5 (or xx) minutes. When DATE change occurs, it will reset the counter file and filter rules counters.
  2. Create two FILTER rules in Mikrotik as following. SFP1 is the wan interface, so do change it accordingly.
/ip firewall filter
add action=passthrough chain=forward comment=WAN1IN in-interface=sfp1
add action=passthrough chain=forward comment=WAN1OUT out-interface=sfp1

the Scripts !

First create a supporting script which will actually get previous date day/date.

1-) Script Name: func_shiftDate

# func_shiftDate - add days to date
# Input: date, days
# date - "jan/1/2017"
# days - number
# correct only for years >1918 uncomment for testing
#:local date "jan/01/2100"
#:local days 2560
########################################
#:put "$date + $days"
:local mdays {31;28;31;30;31;30;31;31;30;31;30;31}
:local months {"jan"=1;"feb"=2;"mar"=3;"apr"=4;"may"=5;"jun"=6;"jul"=7;"aug"=8;"sep"=9;"oct"=10;"nov"=11;"dec"=12}
:local monthr {"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"}

:local dd [:tonum [:pick $date 4 6]]
:local yy [:tonum [:pick $date 7 11]]
:local month [:pick $date 0 3]
:local mm (:$months->$month)
:set dd ($dd+$days)
:local dm [:pick $mdays ($mm-1)]
:if ($mm=2 && (($yy&3=0 && ($yy/100*100 != $yy)) || $yy/400*400=$yy) ) do={ :set dm 29 }
:while ($dd>$dm) do={
 :set dd ($dd-$dm)
 :set mm ($mm+1)
 :if ($mm>12) do={
 :set mm 1
 :set yy ($yy+1)
 }
 :set dm [:pick $mdays ($mm-1)]
 :if ($mm=2 && (($yy&3=0 && ($yy/100*100 != $yy)) || $yy/400*400=$yy) ) do={ :set dm 29 }
};
:local res "$[:pick $monthr ($mm-1)]/"
:if ($dd<10) do={ :set res ($res."0") }
:set $res "$res$dd/$yy"
:return $res

Now create main script that we will schedule to run every five minutes.

2) wan1-total 

# Script to collect WAN DATA USAGE using Firewall FILTER rules, and send data to admin by Email Daily. (or whenever dates changes)
# And reset the counters on daily basis in night (or when date changes) . it will preserve the data in a file even if the router reboots.
# Syed Jahanzaib / aacable @ hotmail . com
# https://aacable.wordpress.com
# 1 Revision : MAY-2015
# 2 Revision : JAN-2018
# 3 Revision : ARP-2019
# Calling another script which have datefunction
:local shiftDate [:parse [/system script get func_shiftDate source]]
:local DT ([/system clock get date])
:local LASTDAY [$shiftDate date=$DT days=-1]
:local curTime [/system clock get time]
:local curDate [/system clock get date]
:local curYear [:pick $curDate 7 13]
:local curMon [ :pick $curDate 0 3 ]
:local curDay [:pick $curDate 4 6]
:local CURRENTMONTH "$curMon"
:local HOSTNAME ([/system identity get name])
# Set comments for firewall filter rules, change them as required
:local INT 
# Set interface name which we want to monitor
:local INT "sfp1"
# I have used MB as limit value as it have more granular controls, modify it as required !
:global DAILYALERT
:global MONTHLYALERT
## SETTING QUOTA FOR DAILY AND MONTHLY TRAFFIC (value in MB)
:local DOWNLOADLIMITPERDAY "5120"
:local DOWNLOADLIMITPERMONTH "153600"
# Get Uptime
:local UPTIME [/system resource get uptime]
:local WAN1INCOMMENT
:local WAN1OUTCOMMENT
# Change the interface name accordingly, and make sure to enter the matching comments too.
:local WAN1INCOMMENT "WAN1IN"
:local WAN1OUTCOMMENT "WAN1OUT"
# Acquire in/out counters from firewall filter rule
:local BYTESOUT [/ip firewall filter get [/ip firewall filter find comment="$WAN1INCOMMENT"] bytes]
:local BYTESIN [/ip firewall filter get [/ip firewall filter find comment="$WAN1OUTCOMMENT"] bytes]
:local DAILYTOTAL
:local DAILYTOTALKB
:local DAILYTOTALMB
:local DAILYTOTALGB
:local DAILYTOTALTB
:local DAILYTOTVALUEHUMANREADABLE
:local COMPANY "ZAIB-COMPANY"
:loca curMonNumber
:local LASTMONTH
:local LASTMONTHNAME
:local 1stexectution
:local FOOTER "This report was generated @ $curTime $curDay $curMon $curYear"
if ($curMon = "jan") do={
:set $LASTMONTHNAME "December"
}
if ($curMon = "feb") do={
:set $LASTMONTHNAME "January"
}
if ($curMon = "mar") do={
:set $LASTMONTHNAME "Febraury"
}
if ($curMon = "apr") do={
:set $LASTMONTHNAME "March"
}
if ($curMon = "may") do={
:set $LASTMONTHNAME "April"
}
if ($curMon = "jun") do={
:set $LASTMONTHNAME "May"
}
if ($curMon = "jul") do={
:set $LASTMONTHNAME "June"
}

{
:if ([:len [/interface find name="$INT" disabled=no]] != 0) do={
:local IPADDRESS "Wan interface has no IP address"
:if ([:len [/ip address find actual-interface="$INT" disabled=no]] != 0) do={
:set IPADDRESS [/ip address get [:pick [/ip address find interface="$INT"] 0] address]
}
# :put $IPADDRESS
} else={
:set $IPADDRESS "x.x.x.x"
}
}
#:local IPADDRESS [/ip address get [find interface="$INT"] address]

# set DATE TIME 
:local date
:local time
:set date [/system clock get date];
:set time [/system clock get time];

# SET GMAIL for sending email, make sure you have configured in /TOOLS,EMAIL option of mikrotik.
:global gmailid
:set gmailid "XXX@gmail.com"
:global gmailsmtp
:set gmailsmtp [:resolve "smtp.gmail.com"];
# Set your GMAIL Account Password
:local gmailpass
:set gmailpass XXXXX
# Set your email where you want to receieve the alert 
:local mailsendto1
:set mailsendto1 aacable@XXXXXX.com

#Starting Script Functions

:if ([:len [/file find name="dailycounter.txt"]] > 0) do={
:log warning "Previous Data found, script is not running forthe first time...";
:set 1stexectution "N";
} else {
:set 1stexectution "Y";
:log warning "It seems the script is running for the first time. creating necessary files to store counters..";
/file print file=dailycounter.txt where name=dailycounter.txt
/delay delay-time=1;
/file print file=monthlycounter.txt where name=monthlycounter.txt
/delay delay-time=1;
/file print file=counterslastupdate.txt where name=counterslastupdate.txt
/delay delay-time=1;
/file print file=counterslastupdatenormalformat.txt where name=counterslastupdatenormalformat.txt
/delay delay-time=1;
/file print file=counterslastupdatemonthnormalformat.txt where name=counterslastupdatemonthnormalformat.txt
/delay delay-time=1;
/file set monthlycounter.txt contents="1";
/delay delay-time=1;
/file set dailycounter.txt contents="1";
/delay delay-time=1;
/file set counterslastupdatenormalformat.txt contents="0";
/delay delay-time=1;
/file set counterslastupdate.txt contents="0";
/delay delay-time=1;
/file set counterslastupdatenormalformat.txt contents="0";
/delay delay-time=1;
/file set counterslastupdatemonthnormalformat.txt contents="0";
/delay delay-time=1;
}
# Setting variables 
:local CURRENTDATE "$curDay$curMon$curYear"
:local CURRENTMONTH "$curMon"
:local LASTUPDATEDATE value=[/file get counterslastupdate.txt contents]
:local LASTUPDATEDATENORMAL value=[/file get counterslastupdatenormalformat.txt contents]
:local LASTUPDATEDATEMONTH value=[/file get counterslastupdatemonthnormalformat.txt contents]
/file set counterslastupdatemonthnormalformat.txt contents=$curMon

# Update counters last update with current date time
/file set counterslastupdate.txt contents=$CURRENTDATE

# Calculate data in MB to be displayed in LOG and email
:set $DAILYTOTAL ($BYTESOUT+$BYTESIN)
:set $DAILYTOTALKB ($DAILYTOTAL / 1024)
:set $DAILYTOTALMB ($DAILYTOTAL / 1024 / 1024)
:set $DAILYTOTALGB ($DAILYTOTAL / 1024 / 1024 /1024)
:set $DAILYTOTALTB ($DAILYTOTAL / 1024 / 1024 /1024 /1024)

####### UPDATE USAGE in FILES FUNCTION START #########
# Get value from stored data
:local BEFOREDAILYVALUE value=[/file get dailycounter.txt contents]
:set $DAILYTOTALINFILE ($DAILYTOTAL+$BEFOREDAILYVALUE)
/file set dailycounter.txt contents=$DAILYTOTALINFILE

:local BEFOREMONTHLYVALUE value=[/file get monthlycounter.txt contents]
:set $MONTHLYTOTALINFILE ($DAILYTOTAL+$BEFOREMONTHLYVALUE)
/file set monthlycounter.txt contents=$MONTHLYTOTALINFILE
####### UPDATE USAGE in FILES FUNCTION END #########

:local DAILYTOTALFINAL value=[/file get dailycounter.txt contents]
# Calculate data in MB to be displayed in LOG and email
:set $DAILYTOTALKB ($DAILYTOTALFINAL / 1024)
:set $DAILYTOTALMB ($DAILYTOTALFINAL / 1024 / 1024)
:set $DAILYTOTALGB ($DAILYTOTALFINAL / 1024 / 1024 /1024)
:set $DAILYTOTALTB ($DAILYTOTALFINAL / 1024 / 1024 /1024 /1024)
#1000000000000
if (($DAILYTOTALFINAL>1) && ($DAILYTOTALFINAL<1000000)) do={
#:log warning "Daily Data is in KB"
:set $DAILYTOTVALUEHUMANREADABLE "$DAILYTOTALKB KB"
}
if (($DAILYTOTALFINAL>1000000) && ($DAILYTOTALFINAL<1000000000)) do={
#:log warning "Daily Data is in MB"
:set $DAILYTOTVALUEHUMANREADABLE "$DAILYTOTALMB MB"
}
if (($DAILYTOTALFINAL>1000000000) && ($DAILYTOTALFINAL<1000000000000)) do={
#:log warning "Daily Data is in GB"
:set $DAILYTOTVALUEHUMANREADABLE "$DAILYTOTALGB GB"
}
if ($DAILYTOTALFINAL>1000000000000) do={
#:log warning "Daily Data is in TB"
:set $DAILYTOTVALUEHUMANREADABLE "$DAILYTOTALTB TB"
}

# MONTHLY - Calculate data in MB to be displayed in LOG and email
:set $MONTHLYTOTALKB ($MONTHLYTOTALINFILE / 1024)
:set $MONTHLYTOTALMB ($MONTHLYTOTALINFILE / 1024 / 1024)
:set $MONTHLYTOTALGB ($MONTHLYTOTALINFILE / 1024 / 1024 /1024)
:set $MONTHLYTOTALTB ($MONTHLYTOTALINFILE / 1024 / 1024 /1024 /1024)
#1000000000000
if (($MONTHLYTOTALINFILE>1) && ($MONTHLYTOTALINFILE<1000000)) do={
#:log warning "MONTHLY Data is in KB"
:set $MONTHLYTOTVALUEHUMANREADABLE "$MONTHLYTOTALKB KB"
}
if (($MONTHLYTOTALINFILE>1000000) && ($MONTHLYTOTALINFILE<1000000000)) do={
#:log warning "MONTHLY Data is in MB"
:set $MONTHLYTOTVALUEHUMANREADABLE "$MONTHLYTOTALMB MB"
}
if (($MONTHLYTOTALINFILE>1000000000) && ($MONTHLYTOTALINFILE<1000000000000)) do={
#:log warning "MONTHLY Data is in GB"
:set $MONTHLYTOTVALUEHUMANREADABLE "$MONTHLYTOTALGB GB"
}
if ($MONTHLYTOTALINFILE>1000000000000) do={
#:log warning "MONTHLY Data is in TB"
:set $MONTHLYTOTVALUEHUMANREADABLE "$MONTHLYTOTALTB TB"
}

:if ($MONTHLYTOTALMB>$DOWNLOADLIMITPERMONTH) do={
:set MONTHLYALERT "- WARNING: Your Monthly download limit have reached that is $DOWNLOADLIMITPERMONTH MB per month ..."
} else {
:set MONTHLYALERT ""
#:log error "$MONTHLYALERT";
}

##### MONTHLY REPORT SECTION
#:if ([:pick [/system clock get date] ([:find [/system clock get date] "/" ] + 1) 6 ] = "01") do={:set $1stday value= "Y"}
#if ($1stday = "Y") do={
#:log error "1stday $1stday";
:if ($1stexectution = "N") do={
:if ($CURRENTMONTH = $LASTUPDATEDATEMONTH) do={ 
:log warning "MONTH not changed. No need to send email.";
} else={
:log warning "Sending Email for monthly Report & Resetting all counters...";
:local es "$HOSTNAME - $IPADDRESS - Monthly Report - $LASTMONTHNAME - $MONTHLYTOTVALUEHUMANREADABLE was downloaded via $INT link."
# Set Email Body
:local eb "HOSTNAME: $HOSTNAME / $IPADDRESS \n\n$MONTHLYALERT\n\n- $MONTHLYTOTVALUEHUMANREADABLE were downloaded via $INT link on Month $LASTMONTHNAME (guessing year is $curYear\n\nMore Details:\nMonthly Download Breakup Details (in KB/Mb/GB/TB separetely) :\n\n-KB: $MONTHLYTOTALKB\n-MB: $MONTHLYTOTALMB\n-GB: $MONTHLYTOTALGB\n-TB: $MONTHLYTOTALTB\n\n\n $COMPANY\n\n\n $FOOTER"
# Finally send email 
:log warning "Sending Email ...";
/tool e-mail send to=$mailsendto1 subject=$es body=$eb start-tls=yes user=$gmailid password=$gmailpass
# Resetting Monthly Counter file because month has changed...
#/file set dailycounter.txt contents="1";
/file set monthlycounter.txt contents="1";
}
}

:if ($DAILYTOTALMB>$DOWNLOADLIMITPERDAY) do={
:set DAILYALERT "- WARNING: Your Daily download limit have reached that is $DOWNLOADLIMITPERDAY MB per day ..."
} else {
:set DAILYALERT ""
#:log error "$DAILYALERT";
}

##### DAILY REPORT SECTION
# If date is changed (usually in night) , then send email using GMAIL , with the Data
:if ($1stexectution = "N") do={
:if ($CURRENTDATE = $LASTUPDATEDATE) do={ 
:log warning "Date not changed. No need to send email."
} else {
:log warning "DATE changed, sending email for last day data usage and also reset the Firewall Counters ...";
:if ($DAILYTOTALMB>$DOWNLOADLIMITPERDAY) do={
:local DAILYALERT "- WARNING: Your Daily download limit have reached that is $DOWNLOADLIMITPERDAY MB per day ..."
}
# Set Email Subject
:local shiftDate [:parse [/system script get func_shiftDate source]]
:local DT ([/system clock get date])
:local LASTDAY [$shiftDate date=$DT days=-1]
:local es "$HOSTNAME - $IPADDRESS - Daily Report - $LASTDAY - $DAILYTOTVALUEHUMANREADABLE downloaded via $INT link."
# Set Email Body
:local eb "HOSTNAME: $HOSTNAME / $IPADDRESS \n\n$DAILYALERT\n$MONTHLYALERT\n\n- $DAILYTOTVALUEHUMANREADABLE were downloaded via $INT link YESTERDAY (guessing Date is $LASTDAY ) \n- $MONTHLYTOTVALUEHUMANREADABLE were downloaded via $INT link on Month $curMon/$curYear\n\nMore Details: \n\n\ Daily Download Breakup Details (in KB/Mb/GB/TB separetely) : \n-KB: $DAILYTOTALKB\n-MB: $DAILYTOTALMB\n-GB: $DAILYTOTALGB\n-TB: $DAILYTOTALTB\n\n\n $COMPANY\n\n $FOOTER"
# Finally send email 
:log warning "Sending Email for Daily Report ...";
/tool e-mail send to=$mailsendto1 subject=$es body=$eb start-tls=yes user=$gmailid password=$gmailpass
/file set dailycounter.txt contents="1";
}
}

# Update Date time stamp in both files / zaib
/file set counterslastupdate.txt contents=$CURRENTDATE
/file set counterslastupdatenormalformat.txt contents=$curDate

:log warning "DAILY $DT REPORT: $DAILYTOTVALUEHUMANREADABLE have been download via $INT interface
$DAILYALERT";
:log warning "MONTH $curMon REPORT: $MONTHLYTOTVALUEHUMANREADABLE have been download via $INT interface
$MONTHLYALERT";

/ip firewall filter reset-counters [find comment=$WAN1INCOMMENT]
/ip firewall filter reset-counters [find comment=$WAN1OUTCOMMENT]
:log error "Script Ends Here ...";

# Regard's
# Syed Jahanzaib

End Results !

wan usage report.PNG

 

 

%d bloggers like this: