Syed Jahanzaib – Personal Blog to Share Knowledge !

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

 

Renew DHCP lease if Gateway not responding

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

automation

Nothing Fancy , just short notes for script command reference purposes!

Following is a very simple 2 minutes instant cooked noddle type script. It will simply check the gateway status acquired by the dhcp-client using  ARP ping, , if it fails, then it will simply try to renew the ip by release/renew. Its very basic level, but its interesting to see how Mikrotik can really help you in day to day task by facilitated with the Tik level scripting !

Regard’s
Syed Jahanzaib

# Mikrotik Script - Tested with 5.x
# Script to check default gateway acquired by dhcp client on specific interface,
# Lot of room for improvements and modification but following was enough for some particular task,
# You can add BOUND status as well too, but i wanted this particular checking, you can add whatever you like
# Syed Jahanzaib == aacable AT hotmail DOT com - https:// aacable DOT wordpress DOT com
# Feb,2018
# Setting Variables

# Set Interface name which will get DHCP ip , This is the only option you may need to modify
:local INTERFACE "wan1"

# Number of Ping Count, how many times mikrotik should ping the target device
:local PINGCOUNT "5"

# Ping threshold, how many values should set alert, like if 5 out of 5 goes out
:local PINGTS "5"

:local i 0;
#:local i value=0;
:local F 0;
:local date;
:local time;

:log info "Checking default gateway for $INTERFACE interfaces."
:local DHCPGW [ /ip dhcp-client get [/ip dhcp-client find where interface=$INTERFACE ] gateway ]

# IF there is no default gateway on dhcpclient interface or if interface is disabled, then error
:if ([:len $DHCPGW] = 0) do={
:log error "No DEFAULT gateway found on $INTERFACE interface @ $date $time ..."
# Try to renew ip
/ip dhcp-client release $INTERFACE
/ip dhcp-client renew $INTERFACE
# Exit the script without further process ... I found this recently because in mikrotik there is no EXIT 1
error :error
}

# PING host $PINGCOUNT times
:for i from=1 to=$PINGCOUNT do={
:if ([/ping arp-ping=yes interface=$INTERFACE $DHCPGW count=1]=0) do={:set F ($F + 1)}
:delay 1;
};

# If no ping found then give error and do action
:if (($F=$PINGTS)) do={
:log error "PING to $DHCPGW via $INTERFACE is DOWN! @ $date $time "

# Take action if unable to ping gateway
/ip dhcp-client release $INTERFACE
/ip dhcp-client renew $INTERFACE
} else={
:log warning "PING to $DHCPGW via $INTERFACE is UP! @ $date $time "
}

February 20, 2018

Bursting with Mikrotik Burst ^o^

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

Disclaimer! This is important!

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

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

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

Regard's
Syed Jahanzaib~

Mikrotik Burst feature provides predefined extra bandwidth for a limited period of time IF the user remains under the burst threshold limit, or else He will be limited to his max–limit package.

It is best explained here

https://wiki.mikrotik.com/wiki/Manual:Queues_-_Burst

 

Real life Example:


Explanation !

  • User IP (1-TARGET) on which this queue will be implemented
  • When this IP will start downloading he can reach download rate of 512 kbps (3-BURST_LIMIT)
  • Until he continue to do so for a minute (5-BURST_TIME) (period of time, in seconds, over which the average data rate is calculated. (This is NOT the time of actual burst, so on avg it will become 30s)
  • That is on an average basis his download remains 256 kbps (4-BURST_THRESHOLD) for a minute, (average of 30 seconds)
  • Then he will be get back limited to his max-limit (2-MAX_LIMIT)
  • When a user doesn’t use the traffic at all and 30 second average goes to 0 so the next time traffic is requested then it will be at the Burst speed (3-BURST_LIMIT).

 

Small BURST TIME may not give you correct results. So use reasonable time. in this example I used shorter time for demonstration purposes. A large burst isn’t a problem technically, it’s more of a business decision.

To calculate burst and relates values, download this excel sheet named “MikroTik burst simulator.xlsx” from my google drive & try it yourself … you will get clear picture


Another Example by joshaven! so that you can better understand —-


Now look at its Demo ,

 

 

FREERADIUS WITH MIKROTIK – Part #13 – Detecting user device vendor based on MAC address

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

fre2

online users by vendor

mac-address

1- identify vendor from mac

 

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …

Disclaimer! This is important!

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

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

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

Regard's
Syed Jahanzaib~

* Scenario:

We have a generic freeradius based billing system in place. in RADACCT table, we have a trigger that fire-up after successfull connection made from user device & it inserts user device MAC address in radcheck table for MAC validation & restriction.

* Requirement:

For better management , control & reporting purposes we want to add more checks and controls by adding user calling device VENDOR name in an additional column so that we can have idea what devices are most common in our network, or to detect any device that is prohibited by the company SOP or policy example commercial grade routers to minimize misuse of our residential services. Based on this reporting  we can prohibit connecting these devices as well if required.

To fulfill this we will do following

  1. Create a TRIGGER on RADACCT that will executes after INSERT record (like when user will connect to system successfully)
  2. Create DB and upload vendors mac address data so that we can query for vendor name locally (alternate approach is to simple use any Perl or bash script to look up vendor name dynamically on the fly form the internet)

 


OK before creating TRIGGER we must learn or understand our USER table where mac related will be added or update. One example for such table is showed below …

root@radius:/temp# mysql -uroot -pSQLPASS -e "use radius; describe users;"

+-------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(128) | NO | UNI | 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(3) | NO | | NULL | |
| expiration | date | YES | | NULL | |
| mac | varchar(30) | NO | | NULL | |
| macvendor | varchar(128) | NO | | NULL | |
| bwpkg | varchar(256) | NO | | NULL | |
| pool | varchar(128) | YES | | other | |
| is_enabled | int(1) | NO | | NULL | |
| is_days_expired | int(1) | NO | | NULL | |
| is_qt_expired | int(1) | NO | | NULL | |
| is_uptime_expired | int(1) | NO | | NULL | |
| qt_total | varchar(32) | NO | | NULL | |
| qt_used | varchar(20) | NO | | NULL | |
| uptime_limit | varchar(20) | NO | | NULL | |
| uptime_used | varchar(32) | NO | | NULL | |
| owner | text | NO | | NULL | |
| vlanid | varchar(32) | NO | | NULL | |
| createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------+--------------+------+-----+-------------------+-----------------------------+

In this post we have interest in two columns named mac and macvendor


Now we will create TRIGGER with our so called magical code 😉

1- TRIGGER for radacct table

CREATE TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct`
 FOR EACH ROW BEGIN
# Check if user mac is already added in radcheck table,
SET @mac = (SELECT count(*) from radcheck where username=New.username and attribute='Calling-Station-ID');
# If there is no entry for mac, then update mac in radcheck table, so that in future only this mac will be able to connect with that username
IF (@mac = 0) THEN
INSERT into radcheck (username,attribute,op,value) values (NEW.username,'Calling-Station-ID',':=',NEW.callingstationid);
# add mac in users table for general record purpose
UPDATE users SET mac = NEW.callingstationid where username = NEW.username;
# trim mac for first 3 strings to detect vendor company
SET @mactrim = (select LEFT(mac, 8) from users where username=New.username);
# get vendor name from mac db table
SET @macvendor1 = (select vendor from macdb where oui=@mactrim);
# Update vendor name in user table
UPDATE users SET macvendor=@macvendor1 where username = NEW.username;
END IF;
END
#SYED Jahanzaib - 
2- triger

2- Create MAC Address Database for VENDOR Lookup

This is a bit debatable part, I prefer to have local database for vendor mac addresses so that all lookup should be done locally rather then using any API for mac lookup. But if you want to prefer using internet base lookup , then you can use perl, bash or any other method to do lookup one example for internet lookup is as follows …

curl http://api.macvendors.com/70-54-D2-16-A5-D9
#OUTPUT
PEGATRON CORPORATION

Download macdb.sql from my google drive and import it in RADIUS DB

https://drive.google.com/drive/folders/1WwQTsK2WegT6T7IFH19IDWZ3-L_lV22v

If no error occurs you will be seeing a new table named macdb with all vendors names as well 🙂

db import successfull.JPG

 

to get trimmed OUI, use following

sourcecode]mysql -uroot -pSQLPASS –skip-column-names -s -e “use radius; select LEFT(mac, 8) from users;”/sourcecode]


Now connect any user as normal, and see the mysql.log file

2018-02-20T06:41:04.593739Z 24 Query INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay, xascendsessionsvrkey) VALUES ('81100003', '702d22ac0a080f57', 'zaib', '', '101.11.11.253', '9', 'Ethernet', '2018-02-20 11:41:04', NULL, '0', 'RADIUS', '', '', '0', '0', 'service1', '00:0C:29:B9:D8:A0', '', 'Framed-User', 'PPP', '192.168.50.255', '0', '0', '')
#As soon we receive entry for INSERT, TRIGGER will fire-up, see below log
2018-02-20T06:41:04.594676Z 24 Query SET @mac = (SELECT count(*) from radcheck where username=New.username and attribute='Calling-Station-ID')
2018-02-20T06:41:04.594871Z 24 Query INSERT into radcheck (username,attribute,op,value) values (NEW.username,'Calling-Station-ID',':=',NEW.callingstationid)
2018-02-20T06:41:04.595020Z 24 Query UPDATE users SET mac = NEW.callingstationid where username = NEW.username
2018-02-20T06:41:04.595151Z 24 Query SET @mactrim = (select LEFT(mac, 8) from users where username=New.username)
2018-02-20T06:41:04.595256Z 24 Query SET @macvendor1 = (select vendor from macdb where oui=@mactrim)
2018-02-20T06:41:04.607786Z 24 Query UPDATE users SET macvendor=@macvendor1 where username = NEW.username

& FINALLY we will see records in USERS table that will be displayed on FRONTEND 🙂

1- identify vendor from mac

 

February 19, 2018

High CPU load when PPPoE sessions disconnects in Mikrotik

Filed under: Uncategorized — Syed Jahanzaib / Pinochio~:) @ 4:46 PM

stress


Disclaimer:

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

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

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


Scenario-1:

We are using Mikrotik CCR as PPPOE/NAS. We are using public ip routing setup so each user is assigned public ip via pppoe profile.

Scenario-2:

We are using single Mikrotik CCR as PPPOE/NAS. We have local dsl service therefore NATTING is also done on the same router.


Problem:

When we have network outages like light failure in any particular area , in LOG we see many PPPoE sessions disconnects with ‘peer not responding‘ messages. Exactly at this moments, our NAS CPU usage reaches to almost 100% , which results in router stops passing any kind of traffic. This can continue for a minute or so on.

As showed in the image below …

pppoe high cpu usage

If you are using Masquarade /NAT on the router, that is the problem. When using Masquarade, RouterOS has to do full connection tracking recalculation on EACH interface connect/disconnect.

So if you have lots of PPP session connecting/disconnecting, connection tracking will constantly be recalculated which can cause high CPU usage. When interfaces connect/disconnect, in combination with NAT, it gives you high CPU usage.


Solution OR Possible Workarounds :

First read this

Separating NATTING from ROUTING in Mikrotik

https://aacable.wordpress.com/2018/03/27/separating-natting-from-routing-in-mikrotik/

  • If you have private ip users with natting, Stop using Masquarade on same router that have a lot of dynamic interfaces. Just DO NOT use NAT on any router that have high number of connecting/disconnecting interfaces. Place an additional router connected with your PPPoE NAS, and route NAT there.
    Example: Add another router & perform all natting on that router by sending marked traffic from private ip series to that nat router. Setup routing between the PPPoE NAS and the NAT router.
  • IF all of your clients are on public IP , you can simply Turn Off connection tracking completely. This is the simplest approach.But beware that turning of CT will disable all NATTING / marking traffic as well.
    Note: You can exempt your specific public pool from connection tracking as well.

ct

  • Any device that is CORE device or Gateway on your network, It should be assigned to perform one job only. Try not to mix multiple functions in one device. This will save you from later headache of troubleshooting.

Please read this …

Features affected by connection tracking

  • NAT
  • firewall:
    • connection-bytes
    • connection-mark
    • connection-type
    • connection-state
    • connection-limit
    • connection-rate
    • layer7-protocol
    • p2p
    • new-connection-mark
    • tarpit
  • p2p matching in simple queues

So if you will turn OFF the connection tracking, above features will stop working.


– Code Snippet:

Some working example of excluding your public pool from connection tracking

  • First make sure Connection Tracking is set to AUTO
/ip firewall connection tracking set enabled=auto
  • Then make a address list which should have your users ip pool so that we can use this list as an Object in multiple rules later.
/ip firewall address-list
add address=1.1.1.0/24 list=public_pool
#add address=2.1.1.0/24 list=public_pool
  • Now create rule to turn off connection tracking from our public ip users witht the RAW table
/ip firewall raw
add action=notrack chain=prerouting src-address-list=public_pool
add action=notrack chain=prerouting dst-address-list=public_pool

That’s it!



Some Tips for General Router Management

  • Turn off all non essential services that are not actually being used or needed. Services place an additional CPU load on any system. Example, you can move your DHCP role to cisco switches for better response , also for intervlan routing it is highly recommended., Also if your ROS is acting as DNS as well, then move DNS role to dedicated dns server like BIND etc. This will free up some resources from the core system
  • Use 10-gig network cards instead of 1-gig / Use 1-gig network cards instead of 100 meg
  • Disable STP if it is not needed. Now this is highly debatable part I know 🙂
  • Use Dynamic queues , they are spreader over multi cores

Regard's
Syed Jahanzaib ~

FREERADIUS WITH MIKROTIK – Part #12 – Happy Hours !

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

fre2

happy hours

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …


* SCENARIO:

We have a full working freeradius based billing system which have multiple Quota base services assigned to users. All users accounting is stored in radacct table as usual, but we have a separate column named qt_used in users table which is updates via radacct trigger when accounting updates arrives from the NAS. This column is checked by an external script every 10 minutes & if it finds qt_used value above then qt_total then it simply change user group to EXPIRED group & disconnects users from NAS by sending COA / POD.

* TASK:

Our bandwidth remains empty in night therefore we want to provide some extra benefits to quota base users by BYPASSING quota limit & counting in specific late night hours.

Example: There should be no QUOTA counting for all quota base users from 00:00 (midnight) till 8am (morning).

* Possible SOLUTION !

Like always, this may not be best optimal solution to achieve the task, but still it may work. Be sure took for other better approach rather then this sol.

Following is our USERS table

mysql> describe users;
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(128) | NO | UNI | 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(3) | NO | | NULL | |
| expiration | date | YES | | NULL | |
| mac | varchar(30) | NO | | NULL | |
| bwpkg | varchar(256) | NO | | NULL | |
| is_enabled | int(1) | NO | | NULL | |
| is_days_expired | int(1) | NO | | NULL | |
| is_qt_expired | int(1) | NO | | NULL | |
| is_uptime_expired | int(1) | NO | | NULL | |
| qt_used | varchar(20) | NO | | NULL | |
| uptime_used | varchar(32) | NO | | NULL | |
| owner | text | NO | | NULL | |
| vlanid | varchar(32) | NO | | NULL | |
| createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
22 rows in set (0.00 sec)

& following is our services table

mysql> describe services;
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| srvid | int(10) | NO | PRI | NULL | auto_increment |
| srvname | varchar(128) | NO | MUL | NULL | |
| descr | varchar(128) | YES | | other | |
| enabled | varchar(1) | NO | | NULL | |
| expdays | varchar(3) | NO | | NULL | |
| dlimit | varchar(30) | NO | | NULL | |
| ulimit | varchar(30) | NO | | NULL | |
| qt_enabled | tinyint(1) | NO | | NULL | |
| tot_qt | int(128) | NO | | NULL | |
| free_quota_enabled | tinyint(4) | NO | | NULL | |
| free_qt_start_time | time | NO | | NULL | |
| free_qt_end_time | time | NO | | NULL | |
| ippool | varchar(30) | NO | | NULL | |
| createdon | datetime | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
14 rows in set (0.00 sec)

* MYSQL TRIGGER on RADACCT table for AFTER UPDATE ACTION. [ This is our magical code 😛 ]

Ok, now we will create a TRIGGER on radacct table for AFTER UPDATE action.
[apply it in mysql shell on radius table]

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)
OR (@ctime < @et)
then
UPDATE users set qt_used = qt_used + NEW.acctoutputoctets WHERE username = New.username;
# below section ELSE is not required, I just added it for ON THE FLY troubleshooting]
else
insert into log (data,msg) values ('zaib','happy_hours_time_not_matched');
END IF;
END
# Syed Jahanzaib / aacable at hotmail dot com

Current time is 11:00{am}, inspect the mysql.log & you will find following (quota will not update because time doesnt matches)

2018-02-19T06:18:26.797015Z 51 Query UPDATE radacct SET framedipaddress = '192.168.50.255', acctsessiontime = '1841', acctinputoctets = '0' > 32 | '122655', acctoutputoctets = '0' > 32 | '145069' WHERE acctsessionid = '8100000e' AND username = 'zaib' AND nasipaddress = '101.11.11.253'
2018-02-19T16:18:26.797819Z 51 Query SET @ctime = (select current_time())
2018-02-19T16:18:26.797889Z 51 Query SET @srvid = (select srvid from users where username =New.Username)
2018-02-19T16:18:26.798009Z 51 Query SET @st = (select free_qt_start_time from services where srvid=@srvid)
2018-02-19T16:18:26.798092Z 51 Query SET @et = (select free_qt_end_time from services where srvid=@srvid)
2018-02-19T16:18:26.798165Z 51 Query insert into log (data,msg) values ('zaib','happy_hours_time_not_matched)

.

& if the radacct updates came in between happy hours then we will observe following (Quota will update accordingly)

2018-02-19T06:19:31.818111Z 98 Query UPDATE radacct SET framedipaddress = '192.168.50.255', acctsessiontime = '1906', acctinputoctets = '0' << 32 | '130274', acctoutputoctets = '0' << 32 | '161901' WHERE acctsessionid = '8100000e' AND username = 'zaib' AND nasipaddress = '101.11.11.253'
2018-02-19T06:19:31.818500Z 98 Query SET @ctime = (select current_time())
2018-02-19T06:19:31.818574Z 98 Query SET @srvid = (select srvid from users where username =New.Username)
2018-02-19T06:19:31.818680Z 98 Query SET @st = (select free_qt_start_time from services where srvid=@srvid)
2018-02-19T06:19:31.818758Z 98 Query SET @et = (select free_qt_end_time from services where srvid=@srvid)
2018-02-19T06:19:31.818833Z 98 Query UPDATE users set qt_used = qt_used + NEW.acctoutputoctets WHERE username = New.username

 


Regard's
Syed Jahanzaib ~

.

February 5, 2018

Access other OP portal via Mikrotik Load Balancer

Filed under: Mikrotik Related — Tags: — Syed Jahanzaib / Pinochio~:) @ 8:37 PM

portal routing in pcc.jpg

Disclaimer: I receive many emails from local operators on howto access other operators media sharing portal so that there local users can access them. Rather then replying each one separately & Due to time shortage, I am posting a simple method on how you can let your users access the outer operator media portal via your load balance mikrotik.  It is highly recommended to first search for the target web site/servers ip either using trace-route or wire shark. you have to conduct lengthy tracing by simply first try to connect with the target web site and start downloading multiple files, now using either TORCH, or using WIRE SHARK, you can get ips of all the servers which is being accessed by the torrent/idm which is connecting wit the target services. just make a note of these ip’s and add them in list either ip by ip or by /24 subnet.

Also It is recommended to use Mark Connections / Packets and Mark Routing. This way you wont have to create addition rules. So following pseudo codes is highly recommended to fit yourself in a famous quote that says `Work Smarter , not harder …`

Regard’s
Syed Jahanzaib

Example of WIRESHARK is posted bellow ….

wire-shark-example


Scenario:

We are using Mikrotik as pppoe server and dual vdsl links as WAN load balancer. We have acquired another Operator X line just to access there Entertainment portal which is great in media sharing files including video games etc. We want to let our user access there portal using our mikrotik without interfering with any other network.


Solution:

Quick & Dirty method. You should refine it when deploying in production environment.

We have configured an simple wifi router along with Operator X service in it. Now connect this router LAN line in your mikrotik (example Port 12).

IP Scheme:

  • Mikrotik LAN IP for pppoe users : eth0 > 192.168.0.1
  • Mikrotik PPPoE IP series (allowed users for internet) : 192.168.200.0/24
  • Mikrotik WAN-1 IP Series for DSL1 : eth1 > 192.168.1.1
  • Mikrotik WAN-2 IP Series for DSL2 : eth2 > 192.168.2.1
  • Mikrotik eth12 IP Series for Portal X : eth12 > 192.168.12.2 ( > 192.168.12.1 is wifi router with local OP service)
  • PORTAL-X IP Series: Web Portal – 123.123.123.0/24  Download servers – 172.17.1.0/24

Note:
For DNS, you can either use the OP-X dns servers (or wifi router as your dns as wifi router will get the DNS dynamically from the OP-X, or you can make static dns entries in your mikrotik dns server , and make sure all of your clients are using your mikrotik as there preferred dns server, you can also create a forced router to redirect all outgoing dns requests to your mirkotik. whatever is easier for you 🙂


Code!

# PPPoE Users IP List to access internet/portal
/ip firewall address-list
add address=192.168.200.0/24 comment="Allowed Users to Use Internet" list="allowed users"
# Add Portal X IP Series, you can get there list by inspecting torrent files, or using WIRESHARK
add address=123.123.123.0/24 list=portalx_list
add address=172.17.1.0/24 list=portalx_list

# Accept the PORTAL X packets to avoid processing them in PCC, then using routes we can route them via wifi router
/ip firewall mangle
add action=accept chain=prerouting comment="ACCEPT portalx_list PACKETS FROM PROCESSING THEM IN PCC - ZAIB" dst-address-list=portalx_list src-address-list="pppoe_allowed_users"

# Allow requests going to Portal X interface (to wifi router with OP X service)
/ip firewall nat
add action=masquerade chain=srcnat comment=ALLOW_ACCESS_TO_portalx_list_INTERFACE out-interface=eth12 src-address-list="pppoe_allowed_users

# Since we have excluded the Portal.X from PCC, therefor we have to create ROUTE for these packets
# So that these packets should route via Wi.Fi Router (connected with OP X service)
/ip route
add distance=1 dst-address=123.123.123.0/24 gateway=192.168.12.1 comment=route_for_portalx_site_going_via_local_wifi_router
add distance=1 dst-address=172.17.1.0/24 gateway=192.168.12.1 comment=route_for_portalx_site_going_via_local_wifi_router

# In the end , simply create QUEUE to allow more bandwidth going to Portal X servers,
# Again, if you had used packet marking, then you can use marked pkts & use single queue, more efficient
/queue simple
add max-limit=1G/1G name="portalx_list.torrent queue-1G" target=172.17.17.0/24
add max-limit=1G/1G name=portalx_list-public-ips-1G target=123.123.123.0/24

%d bloggers like this: