Mikrotik - collecting and analyzing NetFlow traffic

Foreword

Once upon a time, in a distant galaxy far away ... Although if you think about it, it was only 15 years ago.

In general, there were times when FreeBSD and Linux based solutions were used as the central gateway to the Internet. And these solutions were lovingly configured, and they were hung with all possible and impossible functions (from the firewall and VPN servers to TFTP + diskless boot PXE services) ... and there was no trouble, and everything was fine ...

But times are changing, new solutions are emerging, companies are appearing that prepare the Linux kernel “cheaply and angrily”, weigh the necessary functionality and sell it for quite modest money (comparable to the cost of the hardware).
')
An example of such a solution is the company Mikrotik and its solution of the same name.

Current reality

Total current situation - to buy and put Mikrotik in an organization in which from 10 to 5000 computers, faster and more economical than taking the “next system engineer” and assembling the gateway in parts (network, software, services, etc.)

At the same time, traffic accounting tasks both have been and remain. And here comes to the aid standing server (usually a NAS based on Linux or FreeBSD).

Accounting for WEB-traffic is simple and clear - a bunch of Squid + LightSquid allows you to quickly and easily collect and aggregate information about who visits which sites, which files it downloads and how much it hangs on Youtube. If necessary, you can limit the sites, time, etc. Simple, convenient solution, proven over the years. In Mikrotik, one rule is made that allows an IP proxy server to the Internet. And everyone is happy.

But the problem is that not everything passes through Squid successfully. There are bank clients written without HTTP and Socks proxy support. There are complex programs that use different connections for different types of traffic - the result - either they do not work well through a proxy, or they do not work at all. And there is a separate category of so-called VIPs ... for whom it is easier to give “Full NAT”, than to aggravate relations, when “something doesn’t open at them”.

Thus, in Mikrotik sooner or later, but there will be separate rules that release "special" directly through NAT, bypassing the proxy server. And we no longer see their traffic in statistics.

The decision to account for such traffic suggests the following:

• Enable the capture of NetFlow statistics on the external interface of Mikrotik;
• Sending these statistics to the NAS (for example, to the flow-tools service, through flow-capture)

For convenient analysis of the files received on the NAS server, this solution is proposed to be improved with the help of a pair of self-written scripts:

• Perl script that will process ft-files and upload information to MySQL;
• PHP script that will serve as a pre-configured UI, for convenient analysis of NetFlow data.

Mikrotik setup

Everything is simple and documentation:

/ip traffic-flow set enabled=yes interfaces=WAN /ip traffic-flow target add dst-address=<NAS IP Address> port=8787 v9-template-timeout=1m version=5 

Flow-Tools configuration using FreeBSD as an example

 #  NetFlow : pkg install flow-tools #  : echo 'flow_capture_enable="YES"' >> /etc/rc.conf.local echo 'flow_capture_flags="-N-2"' >> /etc/rc.conf.local # : service flow_capture start 

Installation and preparation of MySQL DBMS for importing NetFlow data

 # ,    MySQL : pkg install mysql56-server #   echo 'mysql_enable="YES"' >> /etc/rc.conf service mysql start #   : mysql_secure_installation #  Perl-     : pkg install p5-DBI p5-DBD-mysql #            : mysql -u root -p 

# Create DBMS and user:

 mysql> create database netflow; mysql> grant insert,create,update,select,delete on netflow.* to nfuser@'localhost' identified by '987654321'; mysql> flush privileges; mysql> exit; 

Perl script for analyzing ft- * NetFlow statistics files and loading data into MySQL

The script was not written from scratch - a long time ago, in 2005, an article ( link ) was posted on the OpenNET website about traffic counting on the FreeBSD gateway using the nG_ipacct NetGraph module.

The boot script was taken as a basis and rewritten for use with NetFlow and flow-tools. It works on both FreeBSD and Linux (rewrite paths to flow-cat and flow-print programs only).

Features of the script - this option is designed to analyze all ft- * files for the past 24 hours and load them into the database (line by line). In this case, string exclusion is implemented for several patterns in order not to load redundant information in MySQL (for example, exclude broadcast traffic, DNS query traffic, traffic from HTTP / Socks proxy (there is a good proxy statistics in another place). By my measurements, the exception reduces the number of rows loaded into the database by 10, or even 20-30 times.

The tables in the DBMS are created automatically with the beginning of the new month. Day is added to the standard NetFlow v5 format, recording time (the time of created ft-files is used - for example, every 15 minutes), the name of the NetFlow source and the name of the network interface are also indicated.

PHP UI for simplified SQL query building

Back in 2005, when the author's Perl scripts were used, we used SQL commands to analyze the data in MySQL ... and everything suited everyone.

But sooner or later the moment came when they were tired of entering requests. And, having collected my thoughts, I wrote a small PHP code that allowed building SQL queries in a faster and easier way.

Appearance:



What the script allows to do:

1. Produce SQL queries, see the final query itself and its result in tabular form;
2. See the amount of NetFlow traffic for any month;
3. Group traffic by one indicator (SourceIP, SourcePort, DestIP, DestPort, Proto, Date);
4. See the number of lines instead of the sum (both per month and grouped on one basis);
5. Filter by traffic volume;
6. See the records themselves instead of the amount of traffic;
7. Request the specified number of records from the database (for example, for further copy / paste to Excel).

PS: The owner of the netflow.php file must be a Web server user (for example, Apache).
PSS: Access to the DBMS is specified in the netflow.php file explicitly - so change it for yourself.

MySQL table maintenance

If the tables are quite large (although no one bothers you to load only what you need into the DBMS, eliminating slag and reducing the size), then there is an interesting technique that allows you to significantly reduce the size of the DBMS. We are talking about the use of database compression in MyISAM format, as well as index optimization.

To automatically perform these procedures, another Perl script was written, which is launched through Cron on the first day of each new month:

• Repairs the table just in case (suddenly there were incorrect shutdowns of the service and MyISAM contains errors);
• Renames a table by appending c at the end (from compressed);
• Runs the myisamchk and myisampack programs, which compress the database and build a new and sorted index for the compressed table.

After the script has been executed, this table will become ReadOnly, it will be compressed (the size will decrease 3 times), and a new sorted and optimized index will be constructed. Queries in such a table will be executed faster.

Summary

All scripts can be downloaded from the link .

Update1: Improved the netflow.php script - to work with PHP version 7.x: link