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

package Warden;

use strict;
use DBI;
use DBD::mysql;
use Format::Human::Bytes;
use Sys::Syslog qw(:DEFAULT setlogsock);
Sys::Syslog::setlogsock('unix');
use File::Basename;
use Net::CIDR::Lite;
use DateTime;
use MIME::Base64;
use Crypt::X509;

our $VERSION = "2.1";


################################################################################
#			READING OF CONFIGURATION VARIABLES
################################################################################

my $conf_file = "/opt/warden-server/etc/warden-server.conf"; # path is updated by install.sh
our $FACILITY	= undef;
our $DB_NAME	= undef;
our $DB_USER	= undef;
our $DB_PASS	= undef;
our $DB_HOST	= undef;
our $MAX_EVENTS_LIMIT	= undef; # server events limit
our %VALID_STRINGS	= undef;

# load set variables by user
unless (do $conf_file) {
  errMsg("Errors in config file '$conf_file': $@") if $@;
  errMsg("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
}


################################################################################
#				VARIABLES
################################################################################

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


################################################################################
#				LOCAL FUNCTIONS
################################################################################

#-------------------------------------------------------------------------------
# errMsg - print error message and die
#-------------------------------------------------------------------------------
sub errMsg
{
  my $msg = shift;
  $msg = trim($msg);
  print $msg . "\n";
  exit 1;
} # End of errMsg


#-------------------------------------------------------------------------------
# trim - remove whitespace from the start and end of the string
#-------------------------------------------------------------------------------
sub trim
{
  my $string = shift;
  $string =~ s/^\s+//;
  $string =~ s/\s+$//;
  return $string;
} # End of trim


#-------------------------------------------------------------------------------
# write2log - writing message to syslog
#-------------------------------------------------------------------------------
sub write2log
{
  my $priority	= shift;
  my $msg 	= shift;
  my $filename	= File::Basename::basename($0);

  Sys::Syslog::openlog($filename, "cons,pid", $FACILITY);
  Sys::Syslog::syslog("$priority", "$msg");
  Sys::Syslog::closelog();
} # End of write2log


#-------------------------------------------------------------------------------
# getAltNames - parse Alternate names from SSL certifiate
#-------------------------------------------------------------------------------
sub getAltNames
{
  my @an_array;
  my $cn = $ENV{'SSL_CLIENT_S_DN_CN'};

  push(@an_array, $DBH->quote($cn));
  my @a = split("\n", $ENV{'SSL_CLIENT_CERT'});
  pop @a;
  shift @a;
  my $der = decode_base64(join("", @a));
  my $decoded= Crypt::X509->new(cert => $der);

  foreach my $tmp (@{$decoded->SubjectAltName}) {
    if($tmp =~ s/dNSName=//){
      push(@an_array, $DBH->quote($tmp));
    }
  }
  my $alt_names = join(',', @an_array);
  return $alt_names;
}


#-------------------------------------------------------------------------------
# authorizeClient - authorize client by CN,AN and source IP range
#-------------------------------------------------------------------------------

sub authorizeClient
{
  my ($alt_names, $ip, $service_type, $client_type, $function_name) = @_;
  my $sth;

  # obtain cidr based on rigth common name and alternate names, service and client_type
  if($function_name eq 'saveNewEvent') {
    $sth = $DBH->prepare("SELECT hostname, ip_net_client, receive_own_events 
                          FROM clients WHERE hostname IN ($alt_names) AND service = ? AND client_type = ? 
                          ORDER BY SUBSTRING_INDEX(ip_net_client,'/', -1) DESC;");
  } elsif($function_name eq 'getNewEvents') {
    $sth = $DBH->prepare("SELECT hostname, ip_net_client, receive_own_events 
                          FROM clients WHERE hostname IN ($alt_names) AND (type = ? OR type = '_any_') AND client_type = ? 
                          ORDER BY SUBSTRING_INDEX(ip_net_client,'/', -1) DESC;");
  }

  if (!defined $sth) { die("Cannot prepare authorization statement in $function_name: $DBI::errstr\n")}
  $sth->execute($service_type, $client_type);

  my ($an, $cidr, $receive_own, $cidr_list);
  my $correct_ip_source = 0;
  my %ret;

  while(($an, $cidr, $receive_own)  = $sth->fetchrow()) {
    my $cidr_list = Net::CIDR::Lite-> new -> add($cidr);

    $ret{'dns'} = $an;
    $ret{'cidr'} = $cidr;
    $ret{'receive_own'} = $receive_own;

    if ($cidr_list->bin_find($ip)) {
      $correct_ip_source = 1;
      last;
    }
  }

  # check if client is registered
  if ($sth->rows == 0) {
    write2log ("err", "Unauthorized access to '$function_name' from: '$ip'; CN(AN): $alt_names; used service: '$service_type' - client is not registered");
    die("Access denied - client is not registered at warden server!");
    return undef;
  }

  # check if client has IP from registered CIDR
  if (!$correct_ip_source) {
    write2log ("err", "Unauthorized access to '$function_name' from: '$ip'; CN(AN): $alt_names; used service: '$service_type' - access from bad subnet: Registered subnet '" . $ret{'cidr'} . "'");
    die("Access denied - access from unauthorized subnet!");
    return undef;
  }

  return %ret;
} # END of authorizeClient


################################################################################
# 				SOAP Functions
################################################################################

#-----------------------------------------------------------------------------
# saveNewEvent - save new received event into database
#-----------------------------------------------------------------------------
sub saveNewEvent
{
  my ($class, $data) = @_;
  my ($sth, $cidr_list);

  # client network information
  my $cn	= $ENV{'SSL_CLIENT_S_DN_CN'};
  my $alt_names = getAltNames(undef);
  my $ip	= $ENV{'REMOTE_ADDR'};

  # variables defined by server
  my $function_name	= 'saveNewEvent';
  my $client_type	= 's';			# incoming client MUST be sender
  my $valid		= 't';			# registered sender has valid events
  my $received		= DateTime->now;	# time of event delivery (UTC)

  # parse object (event) parameters
  my $service		= $data->{'SERVICE'};
  my $detected		= $data->{'DETECTED'};
  my $type		= $data->{'TYPE'};
  my $source_type	= $data->{'SOURCE_TYPE'};
  my $source		= $data->{'SOURCE'};
  my $target_proto	= $data->{'TARGET_PROTO'};
  my $target_port	= $data->{'TARGET_PORT'};
  my $attack_scale 	= $data->{'ATTACK_SCALE'};
  my $note		= $data->{'NOTE'};
  my $priority		= $data->{'PRIORITY'};
  my $timeout		= $data->{'TIMEOUT'};

  my %client = authorizeClient($alt_names, $ip, $service, $client_type, $function_name);
  if(defined %client) {

    # if validator is configured, check validity of event attributes - TYPE
    if (!exists $VALID_STRINGS{'type'} or grep $type eq $_, @{$VALID_STRINGS{'type'}}) {
      $sth=$DBH->prepare("INSERT INTO events VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");
      if (!defined $sth) { die("Cannot do insert statement in $function_name: $DBI::errstr\n") }
      $sth->execute(undef, $client{'dns'}, $service, $detected, $received, $type, $source_type, $source, $target_proto, $target_port, $attack_scale, $note, $priority, $timeout, $valid);
      return 1;
    } else {
      write2log ("err", "Unknown event type - client from: '$ip'; CN(AN): $alt_names; used type: '$type'");
      die("Unknown event type - client sent event with unknown type '$type'!");
    }
  }
} # END of saveNewEvent


#-----------------------------------------------------------------------------
# getNewEvents - get new events from the DB greater than received ID
#-----------------------------------------------------------------------------
sub getNewEvents
{
  my ($class, $data) = @_;
  my ($sth, @events, $event, @ids, $cidr_list);
  my ($id, $hostname, $service, $detected, $type, $source_type, $source, $target_proto, $target_port, $attack_scale, $note, $priority, $timeout);

  # client network information
  my $cn	= $ENV{'SSL_CLIENT_S_DN_CN'};
  my $alt_names	= getAltNames(undef);
  my $ip	= $ENV{'REMOTE_ADDR'};

  my $client_type	= 'r';	# incoming client MUST be sender
  my $function_name	= 'getNewEvents';

  # parse SOAP data object
  my $requested_type		= $data->{'REQUESTED_TYPE'} || '_any_';
  my $last_id			= $data->{'LAST_ID'};
  my $max_rcv_events_limit 	= $data->{'MAX_RCV_EVENTS_LIMIT'}; # client events limit

  # comparison of client and server limit - which can be used
  (defined $max_rcv_events_limit && $max_rcv_events_limit < $MAX_EVENTS_LIMIT) ? my $used_limit = $max_rcv_events_limit : my $used_limit = $MAX_EVENTS_LIMIT;

  my %client = authorizeClient($alt_names, $ip, $requested_type, $client_type, $function_name);
  if(defined %client) {
    if ($client{'receive_own'} eq 't') {
      if ($requested_type eq '_any_') {
        $sth = $DBH->prepare("SELECT * FROM events WHERE type != 'test' AND id > ? AND valid = 't' ORDER BY id ASC LIMIT ?;");
        if (!defined $sth) {die("Cannot prepare ROE-ANY statement in $function_name: $DBI::errstr\n")}
        my $rows = $sth->execute($last_id, $used_limit);
      } else {
        $sth = $DBH->prepare("SELECT * FROM events WHERE type != 'test' AND id > ? AND type = ? AND valid = 't' ORDER BY id ASC LIMIT ?;");
        if (!defined $sth) {die("Cannot prepare ROE statement in $function_name: $DBI::errstr\n")}
	my $rows = $sth->execute($last_id, $requested_type, $used_limit);
      }
    } else {
      if ($requested_type eq '_any_') {
        $sth = $DBH->prepare("SELECT * FROM events WHERE type != 'test' AND id > ? AND valid = 't' AND hostname NOT LIKE ? ORDER BY id ASC LIMIT ?;");
        if (!defined $sth) {die("Cannot prepare ANY statement in $function_name: $DBI::errstr\n")}
        my ($domain) = $cn =~ /([^\.]+\.[^\.]+)$/;
        $domain = '\%' . $domain;
        my $rows = $sth->execute($last_id, $domain, $used_limit);
      } else {
        $sth = $DBH->prepare("SELECT * FROM events WHERE type != 'test' AND id > ? AND type = ? AND valid = 't' AND hostname NOT LIKE ? ORDER BY id ASC LIMIT ?;");
        if (!defined $sth) {die("Cannot prepare statement in $function_name: $DBI::errstr\n")}
        my ($domain) = $cn =~ /([^\.]+\.[^\.]+)$/;
        $domain = '\%' . $domain;
        my $rows = $sth->execute($last_id, $requested_type, $domain, $used_limit);
      }
    }

    # parse items of events stored in DB
    while (my @result = $sth->fetchrow()) {
      $id 		= $result[0];
      $hostname		= $result[1];
      $service		= $result[2];
      $detected 	= $result[3];
      $type 		= $result[5];
      $source_type 	= $result[6];
      $source	 	= $result[7];
      $target_proto	= $result[8];
      $target_port 	= $result[9];
      $attack_scale	= $result[10];
      $note 		= $result[11];
      $priority 	= $result[12];
      $timeout	 	= $result[13];

      # create SOAP data object
      $event = SOAP::Data->name(event => \SOAP::Data->value(
        SOAP::Data->name(ID		=> $id),
        SOAP::Data->name(HOSTNAME	=> $hostname),
        SOAP::Data->name(SERVICE	=> $service),
        SOAP::Data->name(DETECTED	=> $detected),
        SOAP::Data->name(TYPE		=> $type),
        SOAP::Data->name(SOURCE_TYPE	=> $source_type),
        SOAP::Data->name(SOURCE		=> $source),
        SOAP::Data->name(TARGET_PROTO	=> $target_proto),
        SOAP::Data->name(TARGET_PORT	=> $target_port),
        SOAP::Data->name(ATTACK_SCALE	=> $attack_scale),
        SOAP::Data->name(NOTE		=> $note),
        SOAP::Data->name(PRIORITY	=> $priority),
        SOAP::Data->name(TIMEOUT	=> $timeout)
      ));
      push(@events, $event);
      push(@ids, $id);
    }

    # log sent ID of events
    if (scalar @events != 0) {
      if (scalar @ids == 1) {
        write2log("info", "Sent 1 event [#$ids[0]] to '$ip' (CN(AN): $alt_names) with client limit '$max_rcv_events_limit' events");
      } else {
        write2log("info", "Sent " . scalar @ids . " events [#$ids[0] - #$ids[-1]] to '$ip' (CN(AN): $alt_names) with client limit '$max_rcv_events_limit' events");
      }
    }
    return @events;
  }
} # END of getNewEvents


#-----------------------------------------------------------------------------
# getLastId - get lastest saved event ID
#-----------------------------------------------------------------------------
sub getLastId
{
  my ($class, $arg) = @_;
  my $function_name = 'getLastId';

  my $sth = $DBH->prepare("SELECT max(id) FROM events;");
  if ( !defined $sth ) { die("Cannot prepare statement in '$function_name': $DBI::errstr\n") }
  $sth->execute;
  my $result = $sth->fetchrow();

  return $result;
} # END of getLastID


#-----------------------------------------------------------------------------
# registerSender - register new sender
#-----------------------------------------------------------------------------
sub registerSender
{
  my ($class, $data) = @_;
  my $sth;

  # client network information
  my $cn = $ENV{'SSL_CLIENT_S_DN_CN'};
  my $ip = $ENV{'REMOTE_ADDR'};
  my $local_ip = $ENV{'SERVER_ADDR'};

  my $function_name = 'registerSender';

  if ($local_ip ne $ip) {
    write2log ("err", "Unauthorized access to '$function_name' from: '$ip' ('$cn') - access allowed only from localhost");
    die("Access denied - access allowed only from localhost!");
  } else {
    # defined variables by method
    my $client_type 		= "s";
    my $registered 		= DateTime->now;
    my $type			= undef;
    my $receive_own_events	= undef;

    # parse SOAP data oject
    my $hostname		= $data->{'HOSTNAME'};
    my $requestor		= $data->{'REQUESTOR'};
    my $service			= $data->{'SERVICE'};
    my $description_tags	= $data->{'DESCRIPTION_TAGS'};
    my $ip_net_client		= $data->{'IP_NET_CLIENT'};

    # check if sender has been already registered
    $sth = $DBH->prepare("SELECT registered FROM clients WHERE hostname = ? AND requestor = ? AND service = ? AND client_type = ? AND type = ? AND receive_own_events = ? AND description_tags = ? AND ip_net_client = ? LIMIT 1;");
    if (!defined $sth) {die("Cannot prepare check statement in '$function_name': $DBI::errstr\n")}
    $sth->execute($hostname, $requestor, $service, $client_type, $type, $receive_own_events, $description_tags, $ip_net_client);
    my $result = $sth->fetchrow();

    # register new sender
    if (defined $result) {
      write2log ("err", "Attempt to re-register the sender");
      die("Error - sender has already been registered at '$result'");
    } else {
      $sth = $DBH->prepare("INSERT INTO clients VALUES (?,?,?,?,?,?,?,?,?,?);");
      if (!defined $sth) {die("Cannot do statement in '$function_name': $DBI::errstr\n")}
      $sth->execute(undef, $hostname, $registered, $requestor, $service, $client_type, $type, $receive_own_events, $description_tags, $ip_net_client);
      write2log("info", "New sender '$hostname' (service: '$service', cidr: '$ip_net_client') was registered");
      return 1;
    }
  }
} # END of registerSender


##-----------------------------------------------------------------------------
## registerReceiver - register new receiver
##-----------------------------------------------------------------------------
sub registerReceiver
{
  my ($class, $data) = @_;
  my $sth;

  # client network information 
  my $cn = $ENV{'SSL_CLIENT_S_DN_CN'};
  my $ip = $ENV{'REMOTE_ADDR'};
  my $local_ip = $ENV{'SERVER_ADDR'};

  my $function_name = 'registerReceiver';

  if ($local_ip ne $ip) {
    write2log ("err", "Unauthorized access to '$function_name' from: '$ip' ('$cn') - access allowed only from localhost");
    die("Access denied - access allowed only from localhost!");
  } else {
    # variables defined by method
    my $client_type		= "r";
    my $registered 		= DateTime->now;
    my $service			= undef;
    my $description_tags	= undef;

    # parse SOAP data oject
    my $hostname		= $data->{'HOSTNAME'};
    my $requestor		= $data->{'REQUESTOR'};
    my $type			= $data->{'TYPE'};
    my $receive_own_events	= $data->{'RECEIVE_OWN_EVENTS'};
    my $ip_net_client		= $data->{'IP_NET_CLIENT'};

    # check if receiver has been already registered
    $sth = $DBH->prepare("SELECT registered FROM clients WHERE hostname = ? AND requestor = ? AND service = ? AND client_type = ? AND type = ? AND receive_own_events = ? AND description_tags = ? AND ip_net_client = ? LIMIT 1;");
    if (!defined $sth) {die("Cannot prepare check statement in '$function_name': $DBI::errstr\n")}
    $sth->execute($hostname, $requestor, $service, $client_type, $type, $receive_own_events, $description_tags, $ip_net_client);
    my $result = $sth->fetchrow();

    # register new receiver
    if (defined $result) {
      write2log ("err", "Attempt to re-register the receiver");
      die("Error - receiver has already been registered at '$result'");
    } else {
      $sth = $DBH->prepare("INSERT INTO clients VALUES (?,?,?,?,?,?,?,?,?,?);");
      if (!defined($sth)) {die("Cannot do statement in '$function_name': $DBI::errstr\n")}
      $sth->execute(undef, $hostname, $registered, $requestor, $service, $client_type, $type, $receive_own_events, $description_tags, $ip_net_client);
      write2log("info", "New receiver '$hostname' (type: '$type', cidr: '$ip_net_client', receive_own_events: '$receive_own_events') was registered");
      return 1;
    }
  }
} # END of registerReceiver


#-----------------------------------------------------------------------------
# unregisterClient - unregister client
#-----------------------------------------------------------------------------
sub unregisterClient
{
  my ($class, $data) = @_;
  my $sth;

  # client network information
  my $cn = $ENV{'SSL_CLIENT_S_DN_CN'};
  my $ip = $ENV{'REMOTE_ADDR'};
  my $local_ip = $ENV{'SERVER_ADDR'};

  my $function_name = 'unregisterClient';

  if ($local_ip ne $ip) {
    write2log ("err", "Unauthorized access to '$function_name' from: '$ip' ('$cn') - access allowed only from localhost");
    die("Access denied - access allowed only from localhost!");
  } else {
    # parse SOAP data oject
    my $client_id	= $data->{'CLIENT_ID'};

    # check if receiver has been already registered
    $sth = $DBH->prepare("SELECT client_id, hostname, service, client_type FROM clients WHERE client_id = ? LIMIT 1;");
    if (!defined $sth) {die("Cannot prepare check statement in '$function_name': $DBI::errstr\n")}
    $sth->execute($client_id);
    my ($id, $hostname, $service, $client_type) = $sth->fetchrow();

    # delete registered client
    if (!defined $id) {
      write2log ("err", "Attempt to delete unregister client");
      die("Error - client (#$client_id) is not registered");
    } else {
      if ($client_type eq 's') {
        $sth = $DBH->prepare("DELETE FROM clients WHERE client_id = ?;");
        if (!defined $sth) {die("Cannot do delete statement of sender in '$function_name': $DBI::errstr\n")}
        $sth->execute($client_id);

        $sth = $DBH->prepare("UPDATE events SET valid = 'f' where hostname = ? AND service = ?;");
        if (!defined $sth) {die("Cannot do unvalidation statement in '$function_name': $DBI::errstr\n")}
	$sth->execute($hostname, $service);

        write2log("info", "Sender '$hostname' (client_id: '$client_id', service: '$service') was deleted and its data were invalidated" );
        return 1;
      } else {
        $sth = $DBH->prepare("DELETE FROM clients WHERE client_id = ?;");
        if (!defined $sth) {die("Cannot do delete statement of receiver in '$function_name': $DBI::errstr\n")}
	$sth->execute($client_id);

        write2log("info", "Receiver '$hostname' (client_id: '$client_id') was deleted" );
        return 1;
      }
    }
  }
} # END of unregisterClient


#-----------------------------------------------------------------------------
# getClients -  get list of clients which were registered at warden server
#-----------------------------------------------------------------------------
sub getClients
{
  my ($class, $arg) = @_;

  # client network information
  my $cn = $ENV{'SSL_CLIENT_S_DN_CN'};
  my $ip = $ENV{'REMOTE_ADDR'};
  my $local_ip = $ENV{'SERVER_ADDR'};

  my $function_name = 'getClients';

  if ($local_ip ne $ip) {
    write2log ("err", "Unauthorized access to '$function_name' from: '$ip' ('$cn') - access allowed only from localhost");
    die("Access denied - access allowed only from localhost!");
  } else {
    my (@clients, $client);
    my ($client_id, $hostname, $registered, $requestor, $service, $client_type, $type, $receive_own_events, $description_tags, $ip_net_client);
    my $sth = $DBH->prepare("SELECT * FROM clients ORDER BY client_id ASC;");
    if (!defined $sth) { die("Cannot prepare statement in '$function_name': $DBI::errstr\n") }
    $sth->execute;

    while ( my @result = $sth->fetchrow() ) {
      $client_id		= $result[0];
      $hostname			= $result[1];
      $registered		= $result[2];
      $requestor		= $result[3];
      $service			= $result[4];
      $client_type 		= $result[5];
      $type 			= $result[6];
      $receive_own_events	= $result[7];
      $description_tags		= $result[8];
      $ip_net_client		= $result[9];

      $client = SOAP::Data->name(client => \SOAP::Data->value(
      SOAP::Data->name(CLIENT_ID		=> $client_id),
      SOAP::Data->name(HOSTNAME			=> $hostname),
      SOAP::Data->name(REGISTERED		=> $registered),
      SOAP::Data->name(REQUESTOR		=> $requestor),
      SOAP::Data->name(SERVICE			=> $service),
      SOAP::Data->name(CLIENT_TYPE		=> $client_type),
      SOAP::Data->name(TYPE			=> $type),
      SOAP::Data->name(RECEIVE_OWN_EVENTS	=> $receive_own_events),
      SOAP::Data->name(DESCRIPTION_TAGS		=> $description_tags),
      SOAP::Data->name(IP_NET_CLIENT		=> $ip_net_client),
      ));
      push(@clients, $client);
    }
    my $sum = scalar @clients;
    write2log("info", "Sending information about '$sum' registered clients");
    return @clients;
  }
} # END of getClients


#-----------------------------------------------------------------------------
# getStatus - get list of status items of warden server
#-----------------------------------------------------------------------------
sub getStatus
{
  my ($class, $arg) = @_;

  # client network information
  my $cn = $ENV{'SSL_CLIENT_S_DN_CN'};
  my $ip = $ENV{'REMOTE_ADDR'};
  my $local_ip = $ENV{'SERVER_ADDR'};

  my $function_name = 'getStatus';

  if ($local_ip ne $ip) {
    write2log ("err", "Unauthorized access to '$function_name' from: '$ip' ('$cn') - access allowed only from localhost");
    die("Access denied - access allowed only from localhost!");
  } else {
    my ($sth, @status);

    # Warden server hostname
    my $hostname = $ENV{'SERVER_NAME'};

    # IP address of Warden server
    my $ip_address = $ENV{'REMOTE_ADDR'};

    # used port
    my $port = $ENV{'SERVER_PORT'};

    # size of database events
    $sth = $DBH->prepare("SELECT data_length + index_length FROM information_schema.TABLES WHERE table_schema = ? AND TABLE_NAME = ?");
    $sth->execute('warden', 'events');
    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';");
    if (!defined $sth) { die("Cannot prepare statement in '$function_name': $DBI::errstr\n") }
    $sth->execute;
    my $events_sum = $sth->fetchrow();
    if (!defined $events_sum) { $events_sum = "none" }

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

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

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

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

    my $server_status = SOAP::Data->name(server_status => \SOAP::Data->value(
      SOAP::Data->name(VERSION			=> $VERSION),
      SOAP::Data->name(HOSTNAME			=> $hostname),
      SOAP::Data->name(IP_ADDRESS		=> $ip_address),
      SOAP::Data->name(PORT			=> $port),
      SOAP::Data->name(FACILITY			=> $FACILITY),
      SOAP::Data->name(DB_NAME			=> $DB_NAME),
      SOAP::Data->name(DB_USER			=> $DB_USER),
      SOAP::Data->name(DB_HOST			=> $DB_HOST),
      SOAP::Data->name(DB_SIZE			=> $db_size),
      SOAP::Data->name(EVENTS_SUM		=> $events_sum),
      SOAP::Data->name(EVENTS_LAST_ID		=> $events_last_id),
      SOAP::Data->name(EVENTS_FIRST_TIMESTAMP	=> $events_first_timestamp),
      SOAP::Data->name(EVENTS_LAST_TIMESTAMP	=> $events_last_timestamp),
      SOAP::Data->name(CLIENTS_SUM		=> $clients_sum)
    ));
    push(@status, $server_status);

    # statistics of senders
    if ($clients_sum != 0) {
      $sth = $DBH->prepare("SELECT client_id, hostname, service FROM clients WHERE client_type = 's' ORDER BY client_id ASC;");
      if (!defined $sth) {die("Cannot prepare statement in '$function_name': $DBI::errstr\n")}
      $sth->execute;
      my ($client_id, $hostname, $service);
      my $client_status;
      while(($client_id, $hostname, $service) = $sth->fetchrow()) {
        my $sth2;
        # sum of stored events
        $sth2 = $DBH->prepare("SELECT count(*) FROM events WHERE hostname = ? AND service = ?;");
        if (!defined $sth2) {die("Cannot prepare statement in '$function_name': $DBI::errstr\n")}
        $sth2->execute($hostname, $service);
        my $count = $sth2->fetchrow();
        if (!defined $count) {$count = "none"}
        # timestamp of last stored event 
        $sth2 = $DBH->prepare("SELECT max(received) FROM events WHERE hostname = ? AND service = ?;");
        if (!defined $sth2) {die("Cannot prepare statement in '$function_name': $DBI::errstr\n")}
        $sth2->execute($hostname, $service);
        my $timestamp = $sth2->fetchrow();
        if (!defined $timestamp) {$timestamp = "none"}
        # create SOAP data object
        $client_status = SOAP::Data->name(client_status => \SOAP::Data->value(
          SOAP::Data->name(CLIENT_ID	=> $client_id),
          SOAP::Data->name(HOSTNAME	=> $hostname),
          SOAP::Data->name(SERVICE	=> $service),
          SOAP::Data->name(COUNT	=> $count),
          SOAP::Data->name(TIMESTAMP	=> $timestamp),
        ));
	push(@status, $client_status);
      }
    }
    write2log("info", "Sent of warden server status info");
    return @status;
  }
} # END of getStatus

1;