Saturday, August 25, 2007

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 ( 8 hours):

#!/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";


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;
$d_t = sprintf("%4d-%2.2d-%2.2d %2.2d:00:00",$year,$mon,$mday,$hour,$min,$sec);

#!/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";

if ($ARGV[1] =~ /(hours)|(days)|(months)/) {
$period_type = $ARGV[1];
else {
print "\nIncorrect value supplied for type of units\n";
else {
print "\nUsage: period units\nPeriod: The number of values\nUnits: Hours, Days, Months\n\n";
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;

my $graph = GD::Graph::bars->new(85*$num_values, 300);
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); }

Saturday, August 11, 2007

A simple .forward vacation enable/disable script

I got a little tired of manually enabling/disabling peoples vacation AutoReply. So I decided to knock out a simple bash script that does the enable/disable part, leaving me to simply make sure the actual response message was updated and just AT the script for whenever they wanted to leave/come back.

I used to move .forward to dotforward and back when enabling/disabling - so if you're wondering why I'm referencing files called 'dotforward' it's for backwards compatibility - plus I like the idea of setting up dotforward if the user doesn't have any .forward yet and leaving the rest up to the script.


TMP_DATE=`date +%Y%m%d`
EMAIL_SUBJECT="AutoReply Status"

VACATION=$(which vacation)

if [ -z "$1" ]; then
echo "usage: $0 username"

echo "Doing the .forward thing with user: $USER"

if ! [ -e $FORWARD ]; then
echo "No .forward found, is there a dotforward?"
if [ -e $DOTFORWARD ]; then
echo "Found $DOTFORWARD, moving it to $FORWARD"
EMAIL_BODY="Hello $USER, I have enabled your AutoReply E-Mail as of $DATE"
echo "Moved $DOTFORWARD to $FORWARD"
echo "Hmm, there's already a $FORWARD, I'll just add or remove the vacation reference..."
if [ -e $FORWARD ]; then
if grep "vacation" $FORWARD
then echo "Oooh I found a vacation reference in here! Let's DELETE it buwahaha"
sed -e "s!\"|$VACATION $USER\"!!g" $FORWARD > /tmp/$USER_forward-$TMP_DATE
mv /tmp/$USER_forward-$TMP_DATE $FORWARD
EMAIL_BODY="Hello $USER, I have disabled your AutoReply E-Mail as of $DATE"
echo "Didn't find any vacation reference, I'm adding one"
if ! grep "\\$USER," $FORWARD; then
echo "\\$USER," >> $FORWARD
echo " \"|$VACATION $USER\"" >> $FORWARD
EMAIL_BODY="Hello $USER, I have enabled your AutoReply E-Mail as of $DATE"

echo "$FORWARD now looks like:"
echo `cat $FORWARD`

echo "$EMAIL_BODY" | mail -s "$SUBJECT" $USER

Wednesday, August 08, 2007

Mikrotik RouterOS Firewall Script

The following will hunt through the firewall filter list and enable/disable all rules whose comment is "Drop_Toggle". Usefull if you want to toggle particular sets of filters periodically etc.

# Enable Drop Rules
:global list ""; :foreach i in [/ip firewall filter find] \
do={:if ([:find [/ip firewall filter get $i comment] "Drop_Toggle"]=0) \
do={/ip firewall filter set $i disabled=no} };

# Disable Drop Rules
:global list ""; :foreach i in [/ip firewall filter find] \
do={:if ([:find [/ip firewall filter get $i comment] "Drop_Toggle"]=0) \
do={/ip firewall filter set $i disabled=yes}};

Monday, August 06, 2007

Mirroring a Plesk vhost script

The following script will mirror a vhost from a Plesk managed server. It is up to you to modify the Apache vhost configuration includes (usually there's one created by Plesk in /etc/httpd/conf.d or the like).

# RSYNC/SED script to mirror a Plesk host
# 2007­08­01 Ben Johns

# Requirements:
# SSH Pub/Priv keys shared on both hosts
# ssh­keygen ­-t dsa ­-b 1024 ­-f `whoami`-­`hostname` (NO PASSPHRASE!)
# copy the resultant .pub file to the remote host and append it too
# the RSYNC_USER's .ssh/authorized_keys file.

# RSYNC Version >2.6.3
# HTTPD.INCLUDE needs to be manually configured to suit the config
# of the local host. Ie copy the relevant sections from the remote hosts
# plesk httpd conf to this host. Usually done somewhere in /etc/httpd or /etc/apache.

# REM_HOST: The remote host to mirror
# RSYNC_USER: The user account on the remote host that has permission
# to copy the intended files.
# RSYNC_OPTS: Parameters to use with the rsync command
# SSH_KEY: The private DSA key to use for SSH authentication
# RSYNC_VHOST_SRC_PATH: Path to the source virtual host files on the remote host
# RSYNC_VHOST_SRC_DIR: Directory of the source virtual host files on the remote host
# RSYNC_VHOST_DST_PATH: Path to the destination on the local host
# SED_VHOST_MOD_FILE: Location of the SED parameters to modify VHOST config files

RSYNC_OPTS="-­­avz ­­--perms ­-q ­­--delete­during"

rsync ­$RSYNC_OPTS \
rsync ­$RSYNC_OPTS ­­--include "*/" ­­--include "*.include" ­­--exclude "*" \
-­e "ssh ­-i $SSH_KEY ­-l $RSYNC_USER" \

for file in $RSYNC_VHOST_DST_PATH$RSYNC_VHOST_SRC_DIR/conf/httpd.include ; do
sed ­-f $SED_VHOST_MOD_FILE "$file" > tmp_file
mv tmp_file "$file"
echo "Modified $file"


apache2ctl graceful