Mikrotik IPSEC Tunel with SAP HEC !

---

Following post contains information on how we configured IPSEC tunnel using our mikrotik router (CCR1036) with SAP HANA (High-performance Analytic Appliance) HEC (Hana Enterprice [private] Cloud). I am documenting some basic ideas along with configuration codes for myself, because soon we will be migrating from our decade old mikrotik router to Fortigate NGFW.

HEC is an infrastructure-as-a-service tool that enables companies to run SAP-based operations in a hosted environment. HANA Enterprise Cloud (HEC) was introduced to tr+ansform, migrate and operate mission critical applications in a private cloud environment.

For SAP HEC , We opted for Policy based VPN tunnel (wiht PSK) which was relatively simpler to configure & doesnt requires BGP/ASN which is required in route based vpn option. SAP team shared one sheet with us asking for ipsec related parameters, our local ip subnet, & one subnet for sap end virutal instance that we will be accessing at there end & our wan IP. they shared IPSEC related parameters as per below,

• Phase 1: IKEv2-AES256-SHA384-DHgroup20
• Phase 2: AES256-SHA256-PFSgroup20
• Pre-Shared Key (PSK)
• SAP peer IP address: 1.2.3.4

Unlike other types of VPN’s like pptp/etc, Policy-based IPSec doesn’t have routes. With the Mikrotik, IPSec does not create a virtual interface & you don’t need to add any routes. The packets head for the default route, but the IPSec policy matches the source/dst subnets, and route where it it needs to !

• All VPN/IPSEC parameters must meet exact at both ends i.e HEC & customer end. In beginning, SAP HEC end made some mistakes like incorrect pre-shared key (PSK) was shared with us, our wan ip was not added in there vpn server allowed hosts which cane establish vpn connectivity with there servers. In such cases your local partner ROLE is very important & unfortunatley we didn’t get any active network related support from our local technology parter ! 

Example used in this post for illustration purposes:

• IPSEC paramenters as metnioned above,
• SAP HEC VPN Server IP: 1.2.3.4
• Source LAN IP Pools: 10.1.1.0/24 , 172.16.1.0/24 (our office local subnet, it must be allowed on remote vpn server)
• Destination SAP HEC Private IP Pool on which SAP HEC is hosted: 192.168.50.0/24 (We asked them to use this pool at there end)

---

Mikrotik SAP HEC IPSEC Workingn Config Code:

All the configuration regaridng IPSEC parameters must match exactly at both ends (vendor & customer router) else tunnel will not happen. (In some cases it may be required to request for vendor remote assistance & show them your settings so that they can tune it at there end for matching)

# Add IPSEC profile
/ip ipsec profile
add dh-group=modp2048 enc-algorithm=aes-256 hash-algorithm=sha384 name="SAP HEC IPSEC PROFILE" nat-traversal=no

# Add remote IPSEC server IP address with IKEv2 Support
/ip ipsec peer
add address=1.2.3.4/32 exchange-mode=ike2 name="SAP HEC IPSEC PEER" profile="SAP HEC IPSEC PROFILE"

#Add IPSEC proposal
/ip ipsec proposal
add auth-algorithms=sha256 enc-algorithms=aes-256-cbc lifetime=1d name="SAP HEC IPSEC Proposal" pfs-group=modp2048
# Disable default proposal, as not required
set [ find default=yes ] disabled=yes enc-algorithms=aes-128-cbc

# Add IPSEC pre-shared KEY (provided by SAP or you can also create your own key & shar eit to SAP so that they can set it at there end for your IPSEC profile)
/ip ipsec identity
add peer="SAP HEC IPSEC PEER" secret="STRONG-PRE-SHAREd-KEY"

# Add IPSEC Policies, providing information on what local IP subnet will be able to communicate with remote IP pool, (must be allowed at SAP end as well)
/ip ipsec policy
add dst-address=192.168.50.0/24 peer="SAP HEC IPSEC PEER" proposal="SAP HEC IPSEC Proposal" src-address=10.1.1.0/8 tunnel=yes level=unique
add dst-address=192.168.50.0/24 peer="SAP HEC IPSEC PEER" proposal="SAP HEC IPSEC Proposal" src-address=172.16.1.0/24 tunnel=yes level=unique

# Disable default IPSEC policy, as not required
set 3 disabled=yes

Mikrotik Exemption Rules for IPSEC Tunnels

#Bypass PVN pool from other chains

/ip firewall raw
add action=accept chain=prerouting dst-address=192.168.50.0/24

#Below may not be require if you add it in RAW, but documenting it as we made it work with different scenarios
/ip firewall nat add chain=srcnat action=accept place-before=0 src-address=10.1.0.0/24 dst-address=192.168.50.0/24
/ip firewall nat add chain=srcnat action=accept place-before=0 src-address=172.16.1.0/24 dst-address=192.168.50.0/24

# Exempt SAP HEC Pool from mikrotik local NAT rule
/ip firewall nat
add action=src-nat chain=srcnat comment="Default_NAT_Rule_for_Allowed_Users with Exempting IPSEC Tunnel" dst-address=!192.168.50.0/24 out-interface=WAN1 src-address-list=Net_Allowed_users action=masquerade

---

TIPS:

Following are few tips that I learned in a hard way !

• If you have multiple local subnets, you need to create POLICIES accordingly , as shown in the example in this guide (code example). Level of every policy should be set “unique” instead of “required”.
  Also these pools must be allowed at remote server. In our case , only one pool was allowed at remote server in beginning & this created many days in wall banging efforts, as a workaround we created a src-nat rule to NAT our other local subnets to src-nat via allowed pool ! example:

/ip firewall nat
add action=src-nat chain=srcnat comment="Workaround - Jugaaroo Rule to make IPSEC Tunnel Work / SJZ" disabled=no dst-address=192.168.50.0/24 src-address=172.16.1.0/24 to-addresses=\
x.x.x.x (my router lan subnet ip which is allowed on remote site)

• All VPN/IPSEC parameters must meet exact at both ends i.e HEC & customer end. In beginning, SAP HEC end made some mistakes like incorrect pre-shared key (PSK) was shared with us, our wan ip was not added in there vpn server allowed hosts that cane stablish vpn connectivity with there servers. In such cases your local partner ROLE is very important & unfortunatley we didn’t get any active network related support from our local technology parter ! 
• You also need to exclude the IPSec subnets from the masquerade natting rule. Ensure you EXCLUDE the detination SAP HEC Pool from *any* NATTING rule (show in code example)
• If you clients can ping the remote servers, but your mikrotik itself cannot, then You can fix it either using srcnat, or with fake routes where you set pref-src. It doesn’t matter where such route points to, it won’t be actually used. It can be e.g. empty bridge.  Route with pref-src is better, because it works for everything. E.g. if you do traceroute from one LAN to another, there will by default be a hole where remote router should be.
• Seond option, If you are pinging from the Mikrotik itself, make sure you set src-address so that it matches the policy example
  ping 192.168.50.10  src=10.1.1.1 (10.x is IP from allowed pool)
• Try disabling and re-enabling the identity, as adding policies on the fly behaves funny in some RouterOS versions.

IPsec has as two modes, one is the Tunnel mode and the second is the Transport Mode.

Tunnel mode is used in site to site VPNs, between Gateways in simple words and is the default mode while Transport mode is used for client to site VPNs or end to end, between a computer and a Gateway…

Now, the Tunnel mode adds a New IP Header in front of the IP header of the originating packet…
In transport mode no new IP header is added, so the IP stays the same… Unless NAT is perforfmed…

If you have transport mode IPSec (e.g. for L2TP/IPSec), decrypted packet (L2TP) will have same addresses as encrypted (unless it’s changed by NAT). If you have tunnel mode IPSec (e.g. LAN to LAN tunnel), decrypted packet will have the source address of remote device in LAN (it it came from it) and destination address will be device on local LAN, while encrypted packets will have remote router as source and local router as destination.

IPsec policy kicks in after all other packet processing, including routing and NAT, has been done and the packet is just about to be sent out via an interface. So some route must match the dst-address of the packet, but it is not important which one in particular it is. And to be recognized by the policy, the packets’ src address must not be changed by src-nat if the route sends them out via WAN with src-nat; on the other hand, you may want to src-nat them intentionally in some scenarios.

---

References: Mikrotik Forum

 

Regard’s
Syed Jahanzaib

Story about Mass migration of SLD / VLAN’s / IP Pool / Wifi Mesh Loop / Rectification of Bonjour mDNS using Parallel Topologies

---

---

---

---

---

This post is a quick reference notes for myself on how I managed to rectify some of long pending IT related issues which were left by previous network team, including AD Domain S.L.D migration to FQDN / Public IP scheme being used at Private LAN & its migration to multiple /24 Private IP Pools, Single default VLAN-1 migration to Different VLAN’s with smaller subnets.

The number of desktop/laptop/devices were in several hundreds. Downtime was not an option, therefore we managed to complete the task using parallel topologies for each segment.

Our existing network was running on default VLAN with /8 flat subnet, having each & every device on default VLAN-1 generating enormous number of broadcasts flooding causing network delays/timeout. There was no control on any switch segment, All switch edge port were set on Default roles making situation even worst from Spanning-Tree protocol flooding perspective. Above all like Cherry on the TOP, existing admin used public IP scheme with /8 subnet & many websites which were on this subnet over the internet, never worked on our network.

To over come these issues without any Downtime or disruption, I decided to sort issues on a one by one basis by creating parallel network, using below methods

---

SLD/FQDN Migration:

• On same network, I added new FQDN A.D Domain, Established two way trust between both domains. Then using Microsoft migration tool (ADMT ), I start migrating users (one by one, targeted activity ) from old SLD to FQDN. More details are documented in another article.

Short Notes for Active Directory SLD to FQDN Cross Forest Migration – Using ADMT tool

---

*  Network Related *

We have Cisco L3 Stacked switch at data center & all departmental L2 switches are connected via 10G Fiber TRUNK links. To convert the existing Public IP scheme to Private without having any downtime, I I designed different VLAN’s scheme with smaller /24 private IP’s pool on per department basis, Created there SVI’s on main core switch stack. Using IP helper commands. I also created different vlans for management example different vlan for switch / access points / devices / servers & Users. I created DHCP relay pointed towards our domain controller. As I have to create parallel environment to avoid any downtime or any communication disruption, I didn’t changed any server’s device IP, rather On all server’s I create routes using ROUTE cmd) pointing to switch gateway (SVI) so that all devices can two way communicate with each other either its public or private.

---

Segmentation of different VLAN’s

Slowly gradually, we changed the Server’s/users/devices VLAN form 1 to new vlan scheme, & changed the DNS entries as well (Most of them auto updated there DNS entries at DC DNS upon rebooting or using Group Policy to enforce DNS registration every 30 minutes). New vlan users were able to communicate with older vlan-1 or x users because we configured CLASSLESS for each DHCP pool pointing to Core switch gateway using there corresponding gateways (SVI’s IP’s at Core L3 Switch).

Some Common Tuning we performed resulted in increased reliability of overall network

• Most important set root bridge priority to 1 on L3 Core switch by setting the bridge priority to 1 then set all the others  switches to higher number. This will ensure the STP is built correctly
• Subdivide the network into multiple VLAN’s & smaller logical subnets e.g. Servers, Users, Management vlans for switch n access points, devices like printers , time machines & NVR cameras, departmental vlans etc with each having a /24 e.g. 192.168.1.0/24, 192.168.2.0/24 and so on
• On your core switch allocate SVI’s per required vlan
• ensure each device can talk to each other using intervlan routing [exception to those who required firewalling/security]
• Make all trunk uplinks 802.1q trunks [doesnt requires at new cisco IOS]
• TAG / Associate ports on all switches with the appropriate vlans
• Ensure all ports roles are defined properly, example edge / trunk ports
• User connected EDGE port should be in access mode, & Portfast enabled along with BPDU enabled.
• Disable all UNUSED ports (or make default sandbox VLAN for all unused ports & assign those ports with this sandbox VLAN, to ensure that unknown devices inserted in those ports should remain in sandbox VLAN
• Make sure your Wifi APs are not meshing or bridging
• I added all devices in DNS entries like printers etc , and at user end, added the required devices by DNS name, so that next time if any changes are required, modify the DNS entry only & no need to reconfigure the device at user end.

Some DRY theory on ACCESS/TRUNK Mode

• An access port can have only one VLAN configured on the interface; it can carry traffic for only one VLAN. An access port transmits packets on only one VLAN (traffic is not tagged on this type of port). Portfast feature causes a switch port to enter the spanning tree forwarding state immediately, bypassing the listening and learning states. Portfast on switch ports connected to a single workstation or server allows those devices to connect to the network immediately, instead of waiting for the port to transition from the listening and learning states to the forwarding state.
• Edge ports are configured such that they immediately go to the forwarding state. However, this does not mean that there is no loop protection. It is assumed that edge ports will connect to end devices, and thus it is convenient for them to go directly to the forwarding state. However, someone can try to plug in a switch on such a port and can try to become the root bridge or may connect to multiple ports and create a loop. That’s where you should use BPDUGuard. On all edge ports, BPDUGuard should be enabled so that as soon as such a port receives a BPDU, it will go into err-disabled state, thus preventing an L2 loop. Now keep in mind that for RSTP, if you don’t enable BPDUGuard, and a BPDU is received on an edge port, the edge port simply loses its edge port status.

---

Windows / Linux Route CMD’s for OS with Static IP

At user end, all routes were distributed via windows DHCP Classless routes which worked transparently fine. But server OS with static IP I had to provide proper routes according to our network. Example we have two gateways for internet at LAN, one is SANGFOR IAM for end users, second is Router/NGFW for server ends. Some times we have to route user/server at one or other gateway.

First get the Interface number via ROUTE PRINT CMD

• route print

(Note down the interface name, example Interface number is 8)

Now add the appropriate gateway for intervlan routing & internet routing as well

*** WINDWOS OS / ADD PERMANENT ROUTES using ROUTE command

Note: -p syntax with route cmd adds the route on permanent basis (in registry)

# Below is an example for SERVER which is on 172.16.2.x VLAN , below is for INTERVLAN Routing for local subnet’s intervlan routing . 172.16.2.1 is the CORE Switch Gateway which have SVI’s configured,

route add -p 172.16.0.0 MASK 255.255.0.0 172.16.2.1 metric 1 IF 8
route add -p 192.168.0.0 MASK 255.255.0.0 172.16.2.1 metric 2 IF 8
# Below is for Internet access , 2 Gateways with priority pointing to Sangfor & NGFW Respectively (NGFW route is added as some port forwarding is done which is routed via NGFW)
route add -p 0.0.0.0 MASK 0.0.0.0 172.16.2.2 metric 3 IF 8
route add -p 0.0.0.0 MASK 0.0.0.0 172.16.2.6 metric 4 IF 8

*** LINUX / ADD PERMANENT ROUTES in interfaces FILE [Ubuntu ver 16 or below]

• For older version of Linux (Ubuntu ver 16 or below) I added below in interfaces file

up route add -net 172.16.0.0 netmask 255.255.0.0 gw 172.16.2.1
down route del -net 172.16.0.0 netmask 255.255.0.0 gw 172.16.2.1
up route add -net 192.168.0.0 netmask 255.255.0.0 gw 172.16.2.1
down route del -net 192.168.0.0.0 netmask 255.255.0.0 gw 172.16.2.1

• For new version of Ubuntu ver 18 & above, I used below [sample file shown below]

cat /etc/netplan/00-installer-config.yaml

network:
ethernets:
ens160:
dhcp4: false
addresses: [192.168.0.28/24]
nameservers:
# DNS server is on different VLAN
addresses: [192.168.1.71,192.168.1.72,8.8.8.8]

# STATIC ROUTES for INTERVLAN ROUTING, GW pointing to L3 Core Switch corresponding SVI
routes:
- to: 10.11.0.0/16
via: 192.168.0.1
- to: 10.10.0.0/16
via: 192.168.0.1
# FOR INTERNET , GW POINTING TO SANGFORIAM OR NGFW
- to: default
via: 192.168.0.6
version: 2

---

DHCP for Different VLAN Users

 

On Domain controller (Default route pointed towards L3 Core Switch SVI) I created multiple IP pool for corresponding VLAN users. At departmental L2 switches, I added corresponding VLAN’s & with the help of IP Helper at core switch, users get IP from the Domain controller DHCP server via there corresponding vlan pool. One by One all departments moved to different Vlan’s with smaller IP pool  resulting in dramatically decline in broadcast traffic. This improve network connectivity reliability at a greater extent.

 

---

IP Helper not working across VLAN’s

I got stucked at one point where ‘IP helper’ was not forwarding the other vlans (like vlan 10/11/12) users dhcp pkts to domain controller dhcp residing on default vlan-1 despite all settings seems correct, I contacted few Cisco / VM experts & they remotely checked in cisco & vm config in details & yet no one  was able to solved it. At Core switch there was this “no service dhcp” set & it was the culprit , as soon as I set it to service dhcp, & Alhamodlillah! all got sorted !

One example of IP helper at Wireless (with mDNS support) VLAN interface on L3 Core Switch

interface VlanXX
description WIFI_XXX-MOBILE-VLAN
ip address 10.0.0.1 255.255.255.0
ip helper-address 192.168.0.1
service-routing mdns-sd
service-policy-query querier 60
service-policy mypermit-all IN
service-policy mypermit-all OUT

---

VLAN’s in VMWARE ESXI

Configuring VLAN’s in ESXI was easiest part. The only thing at switch level was to configure TRUNK at which ESXI is connected to. On esxi server, I created new network (VLAN) & tag the required vlan to desired VM guest & Done.

Allah Shuker!

---

Ref:

Howto Add VLAN in ESXI Server

Following is quick short notes on howto add VLAN in ESXI Server

• Login to ESXI (I am using VCSA to manage all esxi servers).
• Now Goto Configure
• Networking > Virtual Switches (NEXT)
• Add Networking (NEXT)
• Select ‘Virtual Machine Port Group for a Standard Switch’ (NEXT)
• Select ‘Select an existing standard switch’ & click on BROWSE & select vSWITCH0 (NEXT)
• In next window (Connection settings) at NETWORK LABEL , type suitable name like VLAN-100 / & under VLAN ID tab, type in the required VLAN ID example 100 (NEXT)
• Click on (FINISH).

Done. Now you can assign this vlan to your desired VM Guest in its network adapter settings.

---

UniFi Access Points with Multiple SSID’s & VLANS

We have many wireless access points installed at various locations & all controlled centrally by UniFi controller software application. After addition of VLAN’s we decided to remove the default vlan1 SSID, & introduce new SSID based on targeted audience like XYZ-Corporate , XYZ-Mobile , XYZ-Guest & each SSID is tagged with different VLAN’s to control the users based on SSID type. The setup was straight forward. First We create separate VLAN for access points management e.g: VLAN100 & at all access points we changed the ips with vlan100 pool & set there default gateway to vlan-100 SVI configured ta Core Switch, Also we changed there management VLAN to VLAN-100.

• Sample of IP/MGT VLAN setting on per AP Basis

• Adding new VLAN ID in UniFi Controller Application

To add VLAN in UniFi Controller ,
Goto SETTINGS  > NETWORKS > & create new VLAN with VLAN-ID number,

• Adding new SSID in UniFi Controller Application

Now to ADD SSID in UniFi Controller Application& tag with NEW VLAN-ID,
Goto SETTINGS  > WiFi > Create NEW Wifi , Now enter the name of SSID like XYZ-Corporate & in Network select the desired VLAN you created in earlier step

Warning: After applying any setting that needs to be synced to all access points in the group, traffic disruption & timeout to access points may occur upto 1or 2 minutes. Therefore perform such operations Only in Off-Peak hours.

---

UniFi Access Point – Trunk Port Config at Cisco Switch PORT

At Cisco switch where access points ethernet cable is attached. We did below config for that port,

interface GigabitEthernet1/0/20
description UniFi_AP_Finance
switchport mode trunk

This enabled to carry all vlans including AP Mgt / Different Vlan’s based on various SSID’s [each SSID tagged with different VLAN via Unifi Controller application]

---

UniFi Wireless Meshing Causing Loop/Broadcast

We noticed that whenever we modify any settings which requires syncing with All AP’s, then one by one AP’s freezes packet transmission connectivity & then get back online few 15-20 seconds but few access points uplink gets there uplink connectivity with another AP wireless (Meshing). This was also creating the loop broadcast. Disabling WIRELESS MESHING in Unifi Controller application & then readopting the AP’s reduced such events.

---

SANGFOR VLAN

To Provide internet access via sangfor to all vlan users, we added corresponding VLAN’s in SANGFOR IAM device, Each vlan sub-interface with corresponding IP to be used as gateway at corresponding VLAN users (all via DC DHCP)

To add corresponding VLAN’s in SANGFOR,

Goto System > Network / Deployment / Settings > & under LAN interface Page add as per below

10/10.10.10.3/255.255.255.0

Whereas 10/ is the cisco VLAN number, and then the IP of this sub interface &  then the subnet

Warning: After modifying any network related settings, SANGFOR Network services will be restarted, & may cause traffic disruption, therefore perform such actions in off-peak hours.

---

TIPS:

Bonjour (mDNS) discovery across different VLAN’s (Wired/WiFi)

After segregated network into smaller subnets along with separate vlans for wired/wifi users/devices etc, users were not able to *cast/discover* LCD TV /  MFD Printers if both are on different vlans. (they works fine if the device & user are on same vlan/subnet).  If wired user on vlan2 tries to connect with LCD (on wifi)  on vlan3, he cannot. if they are on same vlan they can connect fine. This is because broadcast doesnt’ works across different vlan.

In UniFi Controller, there is an option of enabling mDNS which is a service provided by a Unifi router like USG, UXG Pro or UDM. but since we didn’t had any UniFi router/switch therefore UniFi mDNS didn’t had any mDNS functionality and it didn’t worked. We had to configure the service to repeat broadcasts across VLANs using Cisco switches.

The solution was to enable the mDNS ‘service discovery’ at Core L3 Switch.

At core switch where all SVI’s are configured, I allowed mDNS service on particular VLAN’s (wired/wifi) using below CMD’s & the problem got sorted out.

interface VlanXX
description WIFI_MOBILE-VLAN
ip address X.X.X.X 255.X.X.X
ip helper-address X.X.X.X
service-routing mdns-sd
service-policy-query querier 60
service-policy mypermit-all IN
service-policy mypermit-all OUT
end

---

I will keep updating this article as I get free time

Regards
Syed Jahanzaib

CallMeBot: Sending Alerts to Various Messaging Apps using APi

---

At multiple ISP’s or networks, I use either GSM Modems or 3rd party SMS API to send various information / alerts messages to admin or users , & it involves minor cost on a per sms basis or as bundle. Another method to send messages on WHATSAPP & its FREE , very simple to use CALLMEBOT APi’s. Its a online FREE service via which you can send basic messages to multiple messaging services like WhatsApp / messenger / Signal / Telegram apps.  The only caveat is that you cannot send messages to EVERY contact. The receiver contact must have the callmebot API KEY , which he can easily get via adding callmebot number in there contact list & send particular message & he will get it instantly. so EACH API EKY is tagged with that particular mobile number only. Once you know the key & its tagged mobile number, you can send message to that contact using the API_KEY & his mobile number using various methods.

In this guide we will be using callmebot API to send various test message to admin WhatsAPP number. Follow the below steps …

• First, You need to get the API key form the CallMeBot. Add the phone number +34 644 91 96 80 into your Phone Contacts. (Name it it as you wish like CallMeBoT / My_Alerts etc )
• Using WhatsApp, Send this message to this contact

I allow callmebot to send me messages

• once it is sent, Wait until you receive the message like “API Activated for your phone number. Your APIKEY is XXXXXX” from the bot. Note down the APIKEY
  Note: If you don’t receive the ApiKey in 2 minutes, please try again after 24hs.
• The WhatsApp message from the CallMeBot will contain the API key needed to send messages using various API.
• Now You can send text messages using the API after receiving the confirmation.

Following are few methods, which I am using to send various informational/warning/critical messages to Admin whatsapp

---

Send message using Browser HTTP APi:

Using browser, you can send message by using below

(As always, change the Phone/APiKey)

https://api.callmebot.com/whatsapp.php?phone=+923333021909&apikey=123123&text=Fiber+Link+is+Down

Example Message:

---

Linux Examples:

CMD: on Terminal/shell

(As always, change the Phone/APiKey)

curl --insecure 'https://api.callmebot.com/whatsapp.php?phone=+923333021909&apikey=123123&text=Linux+Msg+Test+z'

Example Message:

---

Linux Bash Script Example using separate Text file as attachment

(As always, change the Phone/APiKey)

#!/bin/bash
# DAILY SMS SCRIPT FOR whatsapp MSG Testing
PATH=/opt/someApp/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
# Script by Syed Jahanzaib / aacable@hotmail.com
# https://aacable.wordpress.com
# Version 2.0 -
# Created in year 2013
# Last modified 20-NOV-2017
set -x
logger DAILY 8 am sms executed for whatsapp testing
#ntpdate -u 91.189.91.157
COMPANY="Independence-Day"
TMP="/tmp/dailysms.txt"
> $TMP
# CHAGNE BELOW API KEY AS PER YOUR'S
API_KEY=123123
# Jahanzaib Cell # CHANGE CELL AS PER YOUR's
CELL1="923333021909"
UPTIME=`uptime | awk -F'( |,|:)+' '{if ($7=="min") m=$6; else {if ($7~/^day/) {d=$6;h=$8;m=$9} else {h=$6;m=$7}}} {print d+0,"days,",h+0,"hours,",m+0,"minutes"}'`
DATE=`date`
FOOTER="Powered By Jz"
# Print total and each vlan users
MSG="$COMPANY - Msg Test !
Daily Msg Test @
UPTIME = $UPTIME
$DATE
$FOOTER"
echo "$MSG" > $TMP
cat $TMP

# Finally SEND MSG to whatsapp using CALLMEBOT
curl --insecure "https://api.callmebot.com/whatsapp.php?phone=+$CELL1&apikey=$API_KEY" -G --data-urlencode text@$TMP

rm $TMP
# THE END

Example Message:

---

Mikrotik RouterOS Example:

Terminal:

(As always, change the Phone/APiKey)

/tool fetch http-method=get mode=https url="https://api.callmebot.com/whatsapp.php\?&apikey=123123&phone=+923333021909&text=Mikrotik+Router+Whatsapp+Msg+Example+SJZ"

Example Message:

---

Mikrotik DUDE (for Windows) Example:

First download the windows base WGET utility, copy it in any local folder of dude server. & now add notification like this

On DUDE App, Goto Notifications, Click on + ICON to add new notification, a new popup will appear, fill it as per below

Name: Whatsapp2Zaib
Enabled: Ticked
Type: Execute on Server
& in Command Window section, use below cmd

(As always, change the Phone/APiKey)

C:\wget\wget.exe --no-check-certificate "https://api.callmebot.com/whatsapp.php?phone=+923333021909&apikey=123123&text=Service [Probe.Name] on [Device.Name] is now [Service.Status] ([Service.ProblemDescription])"

Example Message:

---

Hope it Helps !

---

---

Regard’s
Syed Jahanzaib

NAT types for console | a horror tale for gamers behind NAT

First of all, **What is NAT?**

Network Address Translation (NAT) is designed for IP address conservation. It enables private IP networks that use unregistered IP addresses to connect to the Internet. NAT operates on a router, usually connecting two networks together, and translates the private (not globally unique) addresses in the internal network into legal addresses, before packets are forwarded to another network.

As part of this capability, NAT can be configured to advertise only one address for the entire network to the outside world. This provides additional security by effectively hiding the entire internal network behind that address. NAT offers the dual functions of security and address conservation and is typically implemented in remote-access environments.

Basically, NAT allows a single device, such as a router, to act as an agent between the Internet (or public network) and a local network (or private network), which means that only a single unique IP address is required to represent an entire group of computers to anything outside their network.

---

Moving on, the 3 NAT types, when concerning gaming consoles/PCs, PS3/PS4, or the Xbox 360/Xbox One, are

1. Open (Type 1)
2. Moderate (Type 2) &
3. Strict NAT (Type 3)

NAT1 is a direct connection to the internet; all ports are accessible, with no port forwarding rules required. Ultimately, an Open/Type 1 NAT will provide the best connection quality whereas Moderate and Strict NAT restrict the connections between a gaming console or PC. If your internet connection has a public IP address (non-RFC1918, non-RFC6598) on the exterior interface of your home router, you should be able to have your PS4 run in NAT2 mode. If you control the port forwarding on your home router, you should be able to get the PS4 to run in NAT1 mode, even on an internal RFC1918 address.

NAT2 is a single layer of public-to-private conversion, and requires assisted port forwarding to achieve inbound connections to the PS4. The Moderate, Type 2 NAT, as well as Strict, Type 3 NAT, limits the connections that can be created between your gaming console or PC and someone else’s gaming consoles or PCs. Users with Moderate NAT, or type 2 NAT, are only able to connect with other users also having a Moderate NAT type, type 2, or an Open NAT Type, type 1.

NAT3 is two layers of conversion, and usually involves a carrier-grade-NAT device at the ISP, as well as a NAT device at the home, making it nearly impossible to achieve direct inbound connections to the PS4; in NAT3 mode, only server-assisted connections are possible, with each PS4 establishing an outbound connection through the two layers of NAT devices, with centralized servers mediating the PS4-to-PS4 communication. Users with Strict/Type 3 NATs can only connect with other users using an Open/Type 1 NAT.Furthermore, at a smarter NAT 3 setup, If you see an address in 100.64.0.0/10 on the outside interface of your home router, you’re out of luck; you’re in NAT3 territory, may GOD have mercy on your gameplay 🙂

 

More to come from the Mikrotik Side …

Radius | Disconnect users after service change / user disable

---

Note for self

In older version of radius manager, when OP disables any user account or change user service package , those changes doesnt take effect until the user disconnect/reconnect. Sometimes user remains connected for days. Back in those days , we made a workaround by creating a mysql trigger and script combination. 

Assuming you have fully functional radius working, along with root access to DB. Save this trigger.sql and import it in mysql radius DB.

Example: mysql -uroot -pMYPASS radius < trigger.sql

Following trigger will check for rm_users table changes, and if it found any changes in the users disable/enable or srvid changes, it will add entry in the rm_kickuserstable & our schedule script will pick the data from there and will act accordingly …

MYSQL > kickuser_trigger

-- Host: localhost Database: radius
-- Server version 5.5.54-0ubuntu0.12.04.1
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
/!50003 CREATE/ /!50017 DEFINER=root@localhost/ /*!50003 TRIGGER kickuser_trigger BEFORE UPDATE ON rm_users
FOR EACH ROW BEGIN
IF NEW.enableuser <> OLD.enableuser THEN
INSERT INTO rm_kickuser (datetime, username, msg) VALUES (NOW(), new.username, new.enableuser);
END IF;
IF NEW.srvid <> OLD.srvid THEN
INSERT INTO rm_kickuser (datetime, username, msg) VALUES (NOW(), new.username, new.srvid);
END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
---- Dumping routines for database 'radius'
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2021-07-02 8:24:05

rm_kickuser TABLE

Save following & import it in radius db 

Example: mysql -uroot -pMYPASS radius < rm_kickuser_SQL_DB_Creation.sql

root@radius-zaib:/temp# cat rm_kickuser_SQL_DB_Creation.sql

-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 13, 2016 at 10:32 AM
-- Server version: 5.5.46
-- PHP Version: 5.3.10-1ubuntu3.21
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `radius`
--
-- --------------------------------------------------------
--
-- Table structure for table `rm_kickuser`
--
CREATE TABLE IF NOT EXISTS `rm_kickuser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`username` varchar(64) NOT NULL,
`msg` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
--
-- Dumping data for table `rm_kickuser`
--
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

---

the Script !

Now create the script & schedule it to run every 1 minutes

kick_user.sh

#!/bin/bash
#set -x
# Following script is made specifically for Dmasoftlab radius manager 4.1.x
# When OP disables any user or change service, it will kick the user so that either disconnects, or his package changes on reconnect
# it requires custom trigger on rm_users table, this script will be schedule to run every minute
# Created: 25-MARCH-2019
# Tested on Ubuntu OS Only
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
#################
# CHANGE these
HOSTNAME=`hostname`
SQLID="root"
SQLPASS="MYPASSWORD"
NAS_COA_PORT="1700"
DB="radius"
SRV="mysql"
USR_TABLE="rm_kickuser"
MNT="1"
RADCLIENT="/usr/local/bin/radclient"
#################
#DATE TIME FUNCTIONS
currenttime=$(date +%H:%M:%S)
# Add Script start execution entry in the /var/log/syslog to see if the script got executed or not
logger "Kick Disabled/Enabled & Service Change - User poller script Started @ $currenttime by the CRON scheduler ... Powered by SYED.JAHANZAIB"
echo "- Script Start Time - $currenttime"
echo "- Checking Disabled/Enabled Users in $USR_TABLE table ..."
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
#Table which contain main users information
TMPUSRINFO=/tmp/_users_list.txt
TEMP="/temp"

# Checking if /temp folder is previously present or not . . .
{
if [ ! -d "$TEMP" ]; then
echo
echo "- INFO: $TEMP folder not found, Creating it now to store logs ..."
mkdir $TEMP
else
echo -e "- INFO: $TEMP folder is already present to store logs."
echo
fi
}

KICKUSER_LIST_FILE=$TEMP/kick_users_list.txt
SYSLOG="/var/log/syslog"
> $TMPUSRINFO
# Check if table exists
if [ $($CMD \
"select count(*) from information_schema.tables where \
table_schema='$DB' and table_name='$USR_TABLE';") -eq 1 ]; then
echo "- INFO: $USR_TABLE Table exists ..."
else
echo "- WARNING: $USR_TABLE Table does not exists ..."
fi
# pull user record
$CMD "use $DB; select username from $USR_TABLE WHERE datetime >= NOW() - INTERVAL $MNT MINUTE;" >> $TMPUSRINFO
if [ ! -s $TMPUSRINFO ]
then
endtime=$(date +%H:%M:%S)

echo "
- INFO: No User to KICK found in DMA RADIUS MANAGER TABLE '$USR_TABLE' , Sending EXIT signals ...

- Script Ends Here...
- EXITING peacefully...
- Script End Time - $endtime
"
exit 1
fi
# Apply Count Loop Formula while deleting first line which have junk text
num=0
cat $TMPUSRINFO | while read users
do
num=$[$num+1]
username=`echo $users | awk '{print $1}'`
USER_IP=`echo $users | awk '{print $2}'`
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$username' AND acctstoptime is NULL;"`
NAS_IP=`$CMD "use $DB; select nasipaddress from radacct where username ='$username' AND acctstoptime is NULL;"`
NAS_SECRET=`$CMD "use $DB; select secret from nas where nasname = '$NAS_IP' ;"`
if [ -z "$ACCTSESID" ]; then
echo "User Found to KICK: USER: $username , BUT USER IS NOT ONLINE, no action is requiroed ..."
else
# Print Info on screen
echo " User Found to KICK: USER: $username , IP: $USER_IP, ID: $ACCTSESID, NAS: $NAS_IP @ $currenttime ... KICKING him now ..."
logger " User Found to KICK: USER: $username , IP: $USER_IP, ID: $ACCTSESID, NAS: $NAS_IP @ $currenttime ... KICKING him now ..."
echo " User Found to KICK: USER: $username , IP: $USER_IP, ID: $ACCTSESID, NAS: $NAS_IP @ $currenttime ... KICKING him now ..." >> $KICKUSER_LIST_FILE
#in below cmd, I am using SSH base method to kick the user because there were some issues in routing & NAS was not accepting radclient packets, you may use the radclient method which is better approach
ssh -p 22 admin@192.168.0.1 /ppp active remove [find name=$username]
#for pppoe , use below
#echo user-name=$username | radclient -x $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
#for hotspot, enable following line
#echo Framed-IP-Address=$USER_IP | radclient -x -c 1 $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
fi
done
# once done, we should delete the tmp files to clear the garbage
rm $TMPUSRINFO

ADDING CRON Entry

*** Schedule the script to run every minute

crontab -e

*/1 * * * * /temp/kickuser.sh > /dev/null 2>&1

Result:

root@radius-zaib:/temp# ./kickuser.sh

- Script Start Time - 08:43:58
- Checking Disabled/Enabled Users in rm_kickuser table ...
- INFO: /temp folder is already present to store logs.
- INFO: rm_kickuser Table exists ...
- INFO: No User to KICK found in DMA RADIUS MANAGER TABLE 'rm_kickuser' , Sending EXIT signals ...

- Script Ends Here...
- EXITING peacefully...
- Script End Time - 08:43:58

 

---

Regard’s
Syed Jahanzaib

Modifying Expiration Time in Dmasoftlab Radius Manager

This post is published as a personal reference, It describes a method via which you can modify the user account expiration default time of 00:00:00 to some other time, so that middle night disconnection can be avoided & user can get some time in official hours to recharge there account. with traditional FREERADIUS we can modify the disconnection in RADCHECK table, but since DMA doesn’t defines the expiration there & uses its own authentication module which checks for the user expiration date in RM_USERS table, therefore we have to make little modification to facilitate this option.

Another option is to create TRIGGERS, so that whenever a new user is created, it should modify the time instantly, or add the user name in separate table, and using predefined bash script which can monitor the table and perform action accordingly. lots of options can be opted.

---

As asked by few network OP’s who are using dmasoftlab radius manager as there billing system & performs manual recharge after getting payment from the users by door to door collection, One of the most annoying issue is the expiration time at which users get disconnected dueto expiration limit expires. By default when a user is created his time is entered in following format

2021-03-20 00:00:00

As a result, as soon as date changes at 00:00:00 hours, the user will be disconnected from the system. which means in the middle of night. Users starts calling OP help desk & most of the time, its difficult to recharge accounts in mid night.

Therefore its better to change the expiration time to happen in official working hours so that both user & OP can have some time to recharge the accounts within office hours.

To automate this process, create a bash script & schedule it to run at 11:50 pm daily. This script will change all users expiration HOURS to your modified time.

SCRIPT !

Create TEMP folder / script file along with executable permission

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

& paste following contents, make sure to change MYSQL user/password & required expiration hours

#!/bin/sh
# set -x
# BASH base script to change EXPIRATION hours in DMA RADIUS Manager RM_USERS table
# to modify users expiration disconnection time so that middle night disconnection can be avoided
# You can schedule this script to run every XX minutes/hours
# example : in cron use below line , means run at 11:50pm
# 50 23 * * * * /temp/expmod.sh
################################################
# By Syed Jahanzaib / aacable at hotmail dot com
# CREATED on : 20th-March-2021
################################################

# MYSQL related. MAKE SURE TO CHANGE THESE to MATCH YOUR LOCAL's
SQLUSER="root"
SQLPASS="zaib1234"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLUSER --skip-column-names -s -e"
DB="radius"
# DMA related, below is 8pm. change timings as per your requirements
DEFAULT_TIME="00:00:00"
NEW_EXP_TIME="20:00:00"
COLUMN_NAME="expiration"
# R.M Table which contain users expiration information (in freeradius, we use Expiration attribute in radcheck,
# But DMA uses its own authentication module to validate users details from the rm_users table
USER_TABLE="rm_users"
# Date Related
DATE=`date`
TODAY=$(date +"%Y-%m-%d")

# Start execution
# Modify the 00:00:00 hours to suite yours local time, I have used 8pm timings as an example
$CMD "use $DB; UPDATE $USER_TABLE SET $COLUMN_NAME = DATE_FORMAT(expiration, '%Y-%m-%d $NEW_EXP_TIME');"

# or you can use single line code here in mysql directly or by $CMD
#UPDATE rm_users SET expiration = DATE_FORMAT(expiration, '%Y-%m-%d $NEW_EXP_TIME');"

# ECHO on screen and also LOG in /var/log/syslog (for ubuntu)
echo "DMASOFTLAB RADIUS MANAGER - User expiration HOURS now changed from $COLUMN_NAME to $NEW_EXP_TIME - Script executed successfully @ $DATE"
logger "DMASOFTLAB RADIUS MANAGER - User expiration HOURS now changed from $COLUMN_NAME to $NEW_EXP_TIME - Script executed successfully @ $DATE"
#Script Ends here

CRON Scheduler:

You can schedule it to run at 23:50 hours daily (this is the default time when dma expires account program runs, so we will modify the cron schedule to modify expiration time just before DMA program runs …

to edit CRON (Task Manager for linux), edit it by using below CMD

crontab -e

Now add below (you can modify it to to run at single time or multiple times as per your need, every 50 minutes of every hour, or likewise)

50 23 * * * /temp/expmod.sh

---

Regard’s
Syed Jahanzaib

Possibilities: Mikrotik PPP Disconnection/Yellow Sign Problems

---

Disclaimer! This is important!

Every Network is different , so one solution cannot fit/applied to all. Therefore try to understand logics & create/modify the solution as per your network scenario. Do Not follow copy paste blindly.

My humble request is that kindly donot consider me as 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 try to share tips that worked for me.

Tips posted here are based on personal experiences which I faced/sorted at various networks locally/internationally. It is requested to kindly contribute your valuable experience & any tips to help others.
Sharing is Caring …

Regard’s
Syed Jahanzaib~

---

PPP Common Problems

From some time we were getting following complains from few ISP’s regarding

• Few websites (like banking) not opening if user is connected via pppoe only
• User pppoe dial stuck , not able to reach to mikrotik pppoe server
• User pppoe connectivity frequent/intermittent disconnection/termination
• User pppoe dialer is connected but yellow mark at user device/workstation , No internet

 

Try to diagnose the issue one by one by below tips

1. For few websites not working on pppoe clients only issue , try to add following rule & test
   /ip firewall mangle
   add action=change-mss chain=forward new-mss=clamp-to-pmtu passthrough=yes protocol=tcp tcp-flags=syn
2. Mikrotik RouterOS Firmwares plays very important roles in the stability in various segments, Try LONG-TERM release. Sometimes upgrading/downgrading rectifies issues without modifying any configuration. Read Mikrotik Forums to see if other users are having similar issues on particular version.
3. Cheap wifi routers at client end example TPLINK/TENDA are headache to manage. Most of the older models have BUGS from security & stability issues. Always make sure that you dont use buggy routers brands, Always upgrade the Firmwares to latest. This mostly rectifies many issues.
4. On busy network, always use your local dns (must be reachable with/without pppoe dialer as well via intervlan routing level). Uncheck allow remote request from mikrotik. In PPPoE profile, use the local dns as primary and google dns as secondary. In few network this sorted the YELLOW ICON sign.
5. Pay attention to mikrotik CPU, if you have high number of users on single Tik, OR if you have CONNTRACK/NATTING enabled, then disconnection of pppoe users can cause CPU spikes resulting in Tik freezing for a minute or it can cause other users disconnection dueto cpu not responding timely, resulting in looping as well. Use separate router for natting. If you have high number of PPP users along with some NATTING rules, Stop using Masquarade on same router that have a lot of dynamic interfaces. DO NOT use NAT on any router that have high number of connecting/disconnecting interfaces , like pppoe/vpn. Place an additional router connected with your PPPoE NAS, and route NAT traffic there. Make sure to disable CONNECTION TRACKING on PPPoE NAS router. As a rule of thumb, to divide load (& as a failover) , if you are using ccr1036 , add another ccr1036 after every 1200-1500 users.
6. Adding your local DNS & assign it to user profile as a primary DNS sorted the yellow sign problems in some users WiFi Routers.
7. PPP is sensitive to high delays and network timeouts, Make sure you dont have layer 2 level broadcast/delays
8. If you Cisco switch with VLANs , set STP/RSTP to none on switch TRUNK  [*** This sorted the ppp disconnection at few networks]
9. If you have Cisco switches with VLANs, Do Not allow all VLANS on TRUNK ports, Allow only limited/designated vlans on TRUNK port [*** This sorted dialup stuck issues at few networks]
10. Changing the MTU [sometimes it sorts websites & few apps related issues , examples whatsAPP , Telegram, etc]
11. Try to disable Encryption/Compression on the profile of the pppoe. Choosing only (pap) for pppoe server [This sorts some old freeradius related issues]
12. Disable RSTP on all ports/VLANS [Test with caution, for temporary basis only just to confirm if its related issue]
13. Disable LOOP protection in mikrotik ports settings [Test with caution, for temporary basis only]
14. Do Not disable ICMP Some user end routers checks for icmp reachability to detect internet access. It’s quite worse when there are operators that think that ICMP is dangerous and it has to be blocked. Make sure you are not blocking all ICMP traffic, just fine tune it to allow at least certain type of icmp packets, however, when someone further upstream does that, you will have problems
15. Do Not disable NTP protocol, [it is being used by many devices like android devices like android TV’s, Gaming devices etc]

Part 3/4 Annexure Example: [Test it with caution or preferably in LAB tests]

no spanning-tree vlan 1-1014
interface GigabitEthernet2/0/1
description Trunk-LAN-2-Mikrotik
switchport trunk encapsulation dot1q
switchport trunk allowed vlan 2-16,99
switchport mode trunk

---

Personnel Opinion!

Well TBH, Mikrotik is a cheap/affordable solution & overall Mikrotik is excellent for core routing too BUT its not made for large scale ppp NATTING. Mikrotik is not an enterprise grade solution with reference to pppoe concentrator. It have it’s architecture’s limitations. As a rule of thumb/In general , We suggest that after crossing 1200-1400 ppp users (& max 2Gb of traffic), just add another mikrotik (ccr1036 or likewise) & so on. I knows few ISp’s locally who are using mikrotik who have used Mikrotik routers just start up their journey in the SP business but later they moves to more mature products like cisco/juniper/vBNG. One ISP in particular using 10-12 Mikrotiks to cater 15k users load (in routing mode only, no natting). With natting situation gets worse when ppp users disconnects in large quantity resulting in CPU hiking/freezing creating nightmares for admins)

If you have thousands of users , then you are in serious business, go with *Huawei/Juniper/Cisco* (which are much mature but comparatively costly products ) & as an alternate, you may look for *VBNG* which have pay as per you go modules.

Syed Jahanzaib

Mikrotik Queue Tree with Traffic Priority

---

Example if you have assigned user with 1 mb profile, & if user is doing full download, his stream will get degrade, buffered because IDM will fetch using full available bandwidth using multiple connection.

Using Priority feature in TREE, we can configure mikrotik to assign user 1mb bandwidth limit, but it should always give 1mb priority to CDN traffic first, then to others. This way even if user is downloading at full speed liek using IDM, and if he plays video from CDN , then bandwidth priority will be given to CDN first (IDM download will degrade so that priority can be given to CDN.

This was done to avoid streaming buffer issue even if user is downloading at full speed .

– Youtube Link: https://www.youtube.com/watch?v=WxDzEonl-Bk

Queue Code: [dynamic queue for user is auto created upon user pppoe connection, on top of that we r using queue tree to prioritize]

/ip firewall address-list
add address=172.16.99.0/24 list=1mb
add address=58.27.130.0/24 list=cdn_list

/ip firewall mangle
add action=mark-connection chain=forward new-connection-mark=ICMP_Conn protocol=icmp
add action=mark-packet chain=forward connection-mark=ICMP_Conn new-packet-mark=ICMP_Pkts passthrough=no
add action=mark-packet chain=forward comment="MARK CDN UP" dst-address-list=1mb new-packet-mark=cdn_up passthrough=no \
src-address-list=cdn_list
add action=mark-packet chain=forward comment="MARK CDN DOWN" dst-address-list=cdn_list new-packet-mark=cdn_down \
passthrough=no src-address-list=1mb
add action=mark-packet chain=forward comment=MARK_1MB_UP_USER new-packet-mark=1mb_up passthrough=no src-address-list=1mb
add action=mark-packet chain=forward comment=MARK_1MB_DOWN_USER dst-address-list=1mb new-packet-mark=1mb_down \
passthrough=no

/queue simple
add max-limit=1M/1M name=ICMP packet-marks=ICMP_Pkts target=""

/queue tree
add max-limit=1G name=10G-ZAIB-WAN-Link parent=global
add name="icmp pkts Top Priority from Main Feed - Zaib" packet-mark=ICMP_Pkts parent=10G-ZAIB-WAN-Link priority=1
add name=1mb-overall-internet-up packet-mark=1mb_up parent=10G-ZAIB-WAN-Link queue=1mb-upload
add name=1mb-overall-internet-down packet-mark=1mb_down parent=10G-ZAIB-WAN-Link queue=1mb-download
add name=cdn-down-hi-priority packet-mark=cdn_down parent=1mb-overall-internet-down priority=2 queue=1mb-download
add name=cdn-up-hi-priority packet-mark=cdn_up parent=1mb-overall-internet-up priority=2 queue=1mb-upload

/queue simple
add max-limit=1M/1M name=ICMP packet-marks=ICMP_Pkts target=""

Regard’s
Syed Jahanzaib

Mikrotik to Mikrotik/Cisco Bonding – Reference Notes

This post was made for self reference purposes, so that I can find the configs easily from this page when needed again.

---

This post describes possible methods of creating ethernet interfaces bonding between Mikrotik to Cisco (etherchannel) or Mikrotik to Mikrotik to achieve load balancing & failover to achieve higher speed from multiple ethernet interfaces. Bonding is a technology that allows aggregation of multiple Ethernet-like interfaces into a single virtual link, thus getting Maximum Throughput and providing failover. You can use this technique to create bonding between WAN Cisco switch/R to User Mikrotik, then from User mikrotik to User distribution vlan switch. lots of combinations you can create in this regard.

Possible Scenarios:

OP have Mikrotik CCR1036 routerboard with SFP+. ISP have given OP with 10G of SFP+ connectivity on OP WAN Mikrotik router. but on user side router OP have simple CCR1036 with 1G ports only. So the wan link from 1036-wan to 1036-LAN is choking at  1G. Proper solution is to have back to back 10G connectivity between all routers, but since it requires cash investment, therefore the OP sometimes chooses workaround to fulfill the requirements for the time being. BONDING is one of that workaround that can be chosen. although I try to avoid using it as much as possible & always look for 10/25G solutions.

---

Hardware Used in this post

• Mikrotik: RB2011
• Cisco 36450 24 Ports Switch
• In general you can use upto 8 ports Bonding, should be of same type/speed.
• All ports should be enabled before adding them to bond.

---

Option-1# Mikrotik to Cisco Bonding (using 1Gx2 interfaces to achieve failover / load balancing / higher speed)

Using any two interfaces, we are creating 2 port Bonding interface. Example from Mikrotik Port 9 & 10 we will connect two cables connected to Cisco Switch port 23 & 24.

#Mikrotik Example Code , using port 9 & 10

/interface bonding
add link-monitoring=none mode=802.3ad name=bonding1-lan slaves=ether9,ether10 transmit-hash-policy=layer-2-and-3

# Cisco Switch Example Code, suing port 23 & 24

configure terminal
inter range gigabitEthernet 1/0/23-24
channel-group 1 mode active
channel-protocol lacp
#on newer IOS you dont need following CMD
#switchport trunk encapsulation dot1q
switchport mode trunk

Note: If you are doing Cisco to Cisco Port Bonding , then you need to repeat above code on 2nd switch too

Option-2# Mikrotik to Mikrotik Bonding

Its very simple. Just issue below command on both mikrotik and connect the cables from designated interfaces, assign IP, and you are good to go …

/interface bonding
add name=bonding1-to-ppp slaves=ether1,ether2

---

Some Helpful CMD’s related to etherchannel on Cisco switch …

show etherchannel summary
show etherchannel 1 port-channel
show interfaces etherchannel

Note: Before you connect both cable, make sure to add the config on Mikrotik & Cisco side, Else cisco port will get shut dueto STP protection to avoid loop.

*May 13 04:58:33.710: %ETC-5-L3DONTBNDL2: Gi1/0/23 suspended: LACP currently not enabled on the remote port.

---

Configuring EtherChannel Load Balancing

 

To view etherchannel load balancing setting, use the show command …

enable
show etherchannel load-balance

To configure load balancing, use the following commands:

enable
configure terminal
port-channel load-balance dst-mac
do wr

---

Regard’s
Syed Jahanzaib

Syslog-ng – Part 3: Minimized logging to mysql with dynamic tables & trimming

Revision: 7th-JAN-2020

• Part # 1 – Howto Save Mikrotik/Cisco Logs to Remote SYSLOG Server
• Part # 2 – Centralized Syslog-ng logging to MySql DB
• Part # 3 Minimized logging to mysql with dynamic tables & trimming >> You are here

---

In continuation to existing posts related to syslog-ng, Following post illustrates on how you can log only particular messages with pattern matching and let syslog-ng creates dynamic table based on the dates so that searching/querying becomes easy.

This task was required in relation to CGNAT logging. you may want to read it here

https://aacable.wordpress.com/2020/01/01/mikrotik-cgnat/

Hardware Software used in this post:

• Mikrotik Routerboard – firmware 6.46.x
• Ubuntu 16.4 Server x64 along with syslog-ng version 3.25.1 on some decent hardware

Requirements:

Ubuntu OS

---

Ref: Installing latest version of syslog-ng

#Make sure to change the version, I have used this CMD on Ubuntu 16.04 , for version 18, you may change this to 18.04

wget -qO - http://download.opensuse.org/repositories/home:/laszlo_budai:/syslog-ng/xUbuntu_16.04/Release.key | sudo apt-key add -
touch /etc/apt/sources.list.d/syslog-ng-obs.list
echo "deb http://download.opensuse.org/repositories/home:/laszlo_budai:/syslog-ng/xUbuntu_16.04 ./" > /etc/apt/sources.list.d/syslog-ng-obs.list
apt-get update
apt-get -y install apache2 mc wget make gcc mysql-server mysql-client curl phpmyadmin libdbd-pgsql aptitude libboost-system-dev libboost-thread-dev libboost-regex-dev libmongo-client0 libesmtp6 syslog-ng-mod-sql libdbd-mysql libdbd-mysql syslog-ng
Note: during above packages installation, it will ask you to enter mysql/phpmyadmin password, you can use your root password to continue the installations. It may download around  after installation finishes, you can check syslog-ng version.
At the time I did installation I got this
syslog-ng -V
root@nab-syslog:~# syslog-ng -V
syslog-ng 3 (3.30.1)
Config version: 3.29
Installer-Version: 3.30.1
Revision: 3.30.1-2
Compile-Date: Nov 19 2020 16:33:22
Module-Directory: /usr/lib/syslog-ng/3.30
Module-Path: /usr/lib/syslog-ng/3.30
Include-Path: /usr/share/syslog-ng/include
Error opening plugin module; module='mod-java', error='libjvm.so: cannot open shared object file: No such file or directory'
Available-Modules: syslogformat,azure-auth-header,hook-commands,linux-kmsg-format,kafka,afmongodb,json-plugin,cef,secure-logging,afsocket,pseudofile,kvformat,add-contextual-data,afamqp,riemann,http,appmodel,stardate,tfgetent,redis,cryptofuncs,sdjournal,afuser,pacctformat,graphite,confgen,geoip2-plugin,affile,basicfuncs,xml,mod-python,examples,afsmtp,timestamp,map-value-pairs,disk-buffer,afsnmp,system-source,afsql,afstomp,csvparser,tags-parser,afprog,dbparser
Enable-Debug: off
Enable-GProf: off
Enable-Memtrace: off
Enable-IPv6: on
Enable-Spoof-Source: on
Enable-TCP-Wrapper: on
Enable-Linux-Caps: on
Enable-Systemd: on

Status:
root@nab-syslog:~# service syslog-ng status
syslog-ng.service - System Logger Daemon
Loaded: loaded (/lib/systemd/system/syslog-ng.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2021-01-25 00:20:55 EST; 1min 26s ago
Docs: man:syslog-ng(8)
Main PID: 21596 (syslog-ng)
CGroup: /system.slice/syslog-ng.service
21596 /usr/sbin/syslog-ng -F

Jan 25 00:20:55 nab-syslog systemd[1]: Starting System Logger Daemon...
Jan 25 00:20:55 nab-syslog systemd[1]: Started System Logger Daemon.

Create Database in mySQL to store dynamic tables
Create Base Database for storing dynamically created date wise tables
mysql -uroot -pXXX -e "create database syslog;"
Now edit the syslog-ng file
nano /etc/syslog-ng/syslog-ng.conf
& use following as sample. I would recommend that you should add only relevant part, just dont do blind copy paste. This is just sample for demonstration purposes only …

Syslog-ng Sample File
@version: 3.30
@include "scl.conf"
# First, set some global options.
options { chain_hostnames(off); flush_lines(0); use_dns(no); use_fqdn(no);
dns_cache(no); owner("root"); group("adm"); perm(0640);
stats_freq(0); bad_hostname("^gconfd$");
};
########################
# Sources
########################
# This is the default behavior of sysklogd package
# Logs may come from unix stream, but not from another machine.
#
source s_src {
system();
internal();
};
########################
# Destinations
########################
# First some standard logfile
#
destination d_auth { file("/var/log/auth.log"); };
destination d_cron { file("/var/log/cron.log"); };
destination d_daemon { file("/var/log/daemon.log"); };
destination d_kern { file("/var/log/kern.log"); };
destination d_lpr { file("/var/log/lpr.log"); };
destination d_mail { file("/var/log/mail.log"); };
destination d_syslog { file("/var/log/syslog"); };
destination d_user { file("/var/log/user.log"); };
destination d_uucp { file("/var/log/uucp.log"); };
destination d_mailinfo { file("/var/log/mail.info"); };
destination d_mailwarn { file("/var/log/mail.warn"); };
destination d_mailerr { file("/var/log/mail.err"); };
destination d_newscrit { file("/var/log/news/news.crit"); };
destination d_newserr { file("/var/log/news/news.err"); };
destination d_newsnotice { file("/var/log/news/news.notice"); };
destination d_debug { file("/var/log/debug"); };
destination d_error { file("/var/log/error"); };
destination d_messages { file("/var/log/messages"); };
destination d_console { usertty("root"); };
destination d_console_all { file(`tty10`); };
destination d_xconsole { pipe("/dev/xconsole"); };
destination d_ppp { file("/var/log/ppp.log"); };
########################
# Filters
########################
# Here's come the filter options. With this rules, we can set which
# message go where.

filter f_dbg { level(debug); };
filter f_info { level(info); };
filter f_notice { level(notice); };
filter f_warn { level(warn); };
filter f_err { level(err); };
filter f_crit { level(crit .. emerg); };
filter f_debug { level(debug) and not facility(auth, authpriv, news, mail); };
filter f_error { level(err .. emerg) ; };
filter f_messages { level(info,notice,warn) and
not facility(auth,authpriv,cron,daemon,mail,news); };
filter f_auth { facility(auth, authpriv) and not filter(f_debug); };
filter f_cron { facility(cron) and not filter(f_debug); };
filter f_daemon { facility(daemon) and not filter(f_debug); };
filter f_kern { facility(kern) and not filter(f_debug); };
filter f_lpr { facility(lpr) and not filter(f_debug); };
filter f_local { facility(local0, local1, local3, local4, local5,
local6, local7) and not filter(f_debug); };
filter f_mail { facility(mail) and not filter(f_debug); };
filter f_news { facility(news) and not filter(f_debug); };
filter f_syslog3 { not facility(auth, authpriv, mail) and not filter(f_debug); };
filter f_user { facility(user) and not filter(f_debug); };
filter f_uucp { facility(uucp) and not filter(f_debug); };

filter f_cnews { level(notice, err, crit) and facility(news); };
filter f_cother { level(debug, info, notice, warn) or facility(daemon, mail); };
filter f_ppp { facility(local2) and not filter(f_debug); };
filter f_console { level(warn .. emerg); };
########################
# Log paths
########################
log { source(s_src); filter(f_auth); destination(d_auth); };
log { source(s_src); filter(f_cron); destination(d_cron); };
log { source(s_src); filter(f_daemon); destination(d_daemon); };
log { source(s_src); filter(f_kern); destination(d_kern); };
log { source(s_src); filter(f_lpr); destination(d_lpr); };
log { source(s_src); filter(f_syslog3); destination(d_syslog); };
log { source(s_src); filter(f_user); destination(d_user); };
log { source(s_src); filter(f_uucp); destination(d_uucp); };
log { source(s_src); filter(f_mail); destination(d_mail); };
log { source(s_src); filter(f_news); filter(f_crit); destination(d_newscrit); };
log { source(s_src); filter(f_news); filter(f_err); destination(d_newserr); };
log { source(s_src); filter(f_news); filter(f_notice); destination(d_newsnotice); };
log { source(s_src); filter(f_debug); destination(d_debug); };
log { source(s_src); filter(f_error); destination(d_error); };
log { source(s_src); filter(f_messages); destination(d_messages); };
log { source(s_src); filter(f_console); destination(d_console_all);
destination(d_xconsole); };
log { source(s_src); filter(f_crit); destination(d_console); };
@include "/etc/syslog-ng/conf.d/*.conf"

######## Zaib Section Starts here
# Accept connection on UDP
source s_net { udp (); };

# Adding filter for our Mikrotik Routerboard, store logs in FILE as primary
# MIKROTIK ###########

# This entry will LOG all information coming from this IP, change this to match your mikrotik NAS
filter f_mikrotik_192.168.0.1 { host("192.168.0.1"); };
# add info in LOG (Part1)
destination df_mikrotik_192.168.0.1 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};
source s_mysql {
udp(port(514));
tcp(port(514));
};

# Store Logs in MYSQL DB as secondary # add info in MYSQL (Part2)
destination d_mysql {
sql(type(mysql)
host("localhost")
# MAKE SURE TO CHANGE CREDENTIALS
username("root")
password("XXXXX")
database("syslog")
table("${R_YEAR}_${R_MONTH}_${R_DAY}")
columns( "id int(11) unsigned not null auto_increment primary key", "host varchar(40) not null", "date datetime", "message text not null")
values("0", "$FULLHOST", "$R_YEAR-$R_MONTH-$R_DAY $R_HOUR:$R_MIN:$R_SEC", "$MSG")
indexes("id"));
};
log {
source(s_net);
filter(f_mikrotik_192.168.0.1);
destination(d_mysql);
};
IMPORTANT:
Create ‘zlogs‘ folder in /var/log , so that mikrotik logs will be saved in separate file if required by you
mkdir /var/log/zlogs


Mikrotik rule to LOG Forward chain
Now we need to create a rule in mikrotik FILTER section so that it can log all packets being forward to/from pppoe users. Make sure you in source address list you select your local pppoe users pool there to avoid un-related excessive logging. In below example we are doing only TCP base connection for NEW tcp connections only.
LOG SIZE Example: at one ISP who had around 1200+ online users , its log size for TCP connection was around 25 GB. to lower the size, I configured it log only new TCP connections which reduced the DB Size by 50%.
/ip firewall filter
add action=log chain=forward connection-state=new protocol=tcp src-address-list=pppoe_allowed_users
Mikrotik rule to send LOG to SYSLOG-NG Server
/system logging action
add name=syslogng remote=192.168.101.1 target=remote
# Change IP address pointed towards syslog server

/system logging
set 0 topics=info,!firewall
add action=syslogng topics=firewall






Restart Syslog-ng server
Now restart syslog-ng service
service syslog-ng restart
and you will see the dynamic tables created as follows
mysql -uroot -pXXXXX
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 411
Server version: 5.7.28-0ubuntu0.18.04.4-log (Ubuntu)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use syslog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_syslog |
+------------------+
| 2020_01_08 |
+------------------+
1 row in set (0.00 sec)

mysql> describe 2020_01_08;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| host | varchar(40) | NO | | NULL | |
| date | datetime | YES | | NULL | |
| message | text | NO | | NULL | |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
& you can then see data insertion into the table as soon LOG is received from remote devices
2020-01-08T07:49:43.020811Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:28', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto TCP (ACK,PSH), 172.16.0.2:57193->172.217.19.174:443, NAT (172.16.0.2:57193->101.11.11.252:2244)->172.217.19.174:443, len 79')
2020-01-08T07:49:43.031281Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:28', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto TCP (ACK,FIN), 172.16.0.2:57096->3.228.94.102:443, NAT (172.16.0.2:57096->101.11.11.252:2219)->3.228.94.102:443, len 40')
2020-01-08T07:49:43.041420Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:38', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:49247->216.58.208.234:443, NAT (172.16.0.2:49247->101.11.11.252:2202)->216.58.208.234:443, len 1378')
2020-01-08T07:49:43.051112Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:38', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:49247->216.58.208.234:443, NAT (172.16.0.2:49247->101.11.11.252:2202)->216.58.208.234:443, len 1378')
2020-01-08T07:49:43.061280Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:39', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:49760->172.217.19.1:443, NAT (172.16.0.2:49760->101.11.11.252:2202)->172.217.19.1:443, len 1378')
2020-01-08T07:49:43.071449Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:39', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:49760->172.217.19.1:443, NAT (172.16.0.2:49760->101.11.11.252:2202)->172.217.19.1:443, len 1378')
2020-01-08T07:49:44.828993Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:44', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:53503->216.58.208.234:443, NAT (172.16.0.2:53503->101.11.11.252:2203)->216.58.208.234:443, len 827')
2020-01-08T07:49:44.851034Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:44', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:53503->216.58.208.234:443, NAT (172.16.0.2:53503->101.11.11.252:2203)->216.58.208.234:443, len 827')
2020-01-08T07:51:37.518276Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:51:37', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto TCP (ACK), 172.16.0.2:57202->91.195.240.126:80, NAT (172.16.0.2:57202->101.11.11.252:2260)->91.195.240.126:80, len 41')
2020-01-08T07:51:37.522015Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:51:37', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto TCP (ACK), 172.16.0.2:57202->91.195.240.126:80, NAT (172.16.0.2:57202->101.11.11.252:2260)->91.195.240.126:80, len 41')



TIPS
Deleting all tables inside particular DB

#!/bin/bash
# drop tables matching filter
force=1;
u=root;
p=SQLPASS;
db=syslog;
filter=users_;
for t in $(mysql -u $u -p$p -D $db -Bse 'show tables' | grep $filter); do
echo Dropping $t;
[[ $force -eq 1 ]] && mysql -u root -p$p -D $db -Bse "drop table \`$t\`"
done


Regard’s

Syed Jahanzaib