1.1beta6: cli php script to sync all hosts with respective templates

[mconigliaro]

this script will scan the hosts_templates table and sync all hosts with their respective templates. if you only want to see what changes will be made without modifying the database, use the TESTRUN constant (highly recommended).


<?php

chdir('zabbix-1.1beta6/frontends/php'); // root of zabbix installation
include 'include/config.inc.php';

define('TESTRUN', 1); // if set to 1, database wont be modified
$USER_DETAILS = array(
'userid' => '3' // id of user with 'administrative' permissions
);


// *** delete non-template triggers ***
printf("deleting all non-template triggers\n");
if(!TESTRUN) {
DBexecute('delete triggers, functions from functions f, triggers t, items i, hosts h where f.triggerid=t.triggerid and f.itemid=i.itemid and i.hostid=h.hostid and h.status<>3');
}

// *** scan hosts_templates table ***
$result = DBselect('select * from hosts_templates');
while($row = DBfetch($result)) {

// *** delete orphaned hosts from hosts_templates table ***
$result2 = DBselect(sprintf("select host from hosts where hostid=%d limit 1", $row['hostid']));
if (!DBnum_rows($result2)) {
printf("host %d does not exist (deleting from hosts_templates table)\n", $row['hostid']);
if(!TESTRUN) {
DBexecute(sprintf("delete from hosts_templates where hostid=%d", $row['hostid']));
}
}

// *** sync all hosts with templates ***
else {
printf("syncing host %d with template %d\n", $row['hostid'], $row['templateid']);
if(!TESTRUN) {
sync_host_with_template($row['hostid'], $row['templateid'], $row['items'], $row['triggers'], $row['graphs']);
}

// *** get hosts's items ***
$host_items = array();
$result2 = DBselect(sprintf("select key_ from items where hostid=%d order by key_", $row['hostid']));
while($row2 = DBfetch($result2)) {
$host_items[] = $row2['key_'];
}

// *** get template's items ***
$template_items = array();
$result2 = DBselect(sprintf("select key_ from items where hostid=%d order by key_", $row['templateid']));
while($row2 = DBfetch($result2)) {
$template_items[] = $row2['key_'];
}

// *** show remaining host/template differences ***
$item_diff = array_diff($template_items, $host_items);
if (!empty($item_diff)) {
foreach ($item_diff as $this_element) {
printf("host %d is missing item %s from template %d\n", $row['hostid'], $this_element, $row['templateid']);
}
}
$item_diff = array_diff($host_items, $template_items);
if (!empty($item_diff)) {
foreach ($item_diff as $this_element) {
printf("host %d has non-template item %s\n", $row['hostid'], $this_element);
}
}
}
}
?>


$ php zabbix_check_template_linkage.php

[rvillaca]

Thanks for that very useful script.

The first delete query must be like the following:

delete t, f from functions f, triggers t, items i, hosts h where f.triggerid=t.triggerid and f.itemid=i.itemid and i.hostid=h.hostid and h.status<>3

the original one duplicates every trigger.

[]'s

Rodrigo

[mconigliaro]

thats weird, i dont get duplicate triggers unless i change the query to what you posted. i wonder if something changed between your version of mysql and mine. for the record, here's what im running.

mysql --version
mysql Ver 12.22 Distrib 4.0.18, for suse-linux (x86_64)

[azilber]

I have a bunch of triggers per host that aren't associated with a trigger, will this delete them? My database is around 5GB so I can't really export test, re-import...

[mconigliaro]

yes, that first query deletes the existing non-template triggers. unfortunately, i had to do this, because the zabbix functions that handle the synchronizing simply copy triggers from the templates to the hosts, which means that you end up with duplicate triggers on the hosts.

[edeus]

Fantastic!

Thanks for the script, my DB was messy or Zabbix wasnt sync'ing properly because triggers were overwritting others.

Script + updated Delete SQL worked a treat.

Note: I am using PHP5/MySQL5 for those that wanted to check before using it. I also cleared out my history and trends tables because if the items are being recreated, then the id's will be shot anyway.

[elkor]

I really like this script but have to go over it before using it on my main DB.

quick question: the above post concerns me (and admittedly I haven't really looked at the code yet), does this script delete and recrate all items/triggers/etc based on the template or does it truly sync up.. that is only add objects that the host was missing that the template has.

[Alexei]

I cannot comment on this script, however I would STRONGLY recommend to wait ZABBIX 1.1beta7 before doing any modifications in ZABBIX database. We already changed concept of ZABBIX templates (CVS) to make it much much better and more understandable.

[mconigliaro]

I really like this script but have to go over it before using it on my main DB.

quick question: the above post concerns me (and admittedly I haven't really looked at the code yet), does this script delete and recrate all items/triggers/etc based on the template or does it truly sync up.. that is only add objects that the host was missing that the template has.

all im doing is calling zabbix's sync_host_with_template() function within a loop. as far as i can tell, this function only adds items that were missing, but it copies all the triggers over too, which usually causes duplicates. thats the reason for the 'delete' query at the beginning.

i should also mention that deleting the existing triggers breaks the alert history, because the items in the alert table are linked back to the trigger table by the id field. this wasnt a huge deal to me, but it may be to some of you.

[elkor]

kk thanks gyra, (yes I'm american, and a sysadmin, I shorten everything for my convinence.. kneel before zod ;) )

I'm scheduled to start ramping back up on zabbix somtime next week.. If I come up with a relevant patch I'll be sure to let you know.

Alexei: I know these things are abiguous.. but I have stayed away from templating because I KNOW you are working on it and too many cooks spoil the broth (so to speak)

but, do you have a projection on b7? (I say projection to free you of any constraints others may place on coding time) Just a wild guess would do for me as I have to update project plans and fixing templating is one of my line items.

[Alexei]

New templating will be ready after 2-4 days. It will take another week to include additional functionality before release of the next beta.

[elkor]

thanks alexei, that's exactly what I was looking for

[SAT QPass]

I cannot comment on this script, however I would STRONGLY recommend to wait ZABBIX 1.1beta7 before doing any modifications in ZABBIX database. We already changed concept of ZABBIX templates (CVS) to make it much much better and more understandable.

Will this require significant effort to upgrade/convert to? What will happen to the existing templates/host sets?

My biggest concern is a smooth transition.

[Alexei]

Will this require significant effort to upgrade/convert to? What will happen to the existing templates/host sets?

My biggest concern is a smooth transition.
It won't be very straight forward. It will require some reconfiguration to switch to the new schema. BUT, the new templating is much easier, so the effort will be paid off very quickly.

[SAT QPass]

It won't be very straight forward. It will require some reconfiguration to switch to the new schema. BUT, the new templating is much easier, so the effort will be paid off very quickly.

Good enough for me! Sweet