Syed Jahanzaib Personal Blog to Share Knowledge !

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 ,

 

 

Advertisements

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

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

fre2

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

OK lets move forward to create local DB for mac and vendor lookup.

  • Install required modules
apt-get install python-mysqldb python-minimal
  • Download the MAC address OUI from the IEEE site.
cd /tmp
wget http://standards.ieee.org/develop/regauth/oui/oui.txt
  • Once download is done, Create script that will insert mac records into separate table named ​mactable
touch mactosql.sh
chmod +x touch mactosql.sh

Now edit file

nano mactosql.sh

Paste following

# 09-07-2016
# ouiUpdate, grabs the iee oui list and adds it to a table in mysql database.
# Provides no syntax checking of the input file, use with caution
# based on load_iou_to_pgsql.py https://gist.github.com/pwldp/dee6d5f9868a1dd1d076
# Usage:
# python ouiUpdateMysql.py

import MySQLdb
import re
import urllib
import sys

# Globals
# Database Handle, again, no need to change them; Only Declarations
DB = ''
DB_CURSOR = ''
OUI_URL = "http://standards.ieee.org/develop/regauth/oui/oui.txt"
OUI_FILE = "oui.txt"

# Database Related Globals
DB_NAME='radius'
DB_USERNAME='root'
DB_PASSWORD='SQL_PASSWORD'
DB_TABLE='macdb'
DB_HOST='localhost'

def main():
global DB
global DB_CURSOR
global DB_TABLE
# Download oui.txt Comment out the two lines below if you do not want to download the file and use a offline copy
#####
# print "Downloading ",OUI_URL
# urllib.urlretrieve(OUI_URL, OUI_FILE)
#####
##connect to db
try:
## Opening connection to Database with pre-configured Database, Host, User and Password
DB = MySQLdb.connect(host=DB_HOST, user=DB_USERNAME, passwd=DB_PASSWORD, db=DB_NAME)
DB_CURSOR = DB.cursor()
except:
sys.exit('Unable to Open Connection to Database')

# Drop table and start from new
DB_CURSOR.execute("DROP TABLE IF EXISTS ouiList")
# Create table as per requirement
sql ="""CREATE TABLE IF NOT EXISTS ouiList (
id int(6) NOT NULL AUTO_INCREMENT,
oui varchar(8) DEFAULT NULL,
vendor varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB"""

# make the new table
DB_CURSOR.execute(sql)

# parsing oui.txt data and adding to table
print "Parsing data..."
with open(OUI_FILE) as infile:
print "Inserting data into table, please wait..."
for line in infile:
#do_something_with(line)
if re.search("(hex)", line):
try:
mac,vendor = line.strip().split("(hex)")
except:
mac = vendor = ''
#print line.strip().split("(hex)")
#print mac.strip().replace("-",":").lower(), vendor.strip()
if mac!='' and vendor!='':
sql = "INSERT INTO ouiList "
sql+= "(oui,vendor) "
sql+= "VALUES ("
sql+= "'%s'," % mac.strip().replace("-",":").lower()
sql+= "'%s'" % vendor.strip().replace("'","`")
sql+= ")"
#print sql
try:
DB_CURSOR.execute(sql)
DB.commit()
except Exception, e:
DB.rollback()
print "Not inserted because error: "

# count the number of lines in the table
sql = ("SELECT count(id) FROM ouiList WHERE 1")
DB_CURSOR.execute(sql)
result = DB_CURSOR.fetchone()
if result:
print 'Completed: Total lines in table is: {0}'.format(result[0])
else:
print "Unable to count lines in table"

infile.close()
DB_CURSOR.close()
DB.close()
#

if __name__=="__main__":
main()

SAVE & EXIT. Now execute it.

python mactosql.sh

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 :

  • 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

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 🙂

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

January 30, 2018

January 27, 2018

Mikrotik: Schedule script to run in specific day(s) of week only

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


Scenario:

In mikrotik, we want to execute a script which should run on following days at given time. Monday to Friday , 8am Sounds easy enough! but unfortunately Mikrotik doesn’t provides flexible scheduler like windows which let you select days by just clicking. Therefore we have to create some smart function that calculate current day and pass the variable to action part in the script, which than compare variable with its specific days like mon-fri & if it matches then take action ELSE goto Sleep 🙂

(more…)

January 24, 2018

January 23, 2018

Manipulating Date Functions in Mikrotik

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

.

Scenario:

In Mikrotik router , we have a script which is scheduled to run every 5 minutes. It gets in+out bytes for specific interface usage (using firewall counters) and save/add in a file,  when date changes, it simply calculate the total usage and sends report via email to the admin. it’s pretty handy and quick way to get your wan usage with customization. Example of email report via script:

mail for wan total

Problem:

if the script is sending email to the admin about whole day usage at 12:05 am , it will send the current date, whereas the usage is for yesterday. therefore we want to display yesterday day date in the subject. like Current date - 1 day

Solution:

The below script along with separate supporting function script, can manipulate date function in mikrotik. Therefore using this script function we can get yesterday date in our email subject to show correct date.

Surely there must be more simpler methods that I am really unaware of I used this method and it works fine. in Linux date manipulation is way too easy example

TODAY=date -d "yesterday" '+%Y-%m-%d'
But in Mikrotik we dont have such easy functions.

Ok moving forward… We need to make one main function script that can be called by any other script to get the required date manipulation.This will be one time script & will function only if its called from another script.

Script# – For date manipulation function
Name: func_date

# This script is for Mikrotik ROS to manipulate date functions as per requirements , you can modify it as required
# This function script is copied from following mikrotik forum. please see
# https://forum.mikrotik.com/viewtopic.php?f=9&t=127050&p=625209#p625209

# Syed Jahanzaib

: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

Script#2 – Our script that requires Yesterday Date
Name: test

In this example we will get YESTERDAY date.

# You can change the DAYS (-1) as per your requirements like previous or ahead using +)

:local shiftDate [:parse [/system script get func_date source]]
:local DT ([/system clock get date])
:local LASTDAY [$shiftDate date=$DT days=-1]
:put "TODAY Date = $DT"
:put "YESTERDAY date = $LASTDAY"

DRY RUN!

Now run the test script which will result in yesterday date, as showed below …

[zaib@CCR_GW] > /sys scr run test
# OUTPUT RESULT

TODAY Date = jan/23/2018
YESTERDAY date = jan/22/2018

Now you can use such function in any script as per your own requirements…

Regard’s
Syed Jahanzaib

 

January 18, 2018

FREERADIUS WITH MIKROTIK – Part #9 – Restrict user auth from allowed VLAN only

Filed under: freeradius — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 8:48 AM

FreeRadius Core info

restrcited vlan.jpg

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

Disclaimer:
This solution was designed for some specific situation described below. Surely there might be better ways, but sometimes dueto my laziness & dumbness, I select whichever gives required results. You may fine tune it, or modify it as required. I always recommends to read the FR mailing list for any query.

It’s a weird world we live in.
EVERY HUMAN BEING IS BORN FREE BUT YET EVERYWHERE HE IS IN CHAINS … ~

Syed Jahanzaib ~


Scenario:

OP have single Mikrotik CCR as NAS (pppoe server) and Freeradius as billing system. VLAN(s) are configured on Cisco switch for each reseller/dealer area with proper Trunking configured from SW to CCR. OP have few reseller/franchise managers, like Dealer-A, Dealer-B. They can create there own users in freeradius using customized fronted designed in php. All dealers can view/add/edit there own users only.

Sometimes it happens that Dealer-A creates User-ID and provide it to a user/friend who is sitting in Dealer-B network, therefore from Billing perspective its a Loss for Dealer-B.


Requirement:

To prevent users roaming. Every user must be allowed to connect from there respective dealer network Only. [That is ‘allowed-vlan]. User’s MAC address validation is also in place but we know that mac can be cloned easily.


Solution:

p4.jpg

We have a user table which contains a column vlanid. We will add a SQL IF statement which will check the the connecting user NAS-Port-Id & match it with users allowed vlanid in the user’s table.

Users Table Example:

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

mysql> select * from users;
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+------------+-------------------+-------------+----------------------+------------+-----------------+---------------+-------------------+----------+---------+--------------+-------------+--------+-------------+---------------------+
| id | username | password | firstname | lastname | email | mobile | cnic | srvname | srvid | expiration | mac | bwpkg | pool | is_enabled | is_days_expired | is_qt_expired | is_uptime_expired | qt_total | qt_used | uptime_limit | uptime_used | owner | vlanid | createdon |
+----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+------------+-------------------+-------------+----------------------+------------+-----------------+---------------+-------------------+----------+---------+--------------+-------------+--------+-------------+---------------------+
| 1 | zaib | zaib | OK | jahanzaib | aacableAThotmail.com | 03333021909 | 1234567890-1-1 | | 0 | 2018-01-04 | 00:0C:29:B9:D8:A0 | 1024k/1024k | public-pool | 1 | 0 | 0 | 0 | 300 | 38980 | | | manager1 | ether1-LAN | 2018-01-17 16:45:05 |

Now we will add the SQL IF statement that will actually check every incoming Authentication request for matching vlan with users table.

Edit Default Sites-Enabled file,

nano /etc/freeradius/sites-enabled/default

& paste following in `Authorize` Section

if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") {
update reply {
Reply-Message = 'Error: You are not allowed to connect form this VLAN !'
}
update control {
Auth-Type := "Reject"
}
}

Save & Exit.

Now reload Freeradius in Debug Mode (by freeradius -X) & monitor the debugging.

If user will connect from another vlan which does not matches the vlanid in user’s table he will get denied with the message.

Note: For testing purposes . I have added dummy entry in user’s vlanid.

rad_recv: Access-Request packet from host 101.11.11.253 port 34175, id=11, length=119
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 14
NAS-Port-Type = Ethernet
User-Name = "zaib"
Calling-Station-Id = "00:0C:29:B9:D8:A0"
Called-Station-Id = "testppp"
NAS-Port-Id = "ether1-LAN"  # < < < <  Look at this, this is the VLAN id user is coming from, this will be matched with the vlanid we add in the users table
User-Password = "zaib"
NAS-Identifier = "Win"
NAS-IP-Address = 101.11.11.253
# Executing section authorize from file /etc/freeradius/sites-enabled/default
+group authorize {
++[preprocess] = ok

++? if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}")
sql_xlat
expand: %{User-Name} -> zaib
sql_set_user escaped user --> 'zaib'
expand: select vlanid from users where username = '%{User-Name}' -> select vlanid from users where username = 'zaib'
rlm_sql (sql): Reserving sql socket id: 29
sql_xlat finished
rlm_sql (sql): Released sql socket id: 29
expand: %{sql: select vlanid from users where username = '%{User-Name}'} -> ether1-LAN-DUMMY
expand: %{NAS-Port-Id} -> ether1-LAN
? Evaluating ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") -> TRUE
++? if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") -> TRUE
++if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") {
+++update reply {
+++} # update reply = noop
+++update control {
+++} # update control = noop
++} # if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") = noop

Sending delayed reject for request 0
Sending Access-Reject of id 11 to 101.11.11.253 port 34175

# Meaningful Reply Message by zaib
Reply-Message = "Error: You are not allowed to connect form this VLAN !"

Waking up in 4.9 seconds.
Cleaning up request 0 ID 11 with timestamp +31
Ready to process requests.

.

& if the users request matches , he will be granted access (off course after all other checks):)


 

p4-2.jpg

Older Posts »

%d bloggers like this: