We drive traffic in Linux. Accounting, statistics collection

Each admin should set up a mail in his life, write a billing and scold Mazda :)
© bash.org.ru

We will not configure Sendmeyl, as well as scold Billy’s brainchild, but try to create something like billing, because this issue sooner or later confronts almost all system administrators.

The article is aimed at beginners in administration and experienced specialists will hardly be interesting.

And so, what do we have? A network of 50-200 computers, a dedicated Internet channel (ADSL, Ethernet, etc.), a linux router. It is required to count Internet traffic of users (in different directions), limit the speed, determine the traffic limits per month. In addition, I want to manage all of this through some web-muzzle.

We take the distribution package ubuntu-server 8.04 as a basis.
')
In order to uniquely identify the user and secure theft of traffic by replacing ip addresses, configure the VPN network using PPTPD.

Almost all actions in the console will be performed from the root, so in order not to write sudo every time, we write:

rustam@srv:~$ sudo -i

PPTP server setup

Install:

root@srv:~# apt-get install pptpd

Open the /etc/pptpd.conf config and add the lines:

localip 10.1.0.1
remoteip 10.1.0.2-254

we determine the server address and the range of addresses of users in the virtual network.

By default, pptpd stores user accounts in the / etc / ppp / chap-secrets file, add test accounts:

# Secrets for authentication using CHAP
# client server secret IP addresses
user1 pptpd 123 10.1.0.2
user2 pptpd 567 10.1.0.3

Pay attention to the ip addresses, they should be from the range specified in the remoteip parameter of the /etc/pptpd.conf file!

Restart the pptpd service:

root@srv:~# /etc/init.d/pptpd restart

We allow connections to our service:

root@srv:~# iptables -A INPUT -s 192.168.0.0/24 -p tcp -m state --state NEW -m tcp --dport 1723 -j ACCEPT

Instead of 192.168.0.0/24, substitute the address of your local network. We try to connect to our virtual network; to do this, we create a new VPN connection on the machine with Win XP (“Creating a new connection” - “Connecting to the network at the workplace” - “Connecting to a virtual private network”). If everything turned out, move on.

Ulog-acctd setup

To collect traffic statistics we will use ulog-acctd, install:

root@srv:~# apt-get install ulog-acctd

Open the config /etc/ulog-acctd.conf. We are interested in the parameters of the accounting format and fdelay.

The accounting format parameter is responsible for the log entry format, a detailed description of each parameter is in the comments, so I will not describe in detail. We need this format:

#
accounting format="%t\t%s\t%d\t%b\n"

The fdelay parameter determines after what period of time ulog-acctd will reset the accumulated statistics to the log, set it to 10 seconds.

fdelay=10

Restart ulog-acctd.

Iptables configuration

In order for connected users to connect to the Network, configure NAT for the virtual network:

root@srv:~# iptables -t nat -A POSTROUTING -s 10.1.0.0/24 -j MASQUERADE

This rule tells ulog what traffic to log:

root@srv:~# iptables -A FORWARD -d 10.1.0.0/24 -j ULOG --ulog-cprange 48 --ulog-qthreshold 50

We are interested in transit traffic going to the VPN - network.

Now let's test the resulting bundle, connect to the network and try to download something, the following entries should appear in the /var/log/ulog-acctd/account.log log:

1224187495 208.67.222.222 10.1.0.2 126
1224187534 205.188.9.192 10.1.0.2 40
1224187556 62.213.122.2 10.1.0.2 10660
1224187594 205.188.9.192 10.1.0.2 40
1224187599 62.213.122.2 10.1.0.2 22130
1224187615 62.213.122.2 10.1.0.2 1037
...

The first value is the time in unix format, the second is the sender's address, the third is the recipient (user) address, the fourth is the number of bytes.

MySQL and database schema

root@srv:~# apt-get install mysql-server

Create a ulog user in MySQL and import the database schema:

CREATE DATABASE `ulogdb` DEFAULT CHARSET utf8;

USE `ulogdb`;

CREATE TABLE `data` (
`id` int(11) NOT NULL auto_increment,
`id_user` int(11) NOT NULL,
`ts` int(11) NOT NULL,
`bytes` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET utf8;

CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`login` varchar(32) NOT NULL,
`password` varchar(64) NOT NULL,
`ip` varchar(15) NOT NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET utf8;

This is a simplified scheme, later we will modernize it. In the data table we store aggregated traffic statistics, in users information about users. Add users to the users table, which are specified in / etc / ppp / chap-secrets.

INSERT INTO `users` (`login`, `password`, `ip`) VALUES
('user1', '123', '10.1.0.2');
INSERT INTO `users` (`login`, `password`, `ip`) VALUES
('user2', '567', '10.1.0.3');

Parsim log

We will write in perl, so you need to install the module to work with MySQL:

root@srv:~# apt-get install libdbd-mysql-perl

I quote the full text of the script:

#!/usr/bin/perl

use DBI;

# ,
my $db_name = "ulogdb";
my $db_user = "ulog";
my $db_pass = "1234";

# -
$account_log = "/var/log/ulog-acctd/account.log";

#
my $DBH = DBI->connect("DBI:mysql:$db_name:localhost",$db_user,$db_pass) or die "Error connecting to database";

# ip+id_user
my $STH = $DBH->prepare("select ip,id from users");
$STH->execute;
while (@tmp = $STH->fetchrow_array()) {
$users{$tmp[0]} = $tmp[1];
}
$STH->finish;

#
system "cp $account_log /tmp/ulog-parser.tmp && cat /dev/null > $account_log";
open LOGFILE,"< /tmp/ulog-parser.tmp";
while (<LOGFILE>) {
chomp;

# $saddr ,
#

($ts,$saddr,$daddr,$bytes) = split /\t/;

# ,
#
# . 1

$ts = $ts - $ts % 60;

#
# -
# :
# -> id ->

if (exists($users{$daddr})) {
$data{$ts}{$users{$daddr}} += $bytes;
}
}
close LOGFILE;
unlink("/tmp/ulog-parser.tmp");

#
my $STH_CHK = $DBH->prepare("select id_user,id from data where ts=?");
my $STH_ADD = $DBH->prepare("insert into data (id_user,ts,bytes) values(?,?,?)");
my $STH_UPD = $DBH->prepare("update data set bytes=bytes+? where id=?");

#
#
for $ts (keys %data) {
# ,
$STH_CHK->execute($ts);
%ex_data = ();
while (@tmp = $STH_CHK->fetchrow_array()) {
$ex_data{$tmp[0]} = $tmp[1];
}
$STH_CHK->finish;

#
for $id_user (keys %{$data{$ts}}) {
if (exists($ex_data{$id_user})) {
# , bytes
$STH_UPD->execute($data{$ts}{$id_user},$ex_data{$id_user});
$STH_UPD->finish;
} else {
#
$STH_ADD->execute($id_user,$ts,$data{$ts}{$id_user});
$STH_ADD->finish;
}
}
}
#
$DBH->disconnect;


Save the script as /usr/bin/ulog-parser.pl and set the attributes:

root@srv:~# chmod 700 /usr/bin/ulog-parser.pl

We try to start, and we look, whether the data appeared in the data table:

mysql> select count(id) from data;
+-----------+
| count(id) |
+-----------+
| 118 |
+-----------+

With minimal knowledge of PHP and SQL, the conclusion of statistics is not difficult;)

At this point I’ll finish, the article is already too big. If this topic is of interest to you, I will write a sequel (grouped by traffic by directions, speed limit).

PS: Thank you ISVir and thestorm for karma. The first post.