Oct 3, 2011

Getting Nagios Parent-child host relationship

Someone posted an interesting question in Nagios XI (customers only) forum whether it is possible to print the parent-child host relationships in a human readable format. 

NagiosQL stores this data in MySQL database server under the table named nagiosql.tbl_lnkHostToHost.  The table has two fields: idMaster and idSlave but the information is stored as host ids.  The Perl script posted here fetches this information, map the host ids to host names and displays the data in comma separated format which can be opened with any CSV reader, preferably Spreadsheet reader to make the data presentable.

I believe the script posted here applies to everyone who is using NagiosQL and not just Nagios XI users.

You may want to change the following variables before using the script

  • $user
  • $pass

    #!/usr/bin/perl

    use strict;
    use warnings;
    use DBI;

    # this script is released under the same license as perl v5.8.8 itself

    # authentication information and database name
    my ($user, $pass, $db) = qw[nagios_user password nagiosql];
    my $dsn = "dbi:mysql:$db";    # data source name

    my (%host_ids, %host_names);

    # connect to the mysql server
    my $dbh = DBI->connect($dsn, $user, $pass)
            or die "Connection error: $DBI::errstr\n";

    # nagiosql.tbl_lnkHostToHost table holds the
    # parent child relationship information
    my $sql_1 = "select * from tbl_lnkHostToHost";

    # prepare the sql statement and return the handler
    my $sth = $dbh->prepare($sql_1);
    $sth->execute or die "SQL error: $DBI::errstr\n";

    # store the results in a hash
    while (my $record = $sth->fetchrow_hashref) {
            push @{$host_ids{$record->{idSlave}}}, $record->{idMaster};
    }

    # pull host ids and host names
    my $sql_2 = "select id,host_name from tbl_host";

    $sth = $dbh->prepare($sql_2);
    $sth->execute or die "SQL error: $DBI::errstr\n";

    while (my $record = $sth->fetchrow_hashref) {
            $host_names{$record->{id}} = $record->{host_name};
    }

    # map and print the host ids to their corresponding host names
    while (my ($k, $v) = each %host_ids) {
            print "$host_names{$k}";
            for (@{$v}) { print ",", $host_names{$_}, "\n"  }
            print "\n\n";
    }

    The output will be in the following format

    core-switch,lan-switch1
    ,lan-switch2
    ,lan-switch3

    lan-switch2,oracle-host
    ,mysql-host
    ,httpd-host

    lan-switch3,nfs-host
    ,samba-host
    ,ftp-host

    oracle-host,db1
    ,db2
    ,db3
    ,db4

    I prefer to redirect the output to a CSV file and load it in a spreadsheet reader


    $ ./get_host_relationship.pl  > relationship.csv

    This is what I did with the CSV file to make it a bit more presentable in MS Excel



    1 comments:

    Anonymous | March 1, 2012 at 11:32 PM  

    Very nice. Thank you.

    Post a Comment

    Tu comentario será moderado la primera vez que lo hagas al igual que si incluyes enlaces. A partir de ahi no ser necesario si usas los mismos datos y mantienes la cordura. No se publicarán insultos, difamaciones o faltas de respeto hacia los lectores y comentaristas de este blog.