SQL help

Support requests, bug reports, questions etc.

SQL help

Post by dominickilbride » Mon Sep 21, 2009 10:11

Hi All,
Im looking for a bit of help with the SQL database for this exelent program.
I would like to assign all hosts that arnt already in a host group to a new group i have created. The name of this hostgroup is "Nogroup". Ive been looking at the DB design and trying to get my head around the object based design. Is there anyone out there that has been using the direct MYSQL comand line to ajust the config? I feel sure that it should be possible to do this but ive been failing to get a sql statment that works. Does anyone have any idea?

User avatar
NConf developer
NConf developer
Posts: 725
Joined: Fri Mar 06, 2009 17:50
Location: Zurich, Switzerland

Re: SQL help

Post by agargiulo » Mon Sep 21, 2009 15:45


The NConf data model is very abstract. Therefore it's not possible to simply run a query like "SELECT hosts FROM x WHERE hostgroup = NULL".
A bit more insight into the database model can be found here: http://sourceforge.net/apps/mediawiki/n ... onf_design

Still, what you are trying to do is possible. I have attached the queries you need.

First, you will need to find out the hosts that are not in a host group.
The following query will return a list of host ID's which are not in any hostgroup:

Query 1:

Code: Select all

SELECT id_item AS id FROM ConfigItems, ConfigClasses 
  WHERE (SELECT COUNT(*) FROM ItemLinks, ConfigAttrs, ConfigClasses 
           WHERE ItemLinks.fk_id_item=id 
             AND ItemLinks.fk_id_attr=id_attr 
             AND attr_name="members" 
             AND ConfigAttrs.fk_id_class=id_class 
             AND config_class="hostgroup") = 0 
  AND ConfigItems.fk_id_class=id_class 
  AND config_class="host";
Once you have the list from query 1, you can run the following query for each host you want to link to your new hostgroup (you might want to write a small script which runs the second query in a loop):

Query 2:

Code: Select all

INSERT INTO ItemLinks (fk_id_item, fk_item_linked2, fk_id_attr) 
	   (SELECT fk_id_item FROM ConfigValues, ConfigAttrs, ConfigClasses 
              WHERE attr_value LIKE "Nogroup" 
                AND ConfigValues.fk_id_attr = id_attr 
                AND attr_name="hostgroup_name" 
                AND ConfigAttrs.fk_id_class=id_class 
                AND config_class="hostgroup"),
           (SELECT id_attr FROM ConfigAttrs, ConfigClasses 
              WHERE attr_name="members" 
                AND ConfigAttrs.fk_id_class=id_class 
                AND config_class="hostgroup"));
Hope this is a help. We are aware that the NConf datamodel is not very transparent. Instead, it is very flexible and extendable.
We therefore offer a perl-API which should facilitate data manipulation:
http://sourceforge.net/apps/mediawiki/n ... ?title=API