MRTG Log Aggregator

Occasionally, I have needed to provide percentiles on a combined set of interfaces.
This requires a way of adding together samples from a number of log files, even though the sample timestamps might differ from file to file by a few minutes.

Here then is my current hack for doing this. The merged data set is implemented here as a doubly-linked list using nested hashes, not because I make use of these here, but because I lifted it from one of my other log manipulation tools. I will probably return to clean it up as time goes on.

#!/usr/bin/env perl
#
# NAME:         aggregate.pl
#
# AUTHOR:       Philip Damian-Grint
#
# DESCRIPTION:  Synthesize a new MRTG log file from 2 or more other log files.
#
#               This utility expects and generates version 2 MRTG log files,
#               (See http://oss.oetiker.ch/mrtg/doc/mrtg-logfile.en.html), based on a 
#
#               default sampling time of 5 minutes
#               In general there are 600 samples each of 5mins, 30mins, 120mins 
#               and 86400mins. Each dataset is a quintuple:
#               {epoch, in_average, out_average, in_maximum, out_maximum}
#
#               The file with the newest timestamp is used as a template for generating
#               the output file, processed backwards in time.
#
#               Samples from the second and further logfiles are combined with the template
#               according to the following rules:
#
#               1.  Samples from the input logfile which fall between two samples in the
#                   template, are combined into the sample with the higher timestamp
#
#               2.  Samples are combined using basic addition only
#
#               Each of the input files are checked for time synchronisation. If the
#               starting times of any of the second and subsequent input files are more 
#               than 5 minutes adrift from the first input file, the utility aborts.
#
# INPUTS:       Options, Logfile1, Logfile2, ...
#               aggregate.pl [--verbose] Logfile1 [, Logfile2, ...]
#
# OUTPUTS:      Logfile in MRTG format version 2
#               This is written to STDOUT
#
# NOTES:        1.   It should go without saying that running this against live log files while
#                    MRTG is running will have unpredictable results - copy the logfiles to
#                    a location where they will not be disturbed while being processed.
#
#               2.  It is possible that due to occasional variations at sample period
#                   boundaries (e.g. 5mins / 30 mins) and between files, some "samples" in the
#                   merged file might combine one or two samples more than expected.
#                   It would be possible to avoid this by say, adding a further field to each hash
#                   record to count and possibly restrict the samples combined from subsequent files.
#
# HISTORY:      3/2/2012: v1.0 created
#               8/2/2012: v1.1 header detection corrected
#

# PRAGMAS
use strict;

# GLOBALS
local $| = 1;                               # Autoflush STDOUT

# MODULES
use Getopt::Long;

# VARIABLES

# Parameters
my $verbose;

# Working Storage
my @fields;                                 # Holds fields from last record read
my $file_no;                                # Tracks current file being processed
my $inbytes_master;                         # Inbytes counter from the first file
my @keys;                                   # Holds sorted keys for merged dataset
my $outbytes_master;                        # Outbytes counter from the first file
my $prev_time;                              # Remember our previous timestamp
my $record_no;                              # Tracks last record read from current file
my $time_master;                            # First timestamp from first file
my $run_state;                              # Tracks processing phase (first file, subsequent file...)
my %samples;                                # Doubly-linked list representing merged file

# Subroutines
sub record_count {
    print "\r".++$record_no." of ".$file_no;
}

# INITIALISATION

GetOptions ("verbose" => \$verbose );       # Check for verbosity
$prev_time = 0;                             # Reset previous timestamp copy
$run_state = 'INIT';                        # Reset state
$time_master = 0;                           # Reset starting epoch

# MAIN BODY

# Process All Logfiles
while (<>) {
    chomp();                                # Remove carriage return etc
    @fields = ();                           # Clear our temporary holding area
    @fields = (split);                      # Split up our tuple

    # Start of File Processing    
    if (scalar(@fields) == 3) {             # Check for start of file
        print "\nStart of input file, datestamp: ".(scalar localtime(@fields[0]))."\n" if ($verbose);
        $record_no = 0;                     # Reset record counter

        # First file
        if ($run_state eq 'INIT') {         # If this is our first file
            $time_master = @fields[0];      # Capture the header timestamp
            $inbytes_master = @fields[1];   # Capture the header inbytes
            $outbytes_master = @fields[2];  # Capture the header outbytes
            $run_state = 'FIRST';           # And update our state
            $file_no = 1;                   # Start counting input files

        # Subsequent files
        } else {
            # At the end of the first file (only)
            if ($run_state eq 'FIRST') {
                @keys = reverse sort { $a <=> $b } (keys %samples); # Sort our keys
                $run_state = 'SUBSQ';                               # Note that first file has ended
            }
            # And in all cases
            $file_no++;                     # Count input files
            $inbytes_master += @fields[1];  # Add header inbytes to master
            $outbytes_master += @fields[2]; # Add header outbytes to master
            
            # Other files must be within 5 minutes of the first
            die("Header timestamp difference > 5 minutes found in file ".$file_no."\n") if (abs($time_master - @fields[0]) > 300);
        }
        &record_count if ($verbose);        # Update our on-screen counter
        $prev_time = @fields[0];            # Take a copy of this timestamp
        next;                               # Now start processing non-header records
    }

    # Check for "all-files" data mangling
    die("\nIncreasing timestamp found in record ".$record_no." of file ".$file_no."\n") if (@fields[0] > $prev_time);
        
    # First file just populates our template
    if ($run_state eq 'FIRST') {

        # Check for "first-file" data mangling
        die("\nDuplicate timestamp found in record ".$record_no." of file ".$file_no."\n") if (exists ($samples{@fields[0]}));

        # Create a hash entry indexed by datestamp
        $samples{@fields[0]}= {PREV => ($prev_time == @fields[0]) ? undef : $prev_time, NEXT => undef, TUPLE => [@fields[1], @fields[2], @fields[3], @fields[4]]};

        # If not the first item in the list, update the last item's NEXT pointer
        $samples{$prev_time}{NEXT} = @fields[0] if ($record_no > 1);

    # Subsequent files must be merged
    } else {
        foreach (@keys) {
            if ($_ <= @fields[0]) {
                $samples{$_}{TUPLE}[0] += @fields[1];
                $samples{$_}{TUPLE}[1] += @fields[2];
                $samples{$_}{TUPLE}[2] += @fields[3];
                $samples{$_}{TUPLE}[3] += @fields[4];
                last;
            } 
        }
    }
    $prev_time = @fields[0];                # Take a copy of this timestamp
    &record_count if ($verbose);
}

# Were we only given one file? @keys only populated on detection of a second file
die("\nError - only one input file supplied\n") unless (@keys);

# Output Merged File

# First our updated header record
print "$time_master $inbytes_master $outbytes_master\n";

# And then our records in reverse order
foreach (@keys) {
    print "$_ $samples{$_}{TUPLE}[0] $samples{$_}{TUPLE}[1] $samples{$_}{TUPLE}[2] $samples{$_}{TUPLE}[3]\n";
}

MRTG Percentile Calculation

I was recently asked to provide 95th percentile utilisation figures for around 50 WAN interfaces on our network. I've been using MRTG for years, and assumed someone would have contributed something which I could use or customise, but I found nothing.

This then, is my fairly basic hack for processing mrtg-2 log files and calculating the required information.

It's in Perl and contains more documentation than code... You will note that I wasn't brave (stupid?) enough to write my own percentile algorithm...

The actual calculation code is trivial - most of the code is contriving to implement a primitive weighting system to cope with samples covering variable time periods. The code has been tested on Windows under ActivePerl 5.2.12.

#!/usr/bin/env perl
# NAME:   mrtg-ptile.pl
#
# AUTHOR:  Philip Damian-Grint
#
# DESCRIPTION: 
#    Generate percentile calculations for in and out values found
#    in an MRTG log file (version 2).
#    (See http://oss.oetiker.ch/mrtg/doc/mrtg-logfile.en.html)
#
#    In general there are 600 samples each of 5mins, 30mins, 120mins
#    and 86400mins. Each dataset is a quintuple:
#    {epoch, in_average, out_average, in_maximum, out_maximum}
#
#    We want to be able to ask for a variable percentile over a variable
#    length of time stretching back from now.
#
#    We keep track of the effective elapsed time as we go back through the
#    log file. Examination of log files shows that there can be a number
#    of inconsistencies such as variations in timestamp greater or
#    less than expected, and a greater or less number of samples in each
#    bracket.
#
#    To overcome this we compare each timestamp with the previous, and
#    divide it by 300 (seconds) rounded up. The values are repeated the
#    number of times indicated by the dividend.
#
#    So each 5 minute value set will be added once, each 30 minute value set
#    will be added 6 times, and each 2 hour value set will be added 24 times
#    so that we have a number of datasets equivalent to the number of 5min
#    chunks evenly spread over the period being evaluated.
#
# INPUTS:  
#    Logfile (I haven't coded for wildcards)
#    Percentile (I restrict this to an integer between 1 and 99)
#    Time period (I restrict this to 90 days or less)
#
#    The command line arguments are:
#    mrtg-ptile.pl --logfile={filename} \
#                  --percentile={0>x<100} \
#                  --period={y days} \
#                  --verbose
#    Where "\" indicates line wrap.
#
# OUTPUTS:  
#    Percentiles for average bytes per second in, out, maximum in
#    and maximum out
#    These 4 values are output to STDOUT
#
# NOTES:  
#    1. The percentile figures output are based on the figures input,
#       and on the units input. If these relate to router interfaces,
#       they will normally represent bytes per second.
#    2. It should go without saying that running this against live log
#       files while MRTG is running will have unpredictable results.
#       Copy the logfiles to a location where they will not be disturbed
#       while being processed.
#
# HISTORY:  7/12/2010: v1.0 created
#

# PRAGMAS
use strict;

#
# PACKAGES
use Getopt::Long;
use Statistics::Descriptive;

#
# VARIABLES

# Parameters
my $logfile;      # Name of logfile to process
my $percentile;   # Percentile to calculate
my $period;       # Length of time in 24 hour days
my $verbose;      # Flag to request diagnostic information

#
# Working Storage
my $elapsed;      # Seconds between current and previous record's epoch times
my $first_line;   # Used to skim off the first (unused) line in the log file
my $i;            # General purpose loop counter variable
my $in_avg;       # Bps value from field 2 in the current record
my $in_max;       # Bps value from field 4 in the current record
my $inavgstat;    # Statistics::Descriptive object for average IN values
my $inmaxstat;    # Statistics::Descriptive object for maximum IN values
my $last_time;    # Epoch timestamp from the previous record
my $multiplier;   # Number of 5 minute slots represented by the current record
my $out_avg;      # Bps value from field 3 in the current record
my $out_max;      # Bps value from field 5 in the current record
my $outavgstat;   # Statistics::Descriptive object for average OUT values
my $outmaxstat;   # Statistics::Descriptive object for maximum OUT values
my $percentile;   # Contents of the --percentile= command line parameter
my $period;       # Contents of the --period= command line parameter
my $samplesecs;   # Remaining (reporting) period in seconds
my $time;         # Epoch time value from field 1 in the current record

#
# Check that we were called intelligently

GetOptions ("logfile=s" => \$logfile,
   "percentile=i" => \$percentile,
   "period=i" => \$period,
   "verbose" => \$verbose );

if (!($logfile) || !($percentile) || !($period)) {
   die "\nUsage: mrtg-ptile.pl \t--logfile={filename}".
       " \\\n\t\t\t--percentile={integer}".
       " \\\n\t\t\t--period={integer days}\n";
}

#
# Sanity checks on numbers
if ($percentile < 1 || $percentile > 99) {
   die "Percentile must lie between 1 and 99";
}
if ($period > 90) {
   die "Period cannot be greater than 90 days";
} # Only 'cos some of my data older than this is mangled :)

#
# INITIALISATION
$elapsed = 0;                           # Zero elapsed time tracker
open(FILE, "$logfile") or die("Couldn't open file: $logfile \n");
$first_line = <FILE>;             # get header line out of the way
($last_time) = split(" ", $first_line); # capture the first sample time
$samplesecs = $period * 24 * 3600;      # Set up countdown timer

$inavgstat = Statistics::Descriptive::Full->new(); # Initialise stats objects
$inmaxstat = Statistics::Descriptive::Full->new();
$outavgstat = Statistics::Descriptive::Full->new();
$outmaxstat = Statistics::Descriptive::Full->new();

#
# MAIN
while (<FILE>) {
   # Split up our tuple
   ($time, $in_avg, $out_avg, $in_max, $out_max) = (split)[0,1,2,3,4];
   $multiplier = int($elapsed/300);     # Count 5 minute slots

   if ( $samplesecs > $elapsed) {       # if we haven't run out of time...
      $elapsed = $last_time - $time;    # Count elapsed seconds
      $samplesecs -= $elapsed;          # Adjust remaining time period

      if ($verbose) {
         print "Time: ", $time."(".$last_time.
         "), In_Avg: ".$in_avg.", Out_Avg: ".$out_avg.
         ", In_Max: ".$in_max.", Out_Max: ".$out_max.
         ", Elapsed: ".$elapsed.": Post ".$multiplier." times, ".
         $samplesecs . " seconds of samples left\n";
      }

      $last_time = $time;               # track for the next sample
      # post the sample once for every elapsed 5 minutes
      for ($i=1; $i<=$multiplier; $i++) {
         $inavgstat->add_data($in_avg);
         $inmaxstat->add_data($in_max);
         $outavgstat->add_data($out_avg);
         $outmaxstat->add_data($out_max);
      }
   }
}# FINISH
close(FILE);

# Check to see if we ran out of samples
if ($samplesecs > $elapsed) {
   print "Warning: not enough samples found to cover requested period\n";
}

# Output our percentiles
print "In_Avg: ".$inavgstat->percentile($percentile).
      ", Out_Avg: ".$outavgstat->percentile($percentile).
      ", In_Max: ".$inmaxstat->percentile($percentile).
      ", Out_Max: " . $outmaxstat->percentile($percentile);