Posting from a P1i

Well, it'll take some getting used to but it isn't that hard.

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); }

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.

#!/bin/bash

DATE=`date`
TMP_DATE=`date +%Y%m%d`
EMAIL_SUBJECT="AutoReply Status"
EMAIL_FROM="blah@blah.com"

USER="$1"
FORWARD="/home/$USER/.forward"
DOTFORWARD="/home/$USER/dotforward"
VACATION=$(which vacation)

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

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"
mv $DOTFORWARD $FORWARD
echo "Moved $DOTFORWARD to $FORWARD"
fi
else
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"
else
echo "Didn't find any vacation reference, I'm adding one"
if ! grep "\\$USER," $FORWARD; then
echo "\\$USER," >> $FORWARD
fi
echo " \"|$VACATION $USER\"" >> $FORWARD
EMAIL_BODY="Hello $USER, I have enabled your AutoReply E-Mail as of $DATE"
fi
fi
fi

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

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

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}};

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).


#!/bin/bash
# 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

REM_HOST="web.server.com"
RSYNC_USER="rsync"
RSYNC_OPTS="-­­avz ­­--perms ­-q ­­--delete­during"
SSH_KEY="/var/www/rsync_ssh_key"
RSYNC_VHOST_SRC_PATH="/home/httpd/vhosts/"
RSYNC_VHOST_SRC_DIR="vhost_directory"
RSYNC_VHOST_DST_PATH="/var/www/vhosts/"
SED_VHOST_MOD_FILE="/var/www/vhost_include.sed"

rsync ­$RSYNC_OPTS \
-­e "ssh ­-i $SSH_KEY ­-l $RSYNC_USER" \ $RSYNC_USER@$REM_HOST:$RSYNC_VHOST_SRC_PATH$RSYNC_VHOST_SRC_DIR $RSYNC_VHOST_DST_PATH
rsync ­$RSYNC_OPTS ­­--include "*/" ­­--include "*.include" ­­--exclude "*" \
-­e "ssh ­-i $SSH_KEY ­-l $RSYNC_USER" \
$RSYNC_USER@$REM_HOST:$RSYNC_VHOST_SRC_PATH$RSYNC_VHOST_SRC_DIR $RSYNC_VHOST_DST_PATH

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"
done

chmod ug+rwx ­R $RSYNC_VHOST_DST_PATH$RSYNC_VHOST_SRC_DIR

apache2ctl graceful

Planning for high traffic

The most worrying project on my list at this time is working out how to achieve as much grunt as possible to withstand the estimated traffic from the first Steve Irwin day tribute website since his passing.

Currently the Zoo has a single primary server hosting all sites and a secondary web server sharing the load on a few sites. It's not the perfect model by far and I intend on tidying it up as follows.

