#!/usr/bin/perl

#
# getsysinfo.pl - via ssh, get systeminformation about servers.
#       Note: This script depends on DSA/RSA key authentication
#             as we dont want passwords in the actual script so
#             you need to make sure you got this set up correctly
#             before this script will work.

use DBI;
use Net::SSH::Perl;

$username = 'root';
%db = ( database => 'sysinfo',
        hostname => 'localhost',
        username => 'boogo',
        password => 'xxxxx');

# Hostnames of servers to check
@servers = ('nw04','nw05','gw01');

# Commands for the checks to be executed.
%checks  = ( "disk" => 'df -h',
             "mem"  => 'free -mot',
             "pci"  => 'lspci -v',
             "net"  => 'ifconfig'
             );

%results = {};

# Set up mysql connection
$dsn = "DBI:mysql:database=$db{'database'}:host=$db{'hostname'}";
$db  = DBI->connect($dsn, $db{'username'}, $db{'password'}) or
    die ("$!\n");

# lets walk the @servers array and do the tests and save results.
foreach $server (@servers) {
    $ssh = Net::SSH::Perl->new($server, debug=>0,
        identity_files=>["/root/.ssh/id_rsa.pub"], protocol=>'2,1');
    $ssh->login($username);                     # <--- ERROR returned from cron to root's mailbox

    while (($key, $value) = each(%checks)) {
        if (&db_host_exists($server) > 0) {
            # host already exists in database, lets update it.
            ($stdout, $stderr, $exit) = $ssh->cmd($checks{$key});
            $sth = $db->prepare(qq{
                UPDATE servers
                SET $key='<pre>$stdout</pre>', updated=NOW()
            });
            $sth->execute();
            $sth->finish;

        } else {
            # host does not exists in database, lets insert it.
            ($stdout, $stderr, $exit) = $ssh->cmd($checks{$key});
            $sth = $db->prepare(qq{
                INSERT INTO servers (id, hostname, updated, $key)
                VALUES ('','$server',NOW(),'<pre>$stdout</pre>')
            });
            $sth->execute();
            $sth->finish;
        }
    }
}

# db_host_exists($) - subroutine to check if the host allready
#                     exists in the database. Then we use update,
#                     otherwise insert.
# Arguments:          $ - hostname to look for
sub db_host_exists($) {
    $result = 0;

    $sth = $db->prepare(qq{
        SELECT * FROM servers WHERE hostname LIKE '%$server%'
     });
    $sth->execute();

    if ($sth->rows > 0) {
        $result = 1;
    }

    $sth->finish;
    return $result;

}

##</code><code>##

0,30 * * * * /root/bin/getsysinfo.pl