Recording RouterOS's IP Accounting Data

There are a number of ways to gather data from a Mikrotik RouterOS based router. The easiest would be it's 'Accounting Web Access' feature where you can go to http://routeros_addr/accounting/ip.cgi and view a list of ip pairs similar to a basic netflow output.

Using this feature I wrote the below perl scripts to collect the data into a DB file. To keep things reasonable I set it record the data per the hour, meaning my smallest unit of measurement is hourly. While I could have simply used a MySQL database to dump the data into, I wanted to maintain a level of portability and simplicity - it sucks having to install/configure/run a fully fledged RDBMS just to view some basic data usage statistics.

The first script is used to gather the data from the MT router and store it into the db_file, the second script uses GD::Graph to produce bar charts using the data stored in the db_file. I'll be writing more scripts that dumps the contents of the db_file into a .xls spreadsheet for manual reports - handy for tracking down heavy users and to use as evidence if there are any ISP account discrepancies.

Apologies for the untidy code and the lack of formatting. Blogger doesn't provide any 'code markup' function and I cbf'd looking for alternatives. I'll fix it up when I can.

Example graph output (graph.pl 8 hours):

gather.pl:

#!/usr/bin/perl -w

use strict;
use LWP::Simple;
use MLDBM 'DB_File';
use Time::Local;

my $arg0 = $ARGV[0];
my $arg1 = $ARGV[1];

my $ip_accounting_url="http://<routeros ip>/accounting/ip.cgi";
my $accounting_mldbm_data_db = "~/accounting_data.mldbm";

tie my %h, 'MLDBM', $accounting_mldbm_data_db or die $!;

my ($timestamp) = &time_stamp();
my $epoch = time();
# print "\n Epoch set to: $epoch\n";

&gather_ip_accounting($ip_accounting_url);

sub gather_ip_accounting {
my $url = $_[0];
my ($src, $dst, $bytes, $packets, $src_usr, $dst_usr);

foreach my $line (split(/\n/, get($url))) {
($src, $dst, $bytes, $packets, $src_usr, $dst_usr) = split(" ", $line);

if ($dst && $dst =~ /(192\.168\.)|(10\.2\.)|(172\.16\.)/){
my $h_dst = $h{$dst . "_" . $timestamp};
$h_dst->{dst} = $dst;
# $h_dst->{src} = $src;
$h_dst->{bytes} += $bytes;
$h_dst->{packets} += $packets;
# $h_dst->{src_usr} = $src_usr;
$h_dst->{dst_usr} = $dst_usr;
$h_dst->{epoch} = $epoch;
$h{$dst . "_" . $timestamp} = $h_dst;
}
}
}

# use Data::Dumper;
# print Dumper(%h);

untie %h;

sub time_stamp {
my ($d_t);
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);

$year += 1900;
$mon++;
$d_t = sprintf("%4d-%2.2d-%2.2d %2.2d:00:00",$year,$mon,$mday,$hour,$min,$sec);
return($d_t);
}


graph.pl:

#!/usr/bin/perl -w

use strict;
use LWP::Simple;
use MLDBM 'DB_File';
use Time::Local;
use GD::Graph::bars;

my ($num_values, $period_type);
if ($ARGV[0] && $ARGV[1]) {
if ($ARGV[0] =~ /\d+/) {
$num_values = $ARGV[0];
}
else {
print "\nIncorrect value supplied for number of units\n";
exit;
}

if ($ARGV[1] =~ /(hours)|(days)|(months)/) {
$period_type = $ARGV[1];
}
else {
print "\nIncorrect value supplied for type of units\n";
exit;
}
}
else {
print "\nUsage: period units\nPeriod: The number of values\nUnits: Hours, Days, Months\n\n";
exit;
}
print "\n Gathering $num_values $period_type worth of data from db!\n";

my $epoch = time();

my $accounting_mldbm_data_db = "~/accounting_data.mldbm";
my $graph_image_file = "~/accounting_data_" . $num_values . "_" . $period_type . "_" . $epoch . ".png";

tie my %h, 'MLDBM', $accounting_mldbm_data_db or die $!;



#else {
# &print_period_summary($arg0, $arg1);
#}

my($graphvalues, @graphvalues_tmp);
my $period_total = 0;
my $i = 0;
while ($i <= $num_values) {
# print "$i\n";
@graphvalues_tmp = &print_total($i, $period_type);
my $data = $graphvalues_tmp[0];
my $epoch = $graphvalues_tmp[1];
my $HMS = &epoch_to_MDHMS($epoch);
push @{$graphvalues->[0]}, $HMS;
push @{$graphvalues->[1]}, $data;
$period_total = $period_total + $data;
$i++;
}

my $graph = GD::Graph::bars->new(85*$num_values, 300);
$graph->set(
x_label => "$period_type (latest towards the left) Period Total: $period_total",
y_label => 'Mbytes',
title => "Total Mbytes (Over $num_values $period_type)",
transparent => '0',
show_values => '1',
bar_spacing => '2',
) or warn $graph->error;

my $image = $graph->plot($graphvalues) or die $graph->error;

open(IMG, ">$graph_image_file") or die $!;
binmode IMG;
print IMG $image->png;

# use Data::Dumper;
# print Dumper($graphvalues);

untie %h;

sub print_total {
my $h_total=0;
my ($h_row, $h_column, $h_bytes, $h_dst);

my ($num, $period) = @_;
my ($epoch_start, $epoch_end) = &epoch_period($num, $period);

for my $h_row ( keys %h ) {
if ($h{$h_row}{epoch} >= $epoch_start && $h{$h_row}{epoch} <= $epoch_end) { $h_bytes = $h{$h_row}{bytes}; $h_dst = $h{$h_row}{dst}; $h_total = $h_total + $h_bytes; } } my $formatted_total = sprintf("%.3f", $h_total/1024/1024); return($formatted_total, $epoch_start); } sub epoch_period { my ($past_count, $period) = @_; my ($epoch_period_start, $epoch_period_end); my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time); my ($start_hour, $end_hour); if ($period eq "hours") { $start_hour = $hour-$past_count; $end_hour = $hour-$past_count; } elsif ($period eq "days") { $mday = $mday-$past_count; $start_hour = '00'; $end_hour = '23'; } elsif ($period eq "months") { $mon = $mon-$past_count; # $mday = '00'; # $hour = '00'; } $epoch_period_start = timelocal(00,00,$start_hour,$mday,$mon,$year); print "Start: $epoch_period_start\n"; $epoch_period_end = timelocal(59,59,$end_hour,$mday,$mon,$year); print "End: $epoch_period_end\n"; # print "SUB EPOCH_PERIOD: $epoch_period_start, $epoch_period_end\n"; return($epoch_period_start, $epoch_period_end); } sub epoch_to_MDHMS { my $epoch = $_[0]; my ($sec, $min, $hour, $mday, $mon) = (localtime($epoch))[0,1,2,3,4]; my $mdhms = $mon+1 . "-" . $mday . " " . sprintf("%02d", $hour) . ":" . sprintf("%02d", $min) . ":" . sprintf("%02d", $sec); return($mdhms); }