#!/usr/bin/perl -w
#
# getStatus.pl
#
# Copyright (C) 2011-2013 Cesnet z.s.p.o
#
# Use of this source is governed by a BSD-style license, see LICENSE file.

use strict;
use Getopt::Std;
use DBI;
use DBD::mysql;
use Format::Human::Bytes;
use FindBin qw($RealBin $RealScript);
use lib "$FindBin::RealBin/../lib";
use WardenCommon;



################################################################################
#				VARIABLES
################################################################################
our $VERSION = "2.2";
my $etc = "$FindBin::RealBin/../etc";

# read config file
my $conf_file = "$etc/warden-server.conf";
our $BASEDIR		= undef
our $SYSLOG             = undef;
our $SYSLOG_VERBOSE     = undef;
our $SYSLOG_FACILITY    = undef;
our $DB_NAME            = undef;
our $DB_USER            = undef;
our $DB_PASS            = undef;
our $DB_HOST            = undef;
our $MAX_EVENTS_LIMIT 	= undef;
unless (do $conf_file) {
  die("Errors in config file '$conf_file': $@") if $@;
  die("Can't read config file '$conf_file': $!") unless defined $_;
  # if $_ defined, it's retvalue of last statement of conf, for which we don't care
}



################################################################################
#                               FUNCTIONS
################################################################################
sub usage {
  print "Usage: $RealScript [without parameters]\n";
  exit 1;
}



################################################################################
#                               MAIN
################################################################################
our ($opt_h);

die usage unless getopts("h");
my $help = $opt_h;

# catch help param
if ($help) {
  usage;
}

# superuser controle
my $UID = $<;
if ($UID != 0) {
  WardenCommon::errMsg("You must be root for running this script!");
}

# connect to DB
our $DBH = DBI->connect("DBI:mysql:database=$DB_NAME;host=$DB_HOST", $DB_USER, $DB_PASS, {RaiseError => 1, mysql_auto_reconnect => 1})
           || die "Could not connect to database: $DBI::errstr";

#-----------------------------------------------------------------------------
# Warden server variables
my $package_version 	= WardenCommon::trim(`cat /opt/warden-server/etc/package_version`);
my $version 		= substr($package_version, 14);
my $hostname 		= WardenCommon::trim(`hostname -f`);
my $ip_address 		= WardenCommon::trim(`hostname -i`);
my $port		= WardenCommon::trim(`netstat -pln | grep apache | cut -f 4 -d ":"`);
my ($syslog, $syslog_verbose);
if ($SYSLOG == 1) {
  $syslog = "enabled";
} else {
  $syslog = "disabled";
}
if ($SYSLOG_VERBOSE == 1) {
  $syslog_verbose = "enabled";
} else {
  $syslog_verbose = "disabled";
}

#-------------------------------------------------------------------------------
# Warden server status

# size of database events
my $sth = $DBH->prepare("SELECT data_length + index_length FROM information_schema.TABLES WHERE table_schema = ? AND TABLE_NAME = ?") or die "Cannot prepare statement: " . $DBH->errstr;
$sth->execute('warden', 'events') or die "Cannot execute statement: " . $sth->errstr;
my $size = $sth->fetchrow();
my $db_size = defined $size ? Format::Human::Bytes::base10($size) : "none";

# sum of records in table events
$sth = $DBH->prepare("SELECT count(*) FROM events WHERE valid = 't';") or die "Cannot prepare statement: " . $DBH->errstr;
$sth->execute or die "Cannot execute statement: " . $sth->errstr;
my $events_sum = $sth->fetchrow();
if (!defined $events_sum) {
  $events_sum = "none";
}

# sum of records in table events (with invalid)
$sth = $DBH->prepare("SELECT count(*) FROM events;") or die "Cannot prepare statement: " . $DBH->errstr;
$sth->execute or die "Cannot execute statement: " . $sth->errstr;
my $events_sum_inv = $sth->fetchrow();
if (!defined $events_sum_inv) {
  $events_sum_inv = "none";
}

# id of last record in table events
$sth = $DBH->prepare("SELECT max(id) FROM events WHERE valid = 't';") or die "Cannot prepare statement: " . $DBH->errstr;
$sth->execute or die "Cannot execute statement: " . $sth->errstr;
my $events_last_id = $sth->fetchrow();
if (!defined $events_last_id) {
  $events_last_id = "none";
}

# id of last record in table events (with invalid)
$sth = $DBH->prepare("SELECT max(id) FROM events;") or die "Cannot prepare statement: " . $DBH->errstr;
$sth->execute or die "Cannot execute statement: " . $sth->errstr;
my $events_last_id_inv = $sth->fetchrow();
if (!defined $events_last_id_inv) {
  $events_last_id_inv = "none";
}

# timestamp of first record in table events
$sth = $DBH->prepare("SELECT received FROM events WHERE id = (SELECT min(id) FROM events WHERE valid = 't');") or die "Cannot prepare statement: " . $DBH->errstr;
$sth->execute or die "Cannot execute statement: " . $sth->errstr;
my $events_first_timestamp = $sth->fetchrow();
if (!defined $events_first_timestamp) {
  $events_first_timestamp = "none";
}

# timestamp of first record in table events (with invalid)
$sth = $DBH->prepare("SELECT received FROM events WHERE id = (SELECT min(id) FROM events);") or die "Cannot prepare statement: " . $DBH->errstr;
$sth->execute or die "Cannot execute statement: " . $sth->errstr;
my $events_first_timestamp_inv = $sth->fetchrow();
if (!defined $events_first_timestamp_inv) {
  $events_first_timestamp_inv = "none";
}

# timestamp of last record in table events
$sth = $DBH->prepare("SELECT received FROM events WHERE id = (SELECT max(id) FROM events WHERE valid = 't');") or die "Cannot prepare statement: " . $DBH->errstr;
$sth->execute or die "Cannot execute statement: " . $sth->errstr;
my $events_last_timestamp = $sth->fetchrow();
if (!defined $events_last_timestamp) {
  $events_last_timestamp = "none";
}

# timestamp of last record in table events (with invalid)
$sth = $DBH->prepare("SELECT received FROM events WHERE id = (SELECT max(id) FROM events);") or die "Cannot prepare statement: " . $DBH->errstr;
$sth->execute or die "Cannot execute statement: " . $sth->errstr;
my $events_last_timestamp_inv = $sth->fetchrow();
if (!defined $events_last_timestamp_inv) {
  $events_last_timestamp_inv = "none";
}

# sum of records in table clients
$sth = $DBH->prepare("SELECT count(*) FROM clients WHERE valid = 't';") or die "Cannot prepare statement: " . $DBH->errstr;
$sth->execute or die "Cannot execute statement: " . $sth->errstr;
my $clients_sum = $sth->fetchrow();
if (!defined $clients_sum) {
  $clients_sum = "none";
}

# sum of records in table clients (with invalid)
$sth = $DBH->prepare("SELECT count(*) FROM clients;") or die "Cannot prepare statement: " . $DBH->errstr;
$sth->execute or die "Cannot execute statement: " . $sth->errstr;
my $clients_sum_inv = $sth->fetchrow();
if (!defined $clients_sum_inv) {
  $clients_sum_inv = "none";
}

#-------------------------------------------------------------------------------
# Statistics of active registered senders
my $hash_ref;
if ($clients_sum != 0) {
  $sth = $DBH->prepare("SELECT clients.client_id, clients.hostname, clients.service, count(*), max(received) FROM events LEFT JOIN clients ON (events.hostname=clients.hostname AND events.service=clients.service) WHERE clients.valid='t' AND events.valid='t' GROUP BY client_id;") or die "Cannot prepare statement: " . $DBH->errstr;
  $sth->execute or die "Cannot execute statement: " . $sth->errstr;
  my ($client_id, $hostname, $service, $count, $timestamp, $client_status);
  $hash_ref = $sth->fetchall_hashref("client_id");
}

#-------------------------------------------------------------------------------
# Print status info output

print "Warden server variables:\n";
print "========================\n";
print "SERVER_VERSION:\t\t$version\n";
print "HOSTNAME:\t\t$hostname\n";
print "IP_ADDRESS:\t\t$ip_address\n";
print "PORT:\t\t\t$port\n";
print "BASEDIR:\t\t$BASEDIR\n";
print "DB_NAME:\t\t$DB_NAME\n";
print "DB_USER:\t\t$DB_USER\n";
print "DB_HOST:\t\t$DB_HOST\n";
print "SYSLOG:\t\t\t$syslog\n";
print "SYSLOG_VERBOSE:\t\t$syslog_verbose\n";
print "SYSLOG_FACILITY:\t$SYSLOG_FACILITY\n";
print "MAX_EVENTS_LIMIT:\t$MAX_EVENTS_LIMIT\n";
print "\n";

print "Warden server status:\n";
print "=====================\n";
print "Database size:\t\t\t\t\t$db_size\n";
print "Count of saved events:\t\t\t\t$events_sum\n";
print "Count of saved events (with invalid):\t\t$events_sum_inv\n";
print "Last ID in events table:\t\t\t$events_last_id\n";
print "Last ID in events table (with invalid):\t\t$events_last_id_inv\n";
print "Time of first inserted event:\t\t\t$events_first_timestamp (UTC)\n";
print "Time of first inserted event (with invalid):\t$events_first_timestamp_inv (UTC)\n";
print "Time of latest inserted event:\t\t\t$events_last_timestamp (UTC)\n";
print "Time of latest inserted event (with invalid):\t$events_last_timestamp_inv (UTC)\n";
print "Count of registered clients:\t\t\t$clients_sum\n";
print "Count of registered clients (with invalid):\t$clients_sum_inv\n";
print "\n";

if ($clients_sum != 0) {
  print "Statistics of ACTIVE and VALID registered senders:\n";
  print "==================================================\n";
  print "+----------------------------------------------------------------------------------------------------------------+\n";
  print "| Client ID  | Hostname                       | Service                   | Stored events | Last insertion (UTC) |\n";
  print "+----------------------------------------------------------------------------------------------------------------+\n";
  foreach my $key (sort {$a<=>$b} keys %$hash_ref) {
    printf("| %-10s ", $hash_ref->{$key}->{client_id} || "unknown");
    printf("| %-30s ", $hash_ref->{$key}->{hostname}|| "unknown");
    printf("| %-25s ", $hash_ref->{$key}->{service} || "unknown");
    printf("| %-13s ", $hash_ref->{$key}->{"count(*)"} || "unknown");
    printf("| %-20s |\n", $hash_ref->{$key}->{"max(received)"} || "unknown");
  }
  print "+----------------------------------------------------------------------------------------------------------------+\n";
  print "\n";
}

print "Current server status in:\t" . scalar localtime(time) . "\n";

exit 0;