Monitoring network flow with tcpdump and mysql

In my job I was asked to monitor the traffic between two sites, in a L2L environment. Cisco netflow was out of question due problems with the core supervisor-version.

My first impulse was to install ntop on a new Ubuntu box, and starting collection of data. But we all know, ntop is not able to store statistics and after 20 or 24 hours over a 100 Mbps link we will have the ntop crashing and all useful information will be lost - except the rrdtool info.

Well, I gave up to try the ntop tool and decided to use a lower level approach, using tcpdump and mysql.

Simply writing the packets from tcpdump to mysql database is not an useful thing. With a 100Mbps link you will have 6.000.000 packets per hour. Your CPU usage will be huge after a few hours of collection inside mysql.

The solution to this issue is pretty simple. Consolidation of 5 minutes interval will decrease this to approximately 15.000 packets per hour, depending on your number of hosts. All you need to know is a little bit of SQL queries and how to use crontab.

I'm using a Core 2 duo, with 1 GB ram and 250 GBytes disk - I have installed Ubuntu 7.10.

The monitoring is possible using the session monitor from Catalyst 3550 - a simple port mirroring.














My L2L link is on FastEthernet0/1 and my Linux box on FastEthernet0/2.

Here's the configuration from Catalyst 3550:

monitor session 2 source interface Fa0/1
monitor session 2 destination interface Fa0/2

The first thing you'll have to do is install mysql. Simply type:

sudo aptitude install mysql-server

When asked define the password. In this example I'm using "xxxxxx" as password.

After mysql installation you have to create the database and tables to store data - the queries:

# Mysql Database and Table creation
CREATE DATABASE collection;
USE collection;
CREATE TABLE packets (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
cur_timestamp TIMESTAMP(8),
source VARCHAR(15),
destination VARCHAR(15),
pack_size DECIMAL(15,0)unsigned not null
);
CREATE TABLE networks (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ip VARCHAR(15),
mask VARCHAR(15),
name VARCHAR(30)
);
CREATE TABLE media (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
start_time TIMESTAMP(8),
end_time TIMESTAMP(8),
source VARCHAR(15),
num_packets INT,
total_transfer INT,
average INT
);

This is the basic script that collects the tcpdump to a file. I'm not recording all the fields, just the source, destination and size. Actually only source is necessary. This file increases fast in size. Take care of this one at least once a day (I.e.: stop tcp dump and delete it using cron on some low traffic period).

mysql-collect.sh

The next step is to write another script that will run every hour and keep our database smaller, consolidating information about every host, on 5 minutes interval:

consolida.sh

You can put these files on /etc/init.d directory and navigate thru there.
To make mysql-collect.sh start on every machine startup just type:

sudo chmod +x /etc/init.d/mysql-collect.sh
sudo update-rc.d mysql-collect.sh start 99 2 .

Then put consolida.sh on your crontab file, typing:

sudo chmod +x /etc/init.d/consolida.sh
crontab -e

then on the editor you insert the line:

00 * * * * /etc/init.d/consolida.sh

When this script finishes to run you get on /var/log/messages the information:

[Consolida Fri Jan 25 15:00:02 BRST 2008] Starting Consolida 25/01/2008-14h [Consolida Fri Jan 25 15:10:05 BRST 2008] End Consolida

This means that the mysql takes 10 minutes to consolidate 60 minutes of traffic - it's a lot of information.

Now you are able to verify the 20 top talkers on and determined period of time with a simple mysql query:

SELECT * FROM media WHERE start_time='2008-01-25/14:00:00' LIMIT 20;

--------------
select * from media where start_time='2008-01-25/14:20:00' LIMIT 20
--------------

+-------+---------------------+---------------------+--------------+-------------+----------------+---------+
| id | start_time | end_time | source | num_packets | total_transfer | average |
+-------+---------------------+---------------------+--------------+-------------+----------------+---------+
| 77287 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.195.1.16 | 19494 | 26310686 | 701618 |
| 77288 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.80.3.1 | 28903 | 16832161 | 448858 |
| 77289 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.195.1.47 | 16932 | 16433531 | 438227 |
| 77290 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.195.1.56 | 95814 | 15102357 | 402730 |
| 77291 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 172.23.5.52 | 9947 | 12391601 | 330443 |
| 77292 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.195.1.15 | 19739 | 11280969 | 300826 |
| 77293 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.80.3.45 | 23596 | 10848085 | 289282 |
| 77294 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.80.3.225 | 11965 | 10055957 | 268159 |
| 77295 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.195.1.201 | 5900 | 8759995 | 233600 |
| 77296 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.76.19.20 | 5193 | 7674389 | 204650 |
| 77297 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 148.91.2.241 | 17668 | 6162209 | 164326 |
| 77298 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.195.1.28 | 17941 | 6050186 | 161338 |
| 77299 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 172.23.4.228 | 25362 | 3857761 | 102874 |
| 77300 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.194.1.32 | 4960 | 3733610 | 99563 |
| 77301 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.76.19.21 | 2389 | 3376997 | 90053 |
| 77302 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.20.1.3 | 2280 | 3175395 | 84677 |
| 77303 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.80.3.69 | 5331 | 2828996 | 75440 |
| 77304 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.195.1.18 | 43485 | 2810093 | 74936 |
| 77305 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.195.1.84 | 18565 | 2729074 | 72775 |
| 77306 | 2008-01-25 14:20:00 | 2008-01-25 14:24:59 | 10.194.1.13 | 3440 | 2621495 | 69907 |
+-------+---------------------+---------------------+--------------+-------------+----------------+---------+
20 rows in set (0.10 sec)


This is an working example of how to collect data using tcpdump. If you need more information fields regarding source port or destination it can be easily collected from tcpdump with some tweak on 'awk' and tcpdump parameters.

Another option is use tshark instead of tcpdump. The problem with tshark is the huge amount of CPU power needed over a 100 Mbps link. If your objective is to monitor a low speed link, the tshark is easier to use than tcpdump.

Good luck!

Ready for Gutsy?

Then I suggest you read the Release Notes of this new version:

https://wiki.ubuntu.com/GutsyGibbon/ReleaseNotes?action=show&redirect=GutsyReleaseNotes

In the next days I will be posting my first impression of this new version!

See you!

Problems with video card?

Have you seen this screen recently??



Before you reinstall your Ubuntu, check out this tip:

If you changed something on your Linux configuration regarding video you can easily solve your problem returning your old /etc/X11/xorg.conf

In most cases we do not have a backup of this file until we need it. In this case you can reconfigure your system with this command:

sudo dpkg-reconfigure xserver-xorg

Just follow the screen instructions. A Vesa video work fine for most boards in an emergency.



Ubuntu 7.10

Remove startup program manually

When you change something that you shouldn't at Beryl, like the Rendering Platform option...

The programs that you have added manually from this menu:












Are available at your ~/.config/autostart directory.



Use Ctrl + Alt + F1 to open a new terminal, logon and remove the program that is causing the problem.

From Windows to Ubuntu... CLI...

Interesting...

Windows	Linux	What it does?
dir	ls -lah	List files on a directory in a human view
copy	cp	Copy files
move	mv	Move Files
cls	clear	Clear screen
md	mkdir	Create folder
rd	rmdir	Remove folder
del	rm	Remove a file
deltree	rm -r	Remove a folder and its contents

CPU scaling

When you are using a laptop CPU scaling helps you to save power on batteries or give you better performance when connected to AC power.

Just add the applet to some of your gnome panels and change the permissions of file /usr/bin/cpufreq-selector with this command:

sudo chmod +s /usr/bin/cpufreq-selector

Then you will get this icon showing you the processor speed...



Clicking over it you can choose the best profile to use.








Is this information useful?? So remember to Thumb it up!!!

Cedilla / Cedilha Problem (ć to ç)

a very common problem to Brazilian users with U.S. International keyboard. Still with the problem on Feisty...
When you press ' + c you get ć instead of ç.


Edit as super user the file "/etc/gtk2.0/gtk.immodules" and replace

"cedilla" "Cedilla" "gtk+" "/usr/share/locale"
"az:ca:co:fr:gv:oc:pt:sq:tr:wa"

with

"cedilla" "Cedilla" "gtk+" "/usr/share/locale"
"az:ca:co:fr:gv:oc:pt:sq:tr:wa:en"

Great Programs to start

Some programs to make the system usable.

beryl emerald emerald-theme - Graphical enhancements;
tilda - Amazing terminal program that brings you the terminal with a single keystroke;
amarok - Music Player;
k3b - CD/DVD burner;
wine - Run your Windows programs on Linux;
vlc - Video Player;
beagle - Desktop search engine;
checkgmail - Stay up-to-date with your Gmail messages;

To install any of these programs
sudo aptitude update sudo aptitude install "program"

Or, to install them all!
sudo aptitude install beryl emerald emerald-theme tilda amarok k3b wine vlc beagle checkgmail

Remember to put tilda, beryl-manager and checkgmail commands on System / Preferences / Sessions / Startup Programs. This will make these programs to start after reboot.

Useful files

Some files used to configure your system. This list will be increased soon...

Software Sources
Add or remove your software repositories.
/etc/apt/sources.list

Grub Loader
With this you can change your boot order.
/boot/grub/menu.lst

Environment variables
Paths, proxies and misc. variables usable globally. (i.e.: "PATH=/bin")
/etc/environment

Partitions
File used to configure the mount of your disk partitions
/etc/fstab

Make wget, apt-get and aptitude use a proxy server

Just add this lines to your /etc/environment file...

http_proxy="http://ip address:ip port"
ftp_proxy="http://ip address:ip port"

then run this command to apply the changes:

source /etc/environment

Startup - Like autoexec.bat :)

This script will run before the graphical interface appears...

1. Make the script with desired commands.
vi file.sh

2. Copy the script to /etc/init.d
cp file.sh /etc/init.d/file.sh

3. Change permission to allow execution
chmod +x /etc/init.d/file.sh

4. At /etc/init.d Link it!
sudo update-rc.d file.sh start 99 2 .
(do not forget the "." at the end of the command)


If you need to disable this script in a near future:
sudo update-rc.d -f file.sh remove