Page 1 of 1
Inventory Script
Posted: Tue Jul 14, 2015 3:48 pm
by 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
Re: Inventory Script
Posted: Tue Jul 14, 2015 11:03 pm
by Box293
Thanks for sharing.
Did you happen to look at the JSON API?
https://labs.nagios.com/2014/06/19/expl ... -7-part-1/
Re: Inventory Script
Posted: Thu Jul 16, 2015 9:05 am
by 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
Re: Inventory Script
Posted: Thu Jul 16, 2015 10:01 am
by jdalrymple
Did you try
pynag?
It doesn't always work just right, but I can always fiddle with it to get good info.
Re: Inventory Script
Posted: Thu Jul 16, 2015 10:13 am
by 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?
Re: Inventory Script
Posted: Thu Jul 16, 2015 11:05 am
by jdalrymple
daveinvb wrote: 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.
Re: Inventory Script
Posted: Thu Jul 16, 2015 2:58 pm
by daveinvb
Cool. Well thank you, I'd never heard of it before.
Re: Inventory Script
Posted: Thu Jul 16, 2015 4:47 pm
by ssax
Thanks again for posting what you have! I'm going to lock this up and mark it as resolved.