Apparently this is a very hard thing to do. So I’ve written some scripts that take advantage of iptables to track, on a per-minute basis, the traffic used per user.
This is implemented on a Debian virtual-server through which SSH accounts and SSH tunnels are used.
A Brief Overview
This technique involves tailing the /var/log/auth.log file to see when a user logs in via SSH and when they disconnect. In this log is the IP address, port, and user name of the remote connecting user.
By grabbing this information we can insert a rule into iptables to merely monitor all packets going to, and another rule to monitor all packets coming from, that remote IP address and port.
On a regular basis, thereafter, we show the iptables rules along with the statistics Linux keeps for each rule – and the statistic we are interested in is the byte count (not the packet count) that has matched (flowed through) that rule.
Thus we split this goal into two tasks: one to tail the auth.log and create/remove iptables rules; the other task involves listing the iptables rules and writing the results to a database (the accounting side).
Getting iptables to Track Usage
A Perl script was written to tail the auth.log authentication log for messages about when (and from where) a user was connecting (and disconnecting).
When a user connects the auth.log contains a line that looks as follows:
Oct 14 15:17:42 myserver sshd[19897]: Accepted password for jsmith from 192.168.17.3 port 53014 ssh2
A corresponding disconnect line looks like the following:
Oct 15 00:29:12 knuth sshd[19897]: pam_unix(sshd:session): session closed for user jsmith
Note that the process ID (PID) enclosed in square brackets is the only way of matching a closing session to a connection. Thus a running script must keep, in memory, the process IDs of sessions that are being tracked. Comments can be added to iptables rules and this feature could be used to read back existing tracking rules if the tailing script must be restarted.
The following script is executed using the following command:
setsid /usr/bin/tail -n 0 -F /var/log/auth.log |/root/ssh_account.pl 2>/dev/null &
#!/usr/bin/perl
use IO::Select;
use Getopt::Std;
use strict;
my %opts = ();
Getopt::Std::getopts( 'd', \%opts );
select( (select(STDERR),$|=1)[0] ); # autoflush
my %pidlist = ();
my $iptables = "/sbin/iptables";
sub process_log_line {
my ( $line ) = @_;
my $pid = undef;
my $msg = undef;
if (
$line =~ m{
sshd\[(\d+)\]:
\s+
(.+)
$
}x
) {
( $pid, $msg ) = ( $1, $2 );
} else {
next;
}
my $piduser = "";
if ( exists( $pidlist{$pid} ) ) {
$piduser = $pidlist{$pid}->{'user'};
}
if ( $msg =~ m/Accepted \S+ for (\S+(?:.+\S)?) from (\d+\.\d+\.\d+\.\d+) port (\d+)/ ) {
my ( $user, $ip, $port ) = ( $1, $2, $3 );
$pidlist{$pid} = +{
user => $user,
ip => $ip,
port => $port,
pid => $pid,
};
add_entry( $pidlist{$pid} );
} elsif ( exists( $pidlist{$pid} ) && ( $msg =~ m/session closed for user \Q$piduser\E/ ) ) {
# delete rules
my $ip = $pidlist{$pid}->{'ip'};
my $port = $pidlist{$pid}->{'port'};
my $comment = "pid:$pid user:$piduser";
my $cmd = "$iptables -t filter -D useraccount -s $ip -p tcp --sport $port -m comment --comment \"$comment\"";
`$cmd`;
$cmd = "$iptables -t filter -D useraccount -d $ip -p tcp --dport $port -m comment --comment \"$comment\"";
`$cmd`;
delete $pidlist{$pid};
}
}
sub add_entry {
my ( $ref_pid ) = @_;
my $ip = $ref_pid->{ip};
my $user = $ref_pid->{user};
my $port = $ref_pid->{port};
my $pid = $ref_pid->{pid};
my $comment = "pid:$pid user:$user";
# create IP tables rule
my $cmd = "$iptables -t filter -I useraccount -s $ip -p tcp --sport $port -m comment --comment \"$comment\"";
`$cmd`;
$cmd = "$iptables -t filter -I useraccount -d $ip -p tcp --dport $port -m comment --comment \"$comment\"";
`$cmd`;
}
sub setup_iptables {
print( STDERR "- setting up iptables\n" ) if ( $opts{d} );
my $cmd = "$iptables -F useraccount";
`$cmd`;
$cmd = "$iptables -N useraccount";
`$cmd`;
$cmd = "$iptables -D INPUT -p tcp -j useraccount";
`$cmd`;
$cmd = "$iptables -I INPUT -p tcp -j useraccount";
`$cmd`;
$cmd = "$iptables -D OUTPUT -p tcp -j useraccount";
`$cmd`;
$cmd = "$iptables -I OUTPUT -p tcp -j useraccount";
`$cmd`;
}
sub check_iptables {
my $cmd = "$iptables -L useraccount -n -v -x";
my $data = `$cmd 2>&1`;
if ( $data =~ m{\QNo chain/target/match by that name\E} ) {
# must rebuild table
setup_iptables();
}
# go through pid list and add any entries that appear to be missing
foreach my $pid ( sort keys %pidlist ) {
my $ip = $pidlist{$pid}->{ip};
my $port = $pidlist{$pid}->{port};
my $pid = $pidlist{$pid}->{pid};
if ( $data !~ m{\s+\Q$ip\E\s+.*?:\Q$port\E\s+.*?:\Q$pid\E\s+} ) {
add_entry( $pidlist{$pid} );
}
}
}
sub main {
setup_iptables(); # force flush
my $sel = IO::Select->new( \*STDIN );
my $readbuf = "";
while ( 1 ) {
print( STDERR "Entering select...\n" ) if ( $opts{d} );
my ( $rh, $wr, $er ) = IO::Select::select( $sel, undef, undef, 10 );
if ( ( ! $rh ) || ( ! @{$rh} ) ) {
print( STDERR "- check iptables\n" ) if ( $opts{d} );
check_iptables();
next;
}
print( STDERR "- read from STDIN\n" ) if ( $opts{d} );
sysread( \*STDIN, $readbuf, 1024, length($readbuf) );
while ( $readbuf =~ s{[\r\n]*([^\r\n]+)[\r\n]+}{}s ) {
print( STDERR " - process line from STDIN\n" ) if ( $opts{d} );
process_log_line( $1 );
}
}
}
main();
With the above running you will see output similar to the following when you run the command:
vhost01:~# iptables -L useraccount -v -n -x
Chain useraccount (2 references)
pkts bytes target prot opt in out source destination
164 39440 tcp -- * * 0.0.0.0/0 8.8.8.8 tcp dpt:52615 /* pid:30449 user:jsmith */
266 18128 tcp -- * * 8.8.8.8 0.0.0.0/0 tcp spt:52615 /* pid:30449 user:jsmith */
15669 11663476 tcp -- * * 0.0.0.0/0 8.8.4.4 tcp dpt:43697 /* pid:28821 user:ajones */
10965 2717224 tcp -- * * 8.8.4.4 0.0.0.0/0 tcp spt:43697 /* pid:28821 user:ajones */
As you can see the byte count uploaded and downloaded for each session active is given. To keep track of the data used by a user, however, you will need to poll this table frequently and store the incremental changes over time.
Storing Usage Data
I created two tables in MySQL to track usage by minute per user per session, and to summarise this on an hourly basis by user.
I launch a script to do this with the following command:
setsid /root/ssh_store.pl >/dev/null 2>/dev/null &
The script to do this is as follows:
#!/usr/bin/perl -w
use DBI;
use strict;
my $iptables = "/sbin/iptables";
my $debug = 1;
my %pids = ();
sub summarise_minutes {
print( "+summarise_minutes()\n" ) if ( $debug );
my $dbh = DBI->connect('dbi:mysql:sshaccount','sshuser','sshpassword')
or die "Connection Error: $DBI::errstr\n";
my $sql_1 = q!
INSERT INTO hour
( `time`, `user`, `in`, `out` )
SELECT
TIMESTAMP( date(`time`), MAKETIME( hour(`time`), 0, 0 ) ) AS `time`
,user AS `user`
,SUM(`in`) AS `in`
,SUM(`out`) AS `out`
FROM
`minute`
WHERE
(
date( `time` ) = date( NOW() ) AND
hour( `time` ) < hour( NOW() )
) OR (
date( `time` ) < date( NOW() )
)
GROUP BY
TIMESTAMP( date(`time`), MAKETIME( hour(`time`), 0, 0 ) )
,user
!;
my $sth_1 = $dbh->prepare($sql_1);
my $ste_1 = $sth_1->execute() or die( "Error: " . $dbh->errstr );
my $sql_2 = q!
DELETE FROM
`minute`
WHERE
(
date( `time` ) = date( NOW() ) AND
hour( `time` ) < hour( NOW() )
) OR (
date( `time` ) < date( NOW() )
)
!;
my $sth_2 = $dbh->prepare($sql_2);
my $ste_2 = $sth_2->execute() or die( "Error: " . $dbh->errstr );
}
sub do_store {
my ( $first_invocation ) = @_;
my %seen = ();
print( "+do_store()\n" ) if ( $debug );
my $dbh = DBI->connect('dbi:mysql:sshaccount','sshuser','sshpassword')
or die "Connection Error: $DBI::errstr\n";
# fetch iptables output
# 14086 9534592 tcp -- * * 0.0.0.0/0 132.185.144.15 tcp dpt:39292 /* pid:30126 user:auser */
my $data = `$iptables -L useraccount -n -v -x`;
if ( $? ) {
`$iptables -N useraccount`;
$data = `$iptables -L useraccount -n -v -x`;
}
while (
$data =~ m{
[\r\n]+\s*
\d+\s+
(\d+)\s+
\s*tcp
[^\r\n]*
tcp\s+(d|s)pt:
[^\r\n]*
/\*\s*
pid:(\d+)
\s+
user:(.+?)
\s*\*/
}sgx
) {
my ( $bytes, $dir, $pid, $user ) = ( $1, $2, $3, $4 );
print( " iptables:$bytes,$dir,$pid,$user\n" ) if ( $debug );
if ( ! exists( $seen{$pid} ) ) {
$seen{$pid} = +{ 's' => 0, 'd' => 0, user => $user };
}
if ( ! exists( $pids{$pid} ) ) {
$pids{$pid} = +{ 's' => undef, 'd' => undef };
}
if ( ! defined( $pids{$pid}->{$dir} ) ) {
if ( $first_invocation ) {
$pids{$pid}->{$dir} = $bytes; # entry might already exist
} else {
$pids{$pid}->{$dir} = 0; # new entry created
}
}
my $periodbytes = int( $bytes - $pids{$pid}->{$dir} );
$periodbytes = 0 if ( $periodbytes < 0 ); # deal with restart
$seen{$pid}->{$dir} = $periodbytes;
$pids{$pid}->{$dir} = $bytes; # update to current byte count
}
foreach my $pid ( keys %seen ) {
# skip zero records (no need)
next if ( ( ! $seen{$pid}->{'s'} ) && ( ! $seen{$pid}->{'d'} ) );
my $sql = "INSERT INTO minute (`user`, `pid`, `in`, `out`) VALUES (?,?,?,?)";
my $sth = $dbh->prepare($sql);
my $ste = $sth->execute( $seen{$pid}->{'user'}, $pid, $seen{$pid}->{'s'}, $seen{$pid}->{'d'} );
print( " sql:$sql\n" ) if ( $debug );
}
# clean up pids
my @knownpids = keys %pids;
foreach ( @knownpids ) {
delete $pids{$_} if ( ! exists( $seen{$_} ) );
}
}
sub main {
my $first_time = 1;
my $last_time = 0;
my $this_hour = 0;
my $this_hour_done = 0;
while ( 1 ) {
sleep( 1 );
my $this_time = time();
next if ( $last_time == $this_time );
next if ( ( $this_time - $last_time ) < 30 );
if ( ( ( $this_time % 60 ) < 9 ) || ( $this_time - $last_time > 120 ) ) {
do_store( $first_time );
$first_time = 0;
$last_time = $this_time;
if ( $this_hour != int( $this_time / 3600 ) ) {
$this_hour = int( $this_time / 3600 );
summarise_minutes();
}
}
}
}
main();
The tables are created using the following SQL:
CREATE TABLE IF NOT EXISTS `hour` (
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`user` varchar(32) NOT NULL,
`in` bigint(20) NOT NULL DEFAULT '0',
`out` bigint(20) NOT NULL DEFAULT '0',
UNIQUE KEY `user_time` (`user`,`time`),
KEY `user` (`user`),
KEY `time` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `minute` (
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`user` varchar(32) NOT NULL,
`pid` int(11) DEFAULT NULL,
`in` bigint(20) NOT NULL DEFAULT '0',
`out` bigint(20) NOT NULL DEFAULT '0',
KEY `user` (`user`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
UPDATE 2012-05-11 – noticed a link from serverfault.com so replaced code with the current version I use (which is more stable).
Like this:
Like Loading...
Related
You should replace “sshuser” with “mysqluser”, and similarly for “sshpassword” in your ssh_store.pl