Syed Jahanzaib Personal Blog to Share Knowledge !

January 21, 2021

Possibilities: Mikrotik PPP Disconnection/Yellow Sign Problems

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


Disclaimer! This is important!

Every Network is different , so one solution cannot be fit/applied to all. Therefore try to understand logics & create or modify the solutions as per your network scenario. Never 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.

Regard’s
Syed Jahanzaib~


PPP Common Problems

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

  • User pppoe dial stuck , not able to reach to mikrotik pppoe server
  • User pppoe connectivity frequent/intermittent disconnection/termination
  • User pppoe yellow mark at user device/workstation , No internet

Try to diagnose the issue one by one by below tips

  1. Pay attention to mikrotik CPU, if you have high number of users on single Tik, OR if you have NATTING enabled, then disconnection of pppoe users can cause CPU spikes resulting in Tik freezing for a minute or more causing other users disconnection, creating 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.
  2. PPP is sensitive to high delays and network timeouts, Make sure you dont have layer 2 level broadcast/delays
  3. If you Cisco switch with VLANs , set STP/RSTP to none on switch TRUNK  [*** This sorted the ppp disconnection at few networks]
  4. 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 / yellow signs issues at few networks]
  5. Changing the MTU [sometimes it sorts websites & few apps related issues , examples whatsAPP , Telegram, etc]
  6. Try to disable Encryption on the profile of the pppoe
  7. Choosing only (pap) for pppoe server [This sorts some old freeradius related issues]
  8. Disable RSTP on all ports/VLANS [Test with caution, for temporary basis only]
  9. Disable LOOP protection in mikrotik ports settings [Test with caution, for temporary basis only]
  10. 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
  11. 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

January 19, 2021

January 11, 2021

Cisco 10G Switch & Lenovo SFP Module Compatibility issue

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

Recently we acquired cisco 10g SFP+ switch to be added in existing stack. While trying to connect Lenovo ThinkSystem SR650 (P.No: 7X06CTO1WW ) server along with lenovo provide SFP+ modules (P.No 46C3447) with 10g Cisco switch (WS-C3850-24XS-S) via MM Fiber cable. Upon SFP+ module insertion, at both end (server to switch) then the port gets shuts with err-disabled with following error on switch logs

010834: Jan 4 09:43:44: %GBIC_SECURITY_CRYPT-4-VN_DATA_CRC_ERROR: GBIC in port Te1/0/7 has bad crc
010836: Jan 4 09:43:44: %PM-4-ERR_DISABLE: gbic-invalid error detected on Te1/0/7, putting Te1/0/7 in err-disable state

& on vmware esxi  , it showed *DISCONNECTED*

Following were technical details:

SEVER END:

  • ThinkSystem SR650 (P.No: 7X06CTO1WW )
  • 10g NIC: Emulex VFA5.2 2×10 GbE SFP+ PCIe Adapter (P.No: AT7S )
  • 10g SFP+ Module: Lenovo SFP 10gbase-sr Fiber Optic Transceiver Module (P.No 46C3447 / )

SWITCH END:

  • SWITCH MODEL : Cisco 10g SFP+ switch (P.No: WS-C3850-24XS-S )
  • Cisco Switch 10GBASE Fiber Optic SFP 10G Transceiver Module: Cisco SFP-10G-SR * Part No: 10-2415-03)
  • Vivanco Optical Fiber Patch Cord: LC-LC MM DUPLEX OM3 10M

Solution:

After searching here & there, I found that we have to disable SFP compatibility check in the switch using below CMD’s

Add these two commands (blue highlighted) to the switch:

Switch(config)# service unsupported-transceiver
— you will get a warning message here—
Switch(config)# no errdisable detect cause gbic-invalid

Afterwards , shut/no shut the switch interface then plugged in the Lenovo cable back in.  & the connectivity got OK. (make sure WRITE the config on switch so that it stays permanent.

Note: Any time non-Cisco optics are going to be plugged in to a Cisco switch it’s worth adding these commands.


Regard’s
Syed Jahanzaib

Vmware VCenter inaccessible Datastore

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

Recently one of our VCenter 6.7 got crashed & services were not accessible, I spent hours but couldn’t restore it. To save time in further troubleshooting we removed the VC from the esxi server . Few Esxi servers were managed by this Vcenter. I logged in to each ESXI  server & in Actions I selected “Disconnect it from Vcenter“. Afterwards when new Vcenter (VCSA-7) got installed , all esxi were added successfully , But one of the ESXI server was showing some errors, therefore I removed it from the Vcenter, & when I tried to add it again to VC , following error appeared

Datastore ‘M5-11.10–8TB-raid10’ conflicts with an existing datastore in the datacenter that has the same URL (ds:///vmfs/volumes/5d810e33-e56c55bf-71be-0894ef440178/), but is backed by different physical storage.

At VCenter I was seeing below

When I right clicked on this data store, DELETE/MOUNT/UNMOUNT option were greyed out as well. How can I remove this inaccessible data store?

From the Vcenter, I Browsed that inaccessible datastore  , & clicked on VMs tab, it was showing one VM which was moved to another esxi host in the past, I edited that old VM on that esxi server , and removed the mounted ISO (which was pointing to the affected vm esxi server)  , afterwards, the inaccessible datastore disappeared automagically, and the Esxi got re-added in VC again smoothly.


Sharing is caring !

Regard’s
Syed Jahanzaib

November 18, 2020

mySQL Master-Slave Replication Notes

Filed under: mysql — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 11:44 AM

 


This post contains short notes on how we can create mySQL master to slave replication. It is generally required to fulfill multi stage backup topology. Example using the SLAVE server, we can have always uptodate live replica partner which can be used in case of any master server failure.

Very useful for replicate the DB in real time , or to be used in DR sites scenarios as well! I deployed it at few local networks, & results were good when it comes to DR/DB recovery.

 

Pros:
Master-Slave is very fast as in general it doesn’t impose any restrictions on performance.We can split read and write requests to different servers. For example, all analytics queries can be made on Slave nodes.

Cons:
Write requests can hardly be scaled. The only option to scale write requests is to increase compute capacity (RAM and CPU) of the Master node. Failover process is manual in a general case. You should take care of promotion replica node to master one.

Components used in this guide,

  • Two VM’s
  • OS: Ubuntu 16.04.3 LTS (Xenial Xerus)
  • mySQL Version: mysql Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using EditLine wrapper
  • Name/IP: mySQL MASTER server: master-254 / 101.11.11.254
  • Name/IP: mySQL SLAVE server : slave-255 / 101.11.11.255
  • Credentials: mysql root password: zaib1234

Assumptions:

Mysql is installed on both servers.

MASTER Configuration

mysql -uroot -pzaib1234
create database radius;
grant all on radius.* to radius@localhost identified by "zaib1234";
# Create separate ID for replication , it will be used on SLAVE
create user 'zaib'@'%' identified by 'zaib1234';
grant replication slave on *.* to 'zaib'@'%';
exit

Edit mysql configuration to make it MASTER

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add the below entries in [mysqld] section

log-bin=mysql-bin
#(comment below line if you want all DB's to be replicated, in below e.g we are doing only radius DB replication)
binlog-do-db=radius
server-id=1
log_bin = /var/log/mysql/mysql-bin.log
bind-address = 0.0.0.0 # Search this and change it manually , donot copy paste

Restart mySQL service so changes can take effect

service mysql restart

Now we need to lock table & note down the file/position number which will be later use in SLAVE configuration

mysql -uroot -pzaib1234
FLUSH TABLES WITH READ LOCK;
show master status;
# Note down the File name and Position number, this is important, we will use it in SLAVE server
exit

Export the required DB (This will be imported on SLAVE server later)

mysqldump -u root -pzaib1234 radius --master-data > master.sql

once the export is done, unlock the tables

mysql -uroot -pzaib1234
UNLOCK TABLES;
exit

SLAVE Configuration

using any tool like winscp, copy the master.sql from the master server into this slave server.& import it.

mysql -uroot -pzaib1234 radius < master.sql

Once done, Edit mySQL configuration to make it SLAVE

nano /etc/mysql/mysql.conf.d/mysqld.cnf
log-bin=mysql-bin
#(comment below line if you want all DB's to be replicated, in below e.g we are doing only radius DB replication)
binlog-do-db=radius
# This is slave, it will have server ID number 2, on master server we have id=1
server-id=2
log_bin = /var/log/mysql/mysql-bin.log
bind-address = 0.0.0.0 # Search this and change it manually , donot copy paste

SAVE & EXIT

Restart mySQL service so that changes can take effect

service mysql restart

Now login to mySQL & config the change master parameter

mysql -uroot -pzaib1234
CHANGE MASTER TO MASTER_HOST='101.11.11.254',MASTER_USER='zaib', MASTER_PASSWORD='zaib1234', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
START SLAVE;

[Note: In above code, we used MASTER_LOG_FILE & MASTER_LOG_POS parameters when we issued show master status in master configuration section]


TEST

Login to master mySQL & create any tables or entries


root@MASTER-254:~# mysql -uroot -pzaib1234
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 98
Server version: 5.7.32-0ubuntu0.16.04.1-log (Ubuntu)

Copyright (c) 2000, 2020, 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 radius;
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_radius |
+----------------------+
| masterdb_mast_table1 |
+----------------------+
1 row in set (0.00 sec)

mysql>

now login to SLAVE mySQL & look for the tables status


root@SLAVE-255:/tmp# mysql -uroot -pzaib1234
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 49
Server version: 5.7.32-0ubuntu0.16.04.1-log (Ubuntu)

Copyright (c) 2000, 2020, 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 radius;
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_radius |
+----------------------+
| masterdb_mast_table1 |
+----------------------+
1 row in set (0.00 sec)

& we can see that tables got replicated fine.


Tips:

If you want to exclude one table from exporting (master-data) (example any large table within DB which is not important)

mysqldump -u root -pSQLPASS  DBNAME  --master-data --ignore-table=DBNAME.TABLE > export_file_name.sql

 

June 19, 2020

Mikrotik Queue Tree with Traffic Priority

Filed under: Mikrotik Related — Syed Jahanzaib / Pinochio~:) @ 3:17 PM


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

May 15, 2020

Mikrotik to Mikrotik/Cisco Bonding – Reference Notes

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

aggregated

cisco bonding mikrotik to cisco

2 ports bonding

4ports_bonding

2020-05-13 13.19.04

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

February 28, 2020

Restricting Lotus Domino Email Flow for Local Groups

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

neutral-p009-512

We are using Lotus Domino 8.5.3.xxx series mail server which have many local groups along with associated members in it. Yesterday an valid external user sent annoying email to some of local groups like dept1@mydomain.com & the email got delivered to all members associated with this group despite there was no email/internet address defined for it. This happened for the first time & we were surprised as it was not in our knowledge before that external user can send email to local groups as well despite not having internet addresses created for it exclusively.

After doing some R&D and posting to lotus domino groups, it was revealed that under Server Document / Configuration Setting / Router/SMTP / Basics  , there was a setting named ADDRESS LOOKUP set to FULLNAME THEN LOCAL PART , which was responsible for accepting email for the local group even though there was no internet address associated with it.

Some explanation :

FULLNAME THEN LOCAL PART (default):

The Router first searches the Domino Directory for a match for the full Internet address (localpart@domain.com). If no match is found, it searches the directory again, looking for a match for the local part of the address only.

After setting it to FULLNAME ONLY, [followed by tell router update / tell adminp p all / sh nlcache reset] the issue got resolved & now when external user sends email to DEPT1@mydomain.com , he gets ‘Recipient could not be found’ NDR report.

[0B60:000A-18F4] 02/28/2020 08:45:26 AM SMTP Server: Mail for dept1@mydomain.com rejected for policy reasons. Recipient could not be found in the Domino Directory.

NOTE: Full Name Only in conjunction with not having an Internet Address specified for the Group will work.


Other workarounds:

Initially we restricted flow email destined to local group by using two methods

1) MAIL RULES

Under Server Document / Configuration Setting / Router/SMTP / Restrictions & Control / Rules , add a new rule like following

server mail rule

dont forgot to Move this rule on TOP

I have also added my id in exception so that I can send email This is example for EXCEPTION.

2. Group based ACL 

Second method is by putting ACL on each group so that only particular user can see the group , list members, or send email to that particular group. Use the reader attributes of the group being used to email to (open the document properties of the group and click on the tab with the key). Set who can read the group to a limited group of people who are authorized to send such broadcasts. Be sure to include localdomainservers as well as the names of the people who maintain the group. Now they can put it into the TO field without concern for someone replying to all since only someone who can see the group can use it. This works for external users as well because smtp messages are treated as anonymous. Unless you give anonymous access to the group, they can’t use it either.

This is briefly described here

https://www.ibm.com/support/knowledgecenter/SSKTMJ_9.0.1/admin/conf_restrictingusersfromsendingmailtogroupsinthedomi_t.html


Regard’s
Syed Jahanzaib

January 8, 2020

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

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

syslog cgnat

Revision: 7th-JAN-2020


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:

I assume that you already have working setup for Syslog-ng& your remote devices are already sending logs and they are storing in the mysql already. See Part 1 & 2


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

during above packages installation, it will ask you to enter mysql/phpmyadmin password, you can use your root password to continue the installations. after installation finishes, you can check syslog-ng version.

At the time I did installation I got this

syslog-ng 3 (3.25.1)
Config version: 3.25
Installer-Version: 3.25.1
Revision: 3.25.1-1
Compile-Date: Dec 12 2019 12:00:29
Module-Directory: /usr/lib/syslog-ng/3.25
Module-Path: /usr/lib/syslog-ng/3.25
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: add-contextual-data,afsmtp,tfgetent,afsql,cryptofuncs,http,confgen,sdjournal,system-source,cef,syslogformat,json-plugin,afprog,riemann,csvparser,affile,afsocket,afamqp,redis,examples,disk-buffer,xml,linux-kmsg-format,map-value-pairs,hook-commands,kafka,tags-parser,dbparser,graphite,appmodel,afstomp,pacctformat,afmongodb,pseudofile,basicfuncs,geoip2-plugin,kvformat,stardate,timestamp,mod-python,afuser,snmptrapd-parser
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@agpis-linux-test:/var/log/zlogs# 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 Wed 2020-01-08 12:46:52 PKT; 27min ago
Docs: man:syslog-ng(8)
Main PID: 7086 (syslog-ng)
Tasks: 2 (limit: 2290)
CGroup: /system.slice/syslog-ng.service
ââ7086 /usr/sbin/syslog-ng -F

Jan 08 12:46:52 agpis-linux-test systemd[1]: Starting System Logger Daemon...
Jan 08 12:46:52 agpis-linux-test syslog-ng[7086]: [2020-01-08T12:46:52.362728] Macro escaping can only be specified for inline templates;
Jan 08 12:46:52 agpis-linux-test syslog-ng[7086]: [2020-01-08T12:46:52.364052] WARNING: With use-dns(no), dns-cache() will be forced to 'no' too!;
Jan 08 12:46:52 agpis-linux-test 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.25
@include "scl.conf"
# Syslog-ng CUSTOMIZED configuration  file
# Syed Jahanzaib / aacable at hotmail dot com /https://aacable.wordpress.com
# First, set some global options.
options { chain_hostnames(off); flush_lines(0); use_dns(no); use_fqdn(no);
owner("root"); group("adm"); perm(0640); stats_freq(0);
bad_hostname("^gconfd$");
};

######## 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
filter f_mikrotik_252 { host("101.11.11.252"); };
# This entry will LOG ONLY particular message that contains word NAT, useful to minimize CGNAT logging, Enable one entry at a time # ZAIB
#filter f_mikrotik_252 { host("101.11.11.252") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik_252 ); destination ( df_mikrotik_252 ); };
# add info in LOG (Part1)
destination df_mikrotik_252 {
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("XXXXXXXX")
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_252);
destination(d_mysql);
};
########################
# 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();
};

IMPORTANT:

Create ‘zlogs‘ folder in /var/log , so that mikrotik logs will be saved in separate file.

mkdir /var/log/zlogs

Mikrotik rule to LOG Forward chain

/system logging action

set 1 disk-file-count=50 disk-lines-per-file=5000
set 3 remote=101.11.11.254

/system logging add action=remote topics=info

/system logging action set 3 remote=101.11.11.254

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')

syslog-ng dynamic table data from phpmyadmin.PNG


Regard’s
Syed Jahanzaib

January 1, 2020

CGNAT Deployment using Mikrotik RouterOS

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

mdnet cgnat conept.PNG

Note: This is In-complete Post. It contains src-nat method part only. Second method of NETMAP will be added later (if time will allow)  which is I feel far more simple & efficient as compared to the src-nat method. But this method is ok too to comply with the Law using little resources.

My humble request, 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 networks and I read , research & try stuff all of the time. When you are enslaved by private job & working as one man army, you have to perform many task in which you are not formally trained for. 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 help others

Some references used in this post


*CG-NAT* as Workaround:

CGNAT concept is used to share one or preferably more public IP addresses with large number of private ip addresses on ratio basis.CGNAT/NAT444 is a conception, not a function. In terms of RouterOS functionality it’s simple SRC NAT rule.

To combat with this IPV4 exhausting issue, we can use CGNAT as a workaround. This is by no means a solution, & the OP should get public IP space (either ipv4 or ipv6) to comply with the LAW.

Note: Please note that CGNAT concept is mostly for UDP/TCP and its generally not meant for other protocols.

Some possible disadvantages of using CGNAT concept:

  • CGNAT is not sustainable in the long term, hectic to manage the private/public pools especially if you have multiple NASes doing same job
  • ISP deploying IP address sharing techniques should also deploy a corresponding logging architecture to maintain records of the relation between a customer’s identity and IP/port resources utilized
  • You should deploy additional SYSLOG server (either windows or linux base) to store logs. I would prefer linux base SYSLOG-NG). Tracking of users for legal reasons means searching hundreds GB’s of logging would be required, as multiple end users go behind one (or more) public IP address(es). Tracking Logs is not an easy task particularly when you have tongs of Logging (in a DB).Logging every NAT translation is resource consuming. Some super fast computing resources (including preferably RAID10 or SSD based storage) and fine tune DB would be required
  • A CG-NAT device must use the same external IP address mapping for all sessions associated with the same internal IP address
  • Most Applications do not behave well with TCP resets
  • Many operators are still not familiar with CG-NAT complexities. There is a lot of trial and error on the part of ISP’s

 

in my personal experience , Deployment is somewhat hectic, & tracking any request is daunting task ! z@ib


Hardware/Software Used in this post:


CGNAT logging to remote syslog server with some customization

https://aacable.wordpress.com/2020/01/08/syslog-ng-part-3-minimized-logging-to-mysql-with-dynamic-tables-trimming/


Scenario#1

OP is running mini ISP with around 200 active subscribers. Mikrotik Router is being used as PPPoE Server along with Freeradius as AAA. On Mikrotik, one public IP is configured for WAN and additional /24 routed pool (256 public IP addresses) is provided to the OP via ISP so that he can provide public IP to each user. After the network upgrades , OP have reached 700 users in total, and since he have only 256 public ip’s , he is now using natting for half of his users.

We all know that IPV4 shortage is on peak , getting ipv4 is expensive for 3rd world countries & small ISP’s as well.

This NATTING workaround is creating hurdles in tracking illegal activity performed by any NATTED users because hundreds of NATTED user will have same public ip (Mikrotik WAN IP). nowadays law sometimes provide only the public ip along with source port and ask for the user credentials details for investigation purposes.

with single public IP and hundreds of natted hosts behind it. tracking is nearly impossible.


IP scheme example used in this Scenario#1:

Public IP range: (/24 public IP’s routed pool)

  • 1.1.1-1.1.1.255
  • Total Public IP useable: 255

Private IP range for PPPoE users:

  • 172.16.1.1-172.16.1.255
  • 172.16.2.1-172.16.2.255
  • 172.16.3.1-172.16.3.255
  • Total Private IP useable: 765

For 765 Users, we will be using 1:5 Ratio, thus 153 public ips will be used for 765 users. (on a ratio of 1:5).

  • per private IP, we will reserve 10,000 ports, which should be more than enough for each user.
  • per private IP, we will be creating 3 rules, one for TCP, second for UDP, 3rd for non ports range [Use 3rd this rule with caution, it will nat every non tcp/udp traffic, some firewalling may be put, ALSO YOU MAY NOT BE NEEDING 3rd rule which can eliminate 1/3 rules]

in my personal expeirence, CGNAT configuration on RouterOS is very much similar to regular source NAT configuration.


To add multiple Public IP addresses on WAN interface in bulk using single CMD on Terminal

You may need to add all of your public IP addresses (which will be used for CGNAT) on WAN interface(required for troubleshooting purposes as well).

To add ips in bulk using single CMD, you can use Mikrotik FOR X script function for ease / ZAIB

:for x from 1 to 153 do={ /ip address add address="1.1.1.$x/32" comment="1.1.1.$x - Routed IP for ppp CGNAT - zaib" interface="ether1-wan"}

 


Adding FUNCTION in Mikrotik for later Automation

Paste this in Mikrotik RouterOS terminal:

# CGNAT Customized minimalistic Script to add function.
# Disclaimer: This particular function is not made by ME, I only trimmed/modified it to suite my local requirements
# Syed Jahanzaib / aacable at hotmail dot com
:global sqrt
:global sqrt do={
:for i from=0 to=$1 do={
:if (i * i > $1) do={ :return ($i - 1) }
}
}
:global addNatRules do={
:local x [$sqrt $count]
:local y $x
:if ($x * $x = $count) do={ :set y ($x + 1) }
:for i from=0 to=($count - 1) do={
:local prange "$($portStart + ($i * $portsPerAddr))-$($portStart + (($i + 1) * $portsPerAddr) - 1)"
# src-nat TCP traffic
/ip firewall nat add chain=srcnat action=src-nat protocol=tcp src-address=($srcStart + $i) to-address=$toAddr to-ports=$prange
# src-nat UDP traffic
/ip firewall nat add chain=srcnat action=src-nat protocol=udp src-address=($srcStart + $i) to-address=$toAddr to-ports=$prange
# This below 3rd rule is created to allow protocols other then tcp/udp, example ICMP ? , use it with caution , zaib
/ip firewall nat add chain=srcnat action=src-nat src-address=($srcStart + $i) to-address=$toAddr
}
}

Now we have function inserted with the help of above code, and using this function, we can create rules in bulk using following CMD to add rules in NAT section

# per private IP, we will reserve 10000 ports, which should be more than enough for each user.
# per private IP, we will be creating 3 rules, one for TCP, second for UDP, 3rd for non ports range

$addNatRules count=5 srcStart=172.16.1.1 toAddr=1.1.1.1 portStart=10000 portsPerAddr=9999
$addNatRules count=5 srcStart=172.16.1.6 toAddr=1.1.1.2 portStart=10000 portsPerAddr=9999
$addNatRules count=5 srcStart=172.16.1.11 toAddr=1.1.1.3 portStart=10000 portsPerAddr=9999
$addNatRules count=5 srcStart=172.16.1.16 toAddr=1.1.1.4 portStart=10000 portsPerAddr=9999
$addNatRules count=5 srcStart=172.16.1.21 toAddr=1.1.1.5 portStart=10000 portsPerAddr=9999
$addNatRules count=5 srcStart=172.16.1.26 toAddr=1.1.1.6 portStart=10000 portsPerAddr=9999
$addNatRules count=5 srcStart=172.16.1.31 toAddr=1.1.1.7 portStart=10000 portsPerAddr=9999
$addNatRules count=5 srcStart=172.16.1.36 toAddr=1.1.1.8 portStart=10000 portsPerAddr=9999
$addNatRules count=5 srcStart=172.16.1.41 toAddr=1.1.1.9 portStart=10000 portsPerAddr=9999
$addNatRules count=5 srcStart=172.16.1.46 toAddr=1.1.1.10 portStart=10000 portsPerAddr=9999

# & so on for rest of the pool, you can further automate this by using additional functions & scripting

Enable Logging of CG-NAT Output:

# To log user IP/NAT information on LOG window / you can configure remote log to syslog-server too
/ip firewall filter
add action=accept chain=forward log=yes log-prefix="NAT_INFO_FW> " src-address=172.16.0.0/16

Log Result (from different servers , so ip scheme may be changed in these logs, For example purposes)

In this log you can clearly see the src-dst address, and on which public ip request was natted along with ports. This is useful

Rules from LAB Router:

Mikrotik WAN IP’s (2 for test purposes):

  • 101.11.11.255/32
  • 101.11.11.253/32

PPPoE Users (2 for test)

  • 172.16.0.1
  • 172.16.0.2

REMOTE WEB SERVER (considering it’s a web server on internet which our user is accessing or doing illegal stuff)

  • 101.11.11.255

SRC-NAT Rules on MIKROTIK:

/ip firewall nat
add action=src-nat chain=srcnat protocol=tcp src-address=172.16.0.1 to-addresses=101.11.11.255 to-ports=10000-19999
add action=src-nat chain=srcnat protocol=udp src-address=172.16.0.1 to-addresses=101.11.11.255 to-ports=10000-19999
add action=src-nat chain=srcnat src-address=172.16.0.1 to-addresses=101.11.11.255
add action=src-nat chain=srcnat protocol=tcp src-address=172.16.0.2 to-addresses=101.11.11.253 to-ports=20000-29999
add action=src-nat chain=srcnat protocol=udp src-address=172.16.0.2 to-addresses=101.11.11.253 to-ports=20000-29999
add action=src-nat chain=srcnat src-address=172.16.0.2 to-addresses=101.11.11.253

cgnat-log-1.PNG

Result:

On internet web server, we see following

[101.11.11.255]:10133 - - [02/Jan/2020:15:44:37 +0500] "GET /? HTTP/1.1" 200 3138 "-" "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.88 Safari/537.36"

so the law enforcement agency come to us, and tell us that this is your public IP+Port  101.11.11.255:10133, now give us his details. And as we know that we are doing CGNAT, so we have to do little tracking.

On Mikrotik LOG we see following [after enabling LOGS,

cgnat-log-2.PNG

You can now see that our public IP having port 10133 was natted for our local user IP 172.16.0.1. with PPPoE it will show you the user name as well, so you can catch it right from here, or else if RADIUS is being used, you can track the IP via freeradius DB in radacct.


Scenario#2

OP have single public IP (e.g: 101.11.11.252) configured on Mikrotik WAN interface. End user subscriber is connected to mikrotik pppoe server using pppoe dialer. In this example we will be using 172.16.0.0/24 (256 users) and each user IP will be allowed to use 200 ports (200 ports per private IP).

This way when LAW will ask to provide details for 101.11.11.252:41636 , we can look into our LOGS (usually SYSLOG server either in linux, or using windows based SYSLOG like solarwinds syslog serveR) we can look into the 101.11.11.252:41636 & we can see the pppoe username or its private ip and search the ip in radius radacct table if radius is being used)

$addNatRules count=255 srcStart=172.16.0.1 toAddr=101.11.11.252 portStart=2000 portsPerAddr=200

Above CMD will create 765 rules (for 256 users) in IP / Firewall / NAT section. (make sure you have pasted the addNatRules function in the terminal before using above command.

– Enable mikrotik logs in Mikrotik LOG window

To enable LOGS in mikrotik LOG window , use

/ip firewall filter
add action=accept chain=forward log=yes log-prefix="NAT_INFO_FW> " src-address=172.16.0.0/16

– Enable mikrotik built in DISK base logging

To enable DISK base LOGGING in Mikrotik itself, (avoid this, it will OVERLOAD your routerboard which is not designed to handle such massive load of LOGS)

/system logging action
set 1 disk-file-count=25 disk-lines-per-file=5000
/system logging
add action=disk prefix=NAT_INFO_FW topics=info

– Enable remote SYSLOG logging in mikrotik

To ENABLE remote SYSLOG (I used Solarwind SYSLOG server on Windows in this example.

/system logging action
set 3 bsd-syslog=yes remote=10.0.0.2
/system logging
add action=remote prefix=NAT_INFO_FW topics=info

Now we can see in the LOG window (just an example, in actual you have to use some SYSLOG server) to search for 101.11.11.252:41636

Jan/03/2020 10:48:43 firewall,info NAT_INFO_FW> forward: in: out:ether1-wan, src-mac d0:bf:9c:f7:88:76, proto TCP (ACK), 172.16.0.199:54326->179.60.194.35:443, NAT (172.16.0.199:54326->101.11.11.252:41636)->179.60.194.35:443, len 40
Jan/03/2020 10:48:43 firewall,info NAT_INFO_FW> forward: in: out:ether1-wan, src-mac d0:bf:9c:f7:88:76, proto TCP (ACK), 172.16.0.199:54326->179.60.194.35:443, NAT (172.16.0.199:54326->101.11.11.252:41636)->179.60.194.35:443, len 52

& as you can see that 101.11.11.252:41636 was used private IP 172.16.0.199 & it will also show the <pppoe-zaib> . This way you can pull the user details & provide it to law enforcement agencies.

on windows base REMOTE syslog we can see the results, and can search easily as well.

solarwind syslog.png


To Delete older logs from syslog mysql DB

 mysql -uroot -pSQLPASSWORD -s -e "use syslog; DELETE FROM logs WHERE date(datetime) < (CURDATE() - INTERVAL 3 MONTH);"

TIPS for Linux base SYSLOG-NG trimming

I am using SYSLOG-NG to store all logs , to log only the NAT related queries (which actually shows the entries of public:port vs private ip:port use following in syslog ng configuration (before SOURCE section

######## Zaib Section Starts here
# Accept connection on UDP
source s_net { udp (); };
# MIKROTIK ########### add logs into files & in mysql dB as well.zaib
# Add Filter to add our mikrotik
filter f_mikrotik_1 { host("10.0.0.1") and match("NAT" value("MESSAGE")) };
#filter f_mikrotik_1 { host( "10.0.0.1" ); };
log { source ( s_net ); filter( f_mikrotik_1 ); destination ( df_mikrotik_1 ); };
destination df_mikrotik_1 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};

source s_mysql {
udp(port(514));
tcp(port(514));
};
# Play with below, some confusion here
destination d_mysql { pipe("/var/log/mysql.pipe" template("INSERT INTO
logs (host,facility,priority,level,tag,datetime,program,msg) VALUES
('$HOST','','','','','$YEAR-$MONTH-$DAY
$HOUR:$MIN:$SEC','','$MSG');\n") template-escape(yes)); };

log {
source(s_net);
filter(f_mikrotik_1);
destination(d_mysql);
};
####### #Zaib Section ends here

Note: For 500 active subscribers , the average log size on the syslog DB was 500 MB per day. This was after the controlled syslog entries (logging of requests that contains word NAT only).


Regard’s
~ Syed Jahanzaib ~

 

zaib_scattered

Older Posts »

%d bloggers like this: