#!/usr/bin/perl -w

use strict;
use warnings;
use Getopt::Long;
use RRDs;

sub usage($) {
  print <<EOF;
rrdquery.pl version 5

rrdquery.pl selects data from an RRD file with a SQL like approach where each
consolidation function (CF) represents its own table, and each datasource plus
the timestamp are the columns.
Most commandline options are shared with rrdfetch(1). Additional parameters
are explained below.

Usage:
  rrdquery.pl <rrdfile> --info|-i
  rrdquery.pl <rrdfile> <cf> <filter> [--resolution|-r <resolution>]
              [--start|-s <start>] [--end|-e <end>] [--align-start|-a]
              [--daemon|-d <address>] [--utc|-u]
  rrdquery.pl -h
  rrdquery.pl --help

Description:
  --info, -i   Output the names of all round robin archives (RRAs), the names
               of all datasources, as well as the oldest and latest update
               timestamp found in the round robin database (RRD) <rrdfile>
  --utc, -u    Display each timestamp in UTC time instead of local time
  --help       Display common examples
  -h           Display this help ;-)
  <filter>     Select a datasource and optionally filter rows

Filter syntax:
  FUNC(<dsname>) [<comparator> <value>|IS NULL]

  FUNC(...)     Optional aggregate function, one of COUNT(), SUM(), AVG(),
                MIN(), or MAX() (case insensitive)
  <dsname>      The name of a datasource in <rrdfile> (case sensitive)
  <comparator>  Any of =, !=, <> (same as !=), <, <=, >=, or >
  <value>       An integer or float value
  IS NULL       Output any row where <dsname> is null or NaN (case insensitive)

  Whitespace between <dsname>, <comparator>, and/or <value> may be omitted.
  Check for occurences where <dsname> has a non-NULL value can be achieved by
  specifying <dsname> only.

  If no value within the given time range matches the filter, or if the RRD
  file contains no value within in the time range, then rrdquery.pl prints no
  result.
  The COUNT() aggregate function always prints a result of zero or larger.
EOF

  exit($_[0]);
}

sub examples() {
  print <<EOF;
Examples:

1. Print all timestamps and values within the last 24 hours where
   maximum inBytes is larger then 23:
   rrdquery.pl /var/db/rrd/interface-vmx0.rrd MAX "inBytes > 23"

2. Print all timestamps and maximum inBytes within the last 2 weeks:
   rrdquery.pl /var/db/rrd/interface-vmx0.rrd MAX inBytes -s end-2weeks

3, Print the sum of all average outBytes:
   rrdquery.pl /var/db/rrd/interface-vmx0.rrd AVERAGE "sum(outBytes)"

4. Count the occurence of any average outBytes larger or equal than 42:
   rrdquery.pl /var/db/rrd/interface-vmx0.rrd AVERAGE "count(outBytes) >= 42"

5. Print all timestamps and values of all minimum inBytes within
   the last 24 hours:
   rrdquery.pl /var/db/rrd/interface-vmx0.rrd MIN inBytes

6. Print all timestamps and (NULL) values where no value has been added to
   the average consolidation function in the RRD file in February 2023:
   rrdquery.pl /var/db/rrd/interface-vmx0.rrd AVERAGE inBytes is Null \\
     -s 20230201 -e "23:59 28.02.2023"
EOF

  exit(0);
}

sub format_time($$) {
  my ($epoch, $utc) = @_;
  my ($sec, $min, $hour, $day, $mon, $year) = ($utc ?
                                               gmtime($epoch) :
                                               localtime($epoch));

  return sprintf("%04d-%02d-%02d %02d:%02d:%02d",
                 $year + 1900, $mon + 1, $day, $hour, $min, $sec);
}

my ($resolution, $start, $end, $alignstart, $daemon, $utc, $showinfo, $help,
    $examples);

GetOptions(
  "resolution|r=s" => \$resolution,
  "start|s=s"      => \$start,
  "end|e=s"        => \$end,
  "align-start|a"  => \$alignstart,
  "daemon|d=s"     => \$daemon,
  "utc|u"          => \$utc,
  "info|i"         => \$showinfo,
  "h"              => \$help,
  "help"           => \$examples,
) || die;

my ($rrdfile, $cf, @filter) = @ARGV;
my $filter = join(" ", @filter);

examples() if $examples;
usage(0) if $help;
usage(1) if (!$rrdfile || !($showinfo || ($cf && $filter)));

if ($showinfo) {
  # show info
  my $info = RRDs::info($rrdfile);
  my $err = RRDs::error;
  die "Error while getting info from $rrdfile: $err" if $err;

  my %rras;
  my %datasources;
  my $lastupdate;
  my $firstupdate;

  # get the name of all round robin archives and datasources,
  # the oldest update timestamp of all rras, and the latest update
  # timestamp of the database
  foreach (keys %$info) {
    if (/^rra\[(\d+)\]\.cf$/) {
      my $rraidx = $1;
      my $first = RRDs::first($rrdfile, "--rraindex", $rraidx);
      $err = RRDs::error;
      if ($err) {
        die "Error while getting first update of rra $rraidx from $rrdfile: " .
            $err;
      }

      if (!defined $firstupdate || ($first < $firstupdate)) {
        $firstupdate = $first;
      }

      $rras{$info->{$_}} = 0;
    } elsif (/^ds\[([^\]]+)\]\./) {
      $datasources{$1} = 0;
    } elsif ($_ eq "last_update") {
      $lastupdate = $info->{$_};
    }
  }

  print "Round robin archives (RRAs)\n";
  foreach my $rra(sort keys %rras) {
    print "  $rra\n";
  }

  print "\nData sources (DSs):\n";
  foreach my $ds(sort keys %datasources) {
    print "  $ds\n";
  }

  print "\nFirst update: ", $firstupdate,
        " (", format_time($firstupdate, $utc), ")",
        "\nLast update:  ", $lastupdate,
        " (", format_time($lastupdate, $utc), ")\n";
} else {
  # build options for rrdfetch
  my @cmd = ($rrdfile, $cf);
  push @cmd, "-r $resolution" if defined $resolution;
  push @cmd, "-s $start" if defined $start;
  push @cmd, "-e $end" if defined $end;
  push @cmd, "-a" if $alignstart;
  push @cmd, "-d $daemon" if $daemon;

  # SQL-ish aggregates
  my %aggregates = (
    "identity" => sub {
      my ($ts, $utc, $curval, $result) = @_;
      print $ts, " (", format_time($ts, $utc), ") ",
            (defined $curval ? $curval : "NULL"), "\n";
      return $result;
    },
    "count" => sub {
      my ($ts, $utc, $curval, $result) = @_;
      return ($result ? $result + 1 : 1);
    },
    "sum" => sub {
      my ($ts, $utc, $curval, $result) = @_;
      if (!defined $curval) {
        return $result;
      }
      return ($result ? ($result + $curval) : $curval);
    },
    "avg" => sub {
      my ($ts, $utc, $curval, $result) = @_;
      if (!defined $curval) {
        return $result;
      }
      if (defined $result) {
        $result->[0]++;
        $result->[1] += $curval;
      } else {
        $result = [1, $curval];
      }
      return $result;
    },
    "min" => sub {
      my ($ts, $utc, $curval, $result) = @_;
      if (defined $curval && (!defined $result || ($result->[1] > $curval))) {
        $result = [$ts, $curval];
      }
      return $result;
    },
    "max" => sub {
      my ($ts, $utc, $curval, $result) = @_;
      if (defined $curval && (!defined $result || ($result->[1] < $curval))) {
        $result = [$ts, $curval];
      }
      return $result;
    },
  );

  my ($ts, $step, $names, $data) = RRDs::fetch(@cmd);
  my $err = RRDs::error;
  die "Error while fetching data from $rrdfile: $err" if $err;

  # e.g. "sum(outBytes)", "count(inBytes) > 10", "errBytes > 0.1",
  # "inBytes iS NulL", or just "discOutBits"
  if ($filter !~ /^\s*((?<aggregate>count|sum|avg|min|max)\s*\(\s*)?(?<dsname>[^\s=!<>()]+)(\s*\))?((\s*(?<cmp>=|!=|<>|<|<=|>=|>)\s*(?<value>\d+(\.\d+)?))|(\s+(?<isnull>is\s+null)))?\s*$/i) {
    die "Invalid filter: $filter";
  }

  # $value is fixed float given by user filter
  my ($dsname, $cmp, $value, $isnull, $aggregate) = ($+{dsname}, $+{cmp},
                                                     $+{value}, $+{isnull},
                                                     lc($+{aggregate} // ""));
  my $aggregate_func = $aggregates{$aggregate ? $aggregate : "identity"};

  # dsidx holds column index of selected datasource
  my $dsidx;
  for ($dsidx = 0; $dsidx <= $#$names; $dsidx++) {
    last if ($dsname eq $names->[$dsidx]);
  }
  die "No such data source: $dsname" if ($dsidx > $#$names);

  # reference to comparator
  my %cmps = (
    "="  => sub { return defined $_[0] && ($_[0] == $_[1]); },
    "!=" => sub { return defined $_[0] && ($_[0] != $_[1]); },
    "<>" => sub { return defined $_[0] && ($_[0] != $_[1]); },
    "<"  => sub { return defined $_[0] && ($_[0] <  $_[1]); },
    "<=" => sub { return defined $_[0] && ($_[0] <= $_[1]); },
    ">=" => sub { return defined $_[0] && ($_[0] >= $_[1]); },
    ">"  => sub { return defined $_[0] && ($_[0] >  $_[1]); },
  );

  my $cmpfunc;
  if ($isnull) {
    $cmpfunc = sub { return !defined $_[0]; };
  } elsif ($cmp) {
    $cmpfunc = $cmps{$cmp};
  } else {
    $cmpfunc = sub { return defined $_[0]; };
  }

  my $result;
  if ($aggregate eq "count") {
    $result = 0;
  }

  foreach my $line(@$data) {
    my $curval = $line->[$dsidx];
    if (&$cmpfunc($curval, $value)) {
      $result = &$aggregate_func($ts, $utc, $curval, $result);
    }
    $ts += $step;
  }

  if (defined $result) {
    if ($aggregate eq "avg") {
      my $avg = $result->[1] / $result->[0];
      print $avg, "\n";
    } elsif (($aggregate eq "min") || ($aggregate eq "max")) {
      print $result->[0], " (", format_time($result->[0], $utc), ") ",
            $result->[1], "\n";
    } else {
      # count(), sum()
      print $result, "\n";
    }
  }
}