Warden.pm

#!/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.0";


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

my $conf_file = "/opt/warden-server-2/etc/warden-server.conf";
our $FACILITY	= undef;
our $DB_NAME	= undef;
our $DB_USER	= undef;
our $DB_PASS	= undef;
our $DB_HOST	= undef;

# read config file
if (!open( TMP, $conf_file)) {
  die errMsg("Can't read config file '$conf_file': $!\n");
}
close TMP;

# load set variables by user
if (!do $conf_file) {
  die errMsg("Errors in config file '$conf_file': $@");
}


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



################################################################################
# 				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 $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'};


  # obtain cidr based on rigth common name and alternate names, service and client_type
  $sth = $DBH->prepare("SELECT hostname, ip_net_client FROM clients WHERE hostname IN ($alt_names) AND service = ? AND client_type = ? LIMIT 1;");
  if (!defined $sth) {die("Cannot prepare authorization statement in saveNewEvent: $DBI::errstr\n")}
  $sth->execute($service, $client_type);
  my ($an, $cidr) = $sth->fetchrow();

  # check if client is registered
  if (!defined $cidr) {
    write2log ("err", "Unauthorized access to saveNewEvent from: $ip (CN: $cn; AN: $an) - client is not registered");
    die("Access denied - client is not registered at warden server!");
  } else {
    $cidr_list = Net::CIDR::Lite
      -> new
      -> add($cidr);
  }

  # check if client has IP from registered CIDR
  if (!$cidr_list->bin_find($ip)) {
    write2log ("err", "Unauthorized access to saveNewEvent from: $ip (CN: $cn; AN: $an) - access from bad subnet: $cidr");
    die("Access denied - access from unauthorized subnet!");
  } else {
    # insert new events into DB
    $sth=$DBH->prepare("INSERT INTO events VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");
    if (!defined $sth) {die("Cannot do insert statement in saveNewEvent: $DBI::errstr\n")}
    $sth->execute(undef, $cn, $service, $detected, $received, $type, $source_type, $source, $target_proto, $target_port, $attack_scale, $note, $priority, $timeout, $valid);
  }
} # 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

  # parse SOAP data object
  my $requested_type	= $data->{'REQUESTED_TYPE'};
  my $last_id		= $data->{'LAST_ID'};


  # obtain cidr based on rigth common name, service and client_type
  $sth = $DBH->prepare("SELECT hostname, receive_own_events, ip_net_client FROM clients WHERE hostname IN ($alt_names) AND type = ? AND client_type = ? LIMIT 1;");
  if (!defined $sth) {die("Cannot prepare authorization statement in getNewEvents: $DBI::errstr\n")}
  $sth->execute($requested_type, $client_type);
  my ($an, $receive_own_events, $cidr) = $sth->fetchrow();

  # check if client is registered
  if (!defined $cidr) {
    write2log ("err", "Unauthorized access to getNewEvents from: $ip (CN: $cn; AN: $an) - client is not registered");
    die("Access denied - client is not registered at warden server!");
  } else {
    $cidr_list = Net::CIDR::Lite
      -> new
      -> add($cidr);
  }

  # check if client has IP from registered CIDR
  if (!$cidr_list->bin_find($ip)) {
    write2log ("err", "Unauthorized access to getNewEvents from: $ip (CN: $cn; AN: $an) - access from bad subnet: $cidr");
    die("Access denied - access from unathorized subnet!");
  } else {
    # check if client want your own events or not
    if ($receive_own_events eq 't') {
      $sth = $DBH->prepare("SELECT * FROM events WHERE type != 'test' AND id > ? AND type = ? AND valid = 't' ORDER BY id ASC;");
      if (!defined $sth) {die("Cannot prepare ROE statement in getNewEvents: $DBI::errstr\n")}
      $sth->execute($last_id, $requested_type);
    } 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;");
      if (!defined $sth) {die("Cannot prepare statement in getNewEvents: $DBI::errstr\n")}
      my ($domain) = $cn =~ /([^\.]+\.[^\.]+)$/;
      $domain = '\%' . $domain;
      $sth->execute($last_id, $requested_type, $domain);
    }

    # 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 events [#$ids[0]] to $ip (CN: $cn; AN: $an)");
      } else {
        write2log("info", "Sent " . scalar @ids . " events [#$ids[0] - #$ids[-1]] to $ip (CN: $cn; AN: $an)");
      }
    }
    return @events;
  }
} # END of getNewEvents


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

  my $sth = $DBH->prepare("SELECT max(id) FROM events;");
  if ( !defined $sth ) { die("Cannot prepare statement in getLastId: $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'};

  if ($local_ip ne $ip) {
    write2log ("err", "Unauthorized access to registerSender 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 registerSender: $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 registerSender: $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'};

  if ($local_ip ne $ip) {
    write2log ("err", "Unauthorized access to registerReceiver 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 registerReceiver: $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 registerReceiver: $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'};

  if ($local_ip ne $ip) {
    write2log ("err", "Unauthorized access to unregisterClients 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 unregisterClient: $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 unregisterClient: $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 unregisterClient: $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 unregisterClient: $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'};

  if ($local_ip ne $ip) {
    write2log ("err", "Unauthorized access to getClients 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 getClients: $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'};

  if ($local_ip ne $ip) {
    write2log ("err", "Unauthorized access to getStatus 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 getStatus: $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 getStatus: $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 getStatus: $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 getStatus: $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 getStatus: $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 getStatus: $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 getStatus: $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 getStatus: $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;