Inventory Script

[daveinvb]

I looked and looked and could not find anything that would easily pull an inventory of what I had in Nagios that included things like Parent and Contact Groups. So I wrote an SQL script to list: Active, IP Address, Host Name, Description (alias), Host Group, Parent Host Name, Parent Description, Template Name, Contact Group.

Here it is if anyone would like to use it.

Code: Select all

Select tbl_host.active as 'Active',
tbl_host.address as 'IP Address',
tbl_host.host_name as 'Host Name',
tbl_host.alias as 'Description',
group_concat(distinct tbl_hostgroup.hostgroup_name) as 'Host Group',
group_concat(distinct p.host_name) as 'Parent Host Name',
group_concat(distinct p.alias) as 'Parent Description',
tbl_hosttemplate.template_name as 'Template Name',
group_concat(distinct tbl_contactgroup.alias) as 'Contact Group'
from tbl_host 
left join tbl_lnkHostToHostgroup on tbl_host.id = tbl_lnkHostToHostgroup.idmaster
left join tbl_hostgroup on tbl_lnkHostToHostgroup.idslave = tbl_hostgroup.id
left join tbl_lnkHostToHosttemplate on tbl_host.id = tbl_lnkHostToHosttemplate.idMaster
left join tbl_hosttemplate on tbl_lnkHostToHosttemplate.idSlave = tbl_hosttemplate.id
left join tbl_lnkHosttemplateToContactgroup on tbl_lnkHostToHosttemplate.idSlave = tbl_lnkHosttemplateToContactgroup.idMaster
left join tbl_contactgroup on tbl_lnkHosttemplateToContactgroup.idSlave = tbl_contactgroup.id
left join tbl_lnkHostToHost on tbl_host.id = tbl_lnkHostToHost.idMaster
left join tbl_host p on p.id = tbl_lnkHostToHosttemplate.idMaster
Group by tbl_host.address

[Box293]

Thanks for sharing.

Did you happen to look at the JSON API?

https://labs.nagios.com/2014/06/19/expl ... -7-part-1/

[daveinvb]

I did, but it wasn't really what I was looking for. I will probably use it if I ever end up writing a dashlet though.

And my script was incorrect. It only accounted for host groups assigned by the host, not when hosts were assigned by host group. Here is the script that shows both.

Code: Select all

Select tbl_host.active as 'Active',
tbl_host.address as 'IP Address',
tbl_host.host_name as 'Host Name',
tbl_host.alias as 'Description',
group_concat(distinct tbl_hostgroup.hostgroup_name) as 'Host Group assigned by Host',
group_concat(distinct h.hostgroup_name) as 'Host Group assigned by Group',
group_concat(distinct p.host_name) as 'Parent Host Name',
group_concat(distinct p.alias) as 'Parent Description',
tbl_hosttemplate.template_name as 'Template Name',
group_concat(distinct tbl_contactgroup.alias) as 'Contact Group'
from tbl_host 
left join tbl_lnkHostToHostgroup on tbl_host.id = tbl_lnkHostToHostgroup.idmaster
left join tbl_hostgroup on tbl_lnkHostToHostgroup.idslave = tbl_hostgroup.id
left join tbl_lnkHostgroupToHost on tbl_host.id = tbl_lnkHostgroupToHost.idSlave
left join tbl_hostgroup h on tbl_lnkHostgroupToHost.idMaster = h.id
left join tbl_lnkHostToHosttemplate on tbl_host.id = tbl_lnkHostToHosttemplate.idMaster
left join tbl_hosttemplate on tbl_lnkHostToHosttemplate.idSlave = tbl_hosttemplate.id
left join tbl_lnkHosttemplateToContactgroup on tbl_lnkHostToHosttemplate.idSlave = tbl_lnkHosttemplateToContactgroup.idMaster
left join tbl_contactgroup on tbl_lnkHosttemplateToContactgroup.idSlave = tbl_contactgroup.id
left join tbl_lnkHostToHost on tbl_host.id = tbl_lnkHostToHost.idMaster
left join tbl_host p on p.id = tbl_lnkHostToHosttemplate.idMaster
Group by tbl_host.address

However, if you'd like all the host groups in one column, you can use this one:

Code: Select all

Select tbl_host.active as 'Active',
tbl_host.address as 'IP Address',
tbl_host.host_name as 'Host Name',
tbl_host.alias as 'Description',
group_concat(distinct coalesce(tbl_hostgroup.hostgroup_name,'/'),',',coalesce(h.hostgroup_name,'/')) as 'Host Group assigned by Host',
group_concat(distinct p.host_name) as 'Parent Host Name',
group_concat(distinct p.alias) as 'Parent Description',
tbl_hosttemplate.template_name as 'Template Name',
group_concat(distinct tbl_contactgroup.alias) as 'Contact Group'
from tbl_host 
left join tbl_lnkHostToHostgroup on tbl_host.id = tbl_lnkHostToHostgroup.idmaster
left join tbl_hostgroup on tbl_lnkHostToHostgroup.idslave = tbl_hostgroup.id
left join tbl_lnkHostgroupToHost on tbl_host.id = tbl_lnkHostgroupToHost.idSlave
left join tbl_hostgroup h on tbl_lnkHostgroupToHost.idMaster = h.id
left join tbl_lnkHostToHosttemplate on tbl_host.id = tbl_lnkHostToHosttemplate.idMaster
left join tbl_hosttemplate on tbl_lnkHostToHosttemplate.idSlave = tbl_hosttemplate.id
left join tbl_lnkHosttemplateToContactgroup on tbl_lnkHostToHosttemplate.idSlave = tbl_lnkHosttemplateToContactgroup.idMaster
left join tbl_contactgroup on tbl_lnkHosttemplateToContactgroup.idSlave = tbl_contactgroup.id
left join tbl_lnkHostToHost on tbl_host.id = tbl_lnkHostToHost.idMaster
left join tbl_host p on p.id = tbl_lnkHostToHosttemplate.idMaster
Group by tbl_host.address

[jdalrymple]

Did you try pynag?

It doesn't always work just right, but I can always fiddle with it to get good info.

[daveinvb]

That's interesting, still looking to see if I can find more info about what I can pull from it. But why would I want to edit the config files directly though? Will that update the DB, and if not then won't everything be overwritten the next time I write out all the config files?

[jdalrymple]

But why would I want to edit the config files directly though?

You wouldn't. Just because the feature exists doesn't mean you have to use it, and correct - it wouldn't work with XI anyway. For inventorying purposes though it may be helpful. It may not - just thought I'd offer it as a suggestion, if it will do what you want, it beats reinventing the wheel.

[daveinvb]

Cool. Well thank you, I'd never heard of it before.

[ssax]

Thanks again for posting what you have! I'm going to lock this up and mark it as resolved.