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


    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





    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 comment: