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