What I plan to do is install MySQL 5.x on the secondary server and ready it for replication. Then I will dump the contents from the existing MySQL 4.x databases and point all the websites at it. Once I'm satisfied that it's functioning as expected (I'm in the process of testing this on the bench). Then I will upgrade the MySQL 4.x to 5.x on the primary server and begin multi-master replication with the other. This completes stage one of the preparations.

With the databases in place and functioning, I will work out how to replicate all vhosts between the two servers. Plesk, the web control panel operating on both servers, makes this more complex than it really needs to be since I will also need to create the client/domain accounts for each of the replicated sites. When I have worked out what is required I will script the synchronization as much as possible and hopefully end up with near 100% automation. I may need to tap into Plesk's API to do this. This will complete stage two.

With replication of both the databases and general structures taking place between the two existing servers I can now introduce more servers and the greater complexity they will bring.

I will be working towards four application servers just for static content/scripts and a single localised database server. I intend to just have raw boxes running either Redhat or FreeBSD, no fancy control panels getting in the way. This will allow me to script everything with simplicity and provide a basic configuration to each server. I will replicate the data from the first of the existing servers to one of the new application servers and from there to each of the remaining three. This is so I can keep the amount of public traffic between the servers to a minimum. I will introduce the new database server into the multi-master replication loop and point the four new application servers at it. This completes stage three.

Once I am satisfied that each application server is connecting to the database server over their private network and that the database server is successfully replicating the databases from the existing two servers I will set up the load balancer to include the four new application servers. We may need to cut over to a new load balancer since the existing one may not support this many servers.

This setup will provide me with six front end servers and three database servers - with a bit of sharing of resources here and there. The following diagram shows what I intend on achieving.



Relevant links:
ONLamp Advanced MySQL Replication Techniques
MySQL 5.0 Manual - Replication
Rsync
SWSoft Plesk - Upgrading MySQL

What have I been doing?

Been a while since I last posted so time for a update post!

Revamping the old network
The network at the Zoo was a miserable mess - it took me a while to audit what was in place and to devise a topology that would best address the current and future needs of the Zoo. Now the Zoo has a VLAN'd network consisting of dedicated Administration, Point-of-Service and VoIP subnets, OSPF routing at the core, a DMZ, traffic policing and shaping capabilities and VPN (PPTP/L2TP and IPSec) capabilities.

I achieved all this by using two rackmounted WRAP1-2's from Yawarra and a cheap Asus GigaX 2024 switch. I loaded Mikrotik RouterOS 2.9.42 onto the two WRAP1-2's and set up 802.1q VLANs on the switch. The VLANs are routed on the first WRAP1-2 which then connects onto the DMZ where the other WRAP1-2 and Cisco 857/877 routers exist with OSPF routing throughout. The second WRAP1-2 holds up the 1Mbit Unisky wireless connection (PPPoE, over wireless... yuk) and hopefully a substantial fibre based service from someone, such as a 2Mbit E1/G.703 service.



I used pairs of Cisco 8xx series routers to hold up VPN links between the Zoo and its newly opened Mooloolaba retail store. A pair of 857's hold up a general Point-of-Service/LAN traffic IPSec tunnel. In addition to that a pair of 877's hold up a VoIP/Video IPSec tunnel with QoS. The two DSLs are 8Mbit/384Kbit links supplied by Bigpond. Having dedicated 'pairs' of routers/DSL for VPN connectivity is overkill but it's still cheaper than a single fibre service.

These changes provided the framework for the following additions to the network infrastructure.

A new phone system
The Zoo's old Siemens key system was well and truely past its time and was needing upgrades which proved to be exorbitantly costly to do. A new Alcatel OmniPCX PBX was selected and installed by company called Nexon Asia Pacific. Along with the digital and analog extensions a number of VoIP extensions are provided including wireless VoIP sets. Best practice says to establish a dedicated subnet for the PBX/VoIP services to reside within so as to isolate it from the general traffic of the other networks. Having VLAN capability is useful as I can locate the phones nearly anywhere and still keep them within the VoIP subnet. However while the phones support VLANs, they don't want to communicate with the Asus switch.

First it was wireless and whales, now its wireless and... um... elephants?
I will soon have a Zoo wide wireless network built up of Symbol WS5100 and AP300s. These were provided by Barcode Dynamics in addition to inventory/asset tracking equipment. The WS5100 is useful in that it can map VLANs to WLANs - allowing me to simply create wireless extensions of the existing networks with no physical modifications. However security becomes a concern with the absence of a router/firewall - the WS5100 addresses this by supporting WPA1/2, 802.1x and firewall policies. I will also limit transit between the networks and wireless infrastructure via the routers.

To start with the wireless will be used for mobile VoIP. Since the Alcatel mobile sets are basically Spectralink reference designs I can simply apply the pre-configured Spectralink QoS policy on the WS5100 to that WLAN so that it grants expedited access to the wireless bandwidth to VoIP traffic. In the future we will also implement mobile Point-of-Service terminals, either PDA style units or small form factor PCs. There's also the possibility that the roaming photographers could also use the coverage to upload their digital photo's in real-time to the on-site photography lab.

I've just finished setting up a outdoor enclosure for one of the AP300's. It's a pity that the AP300 doesn't have an outdoor variant. The supplied enclosures were just bare boxes, luckily they came with the backing board. However I had to make up the pole brackets myself using some angle brackets, u-bolts and pop-rivets.





Mobile VPN over Telstra's NextG
For the newly launched Whale One vessel the Zoo has established a NextG mobile data service. To connect the boat to this service a ruggedised NextG modem/router was installed on the boat with a 7dBi collinear antenna. The router comes with a PPTP VPN client so I have set this to establish a VPN back to the Zoo. This allows the two Point-of-Service terminals to communicate back to the Zoo's POS services for EFT transactions and accounting/stock control. Under testing we managed to maintain a connection out to 10km to sea and sustain an average data rate of 1.5Mbit/sec. I have yet to test the link with the POS systems running.