I want to assign a specific template to all hosts in a host group. I understand that a bulk update may be in a future release, but could someone advise if the following code will do it in the meantime?
use nagiosql;
update nagiosql.tbl_lnkHostToHosttemplate
set idSlave = 39
where idMaster IN
(select a.id
from tbl_host a,
tbl_lnkHostToHostgroup b,
tbl_hostgroup c
where a.id = b.idmaster
and b.idslave = c.id
and c.hostgroup_name = 'I - windows-servers')
I'm just not sure if there is another table out there that I'm not taking into account and will need to be updated. (there are no results in tbl_lnkHosttemplateToHost).
Firstly, it's always dangerous to directly manipulate the database in my opinion. That said, I've done it myself.
As long as you know all of the relationships between the affected objects and tables, there's no reason why this won't work, although you need to make sure the changes are reflected down to the external configuration files.
I can't personally validate your SQL code and I'll be a bit surprised is Nagios will either as they don't want folks messing up the back end database.
For what it's worth, the way I've "traced" such SQL calls is to get a very quiet Nagios XI server, preferably with as few hosts and services on it as possible. Then, in MySQL, turn on logging (SET GLOBAL general_log = 'on';). Then in the GUI, perform the action you want do in bulk. Go to the MySQL log and find the transactions that occurred through that change.
This does not guarantee it will work as your hosts may have other dependencies that would be affected by this.
Finally, I don't know if this is something you could do through the import function where you would simply modify the host configuration files and then import them. Usually this mechanism, if available for this function, is best.
mp4783 wrote:I can't personally validate your SQL code and I'll be a bit surprised is Nagios will either as they don't want folks messing up the back end database.
This says it all... almost. The only other thing to say if you do go messing about is backup backup backup!
Luckily I'm using RDS, so backing up and restoring is easy. There is one thing I caught beforehand, the column idSort in the tbl_lnkHostToHosttemplate table.
Mass update host template
