Useful PHP template buttons when managing large # of servers.

[mvoss]

It's possible something like this is already posted since I haven't had a chance to read everything yet, but I found adding these sync template buttons extremely useful when working with 450 some odd servers.
(I have an accompanying non-PHP perl script to wipe out all non-template actions,triggers,functions,items,etc - what I would do is create the host to template linkages, modify the templates accordingly, erase the existing setup, and then use these sync buttons to push everything out).

If there is a forum with other solutions less crude pls let me know.

Thx,
Matt

Additions to include/config.inc.php:
=================================================
function sync_all_hosts_with_templates()
{
$sql="select * from hosts where port=10050";
$result=DBselect($sql);
while($row=DBfetch($result))
{
sync_host_with_templates($row["hostid"]);
}
}

function sync_all_hosts_with_template($templateid)
{
$sql="select * from hosts_templates where templateid=$templateid";
$result=DBselect($sql);
while($row=DBfetch($result))
{
sync_host_with_template($row["hostid"],$templateid,$row["items"],$row["triggers"],$row["actions"],
$row["graphs"],$row["screens"]);
}
}
=================================================
Additions to hosts.php:
=================================================

if($_GET["register"]=="sync all hosts that use this template")
{
$result=sync_all_hosts_with_template($_GET["host_templateid"]);
show_messages($result, S_ITEMS_ADDED, S_CANNOT_ADD_ITEMS);
unset($_GET["hostid"]);
}
if($_GET["register"]=="sync all hosts with all linked templates")
{
$result=sync_all_hosts_with_templates();
show_messages($result, S_ITEMS_ADDED, S_CANNOT_ADD_ITEMS);
unset($_GET["hostid"]);
}
if($_GET["register"]=="sync this host with all linked templates")
{
$result=sync_host_with_templates($_GET["hostid"]);
show_messages($result, S_ITEMS_ADDED, S_CANNOT_ADD_ITEMS);
unset($_GET["hostid"]);
}

#........................add these buttons where other change host buttons are

echo "input class=\"button\" type=\"submit\" name=\"register\" value=\"sync all hosts with all linked templates\"";
echo "input class=\"button\" type=\"submit\" name=\"register\" value=\"sync this host with all linked templates\"";
echo "input class=\"button\" type=\"submit\" name=\"register\" value=\"sync all hosts that use this template\"";
=================================================

[mconigliaro]

hopefully the broken host/template linkage stuff will be fixed in the next alpha release, but until then, this is great. thanks!

[charles]

Care to share the perl script (or at least the sql statements used) as well?

thanks
charles

[mconigliaro]

here's a quick thing i threw together using php and adodb. of course, there's probably a more efficient way to do this, but it suits my needs for now:

be aware that this is actually part of something larger, so you cant just copy and paste the code and expect it to work. im posting this here in the hope that most of you can simply look at the comments and sql queries to see what i did.

	// *** connect to the database ***
	$objDB = util_adodb_connect();
	
	// *** get hosts ***
	$recordset = $objDB->execute('select * from hosts where status=0');
	util_check_known_fields($recordset->fields, array('hostid', 'host', 'useip', 'ip', 'port', 'status', 'disable_until', 'network_errors', 'error', 'available'));
	while (!$recordset->EOF) {
		//print($recordset->fields['host'] . "\n");		
		
		// *** get items for this host ***
		$recordset2 = $objDB->execute(sprintf("select * from items where hostid=%u", $recordset->fields['hostid']));
		while (!$recordset2->EOF) {
			//print($recordset2->fields['itemid'] . "\n");
			
			// *** get functions for this item ***
			$recordset3 = $objDB->execute(sprintf("select * from functions where itemid=%u", $recordset2->fields['itemid']));
			while (!$recordset3->EOF) {
				//print($recordset3->fields['functionid'] . "\n");
				
				// *** delete actions and triggers ***
				if (!$test) {
					$objDB->execute(sprintf("delete from actions where triggerid=%u", $recordset3->fields['triggerid']));
					$objDB->execute(sprintf("delete from triggers where triggerid=%u", $recordset3->fields['triggerid']));
				}
				
				$recordset3->moveNext();
			}
			
			// *** delete functions ***
			if (!$test) {
				$objDB->execute(sprintf("delete from functions where itemid=%u", $recordset2->fields['itemid']));
			}
			
			$recordset2->moveNext();
		}
		
		// *** delete items ***
		if (!$test) {
			$objDB->execute(sprintf("delete from items where hostid=%u", $recordset->fields['hostid']));
		}
		
		$recordset->moveNext();
	}

[charles]

Thanks. It was mainly just the sql I wanted to see to save me time trying to make sure I understand the schema properly. I dont mind doing this independently and I dont think its worth adding to zabbix - we just need to bug Alexei to get these template links working right

I dont see you deleting items there - surely you want to?

Here is what I came up with.

mysql 3 - just run on the commandline:

echo "select distinct f.triggerid from hosts h, items i, functions f, actions a where h.hostid=i.hostid and i.itemid=f.itemid and h.status=0;" | mysql zabbix | tail +2 | perl -n -e 'chomp; print "delete from actions where triggerid=$_;\ndelete from triggers where triggerid=$_;\n";' | mysql zabbix
echo "select distinct i.itemid from hosts h, items i, actions a where h.hostid=i.hostid and h.status=0;" | mysql zabbix | tail +2 | perl -n -e 'chomp; print "delete from items where itemid=$_;\ndelete from functions where itemid=$_;\n";' | mysql zabbix

mysql 4 (untested):

delete a from hosts h, items i, functions f, actions a where h.hostid=i.hostid and i.itemid=f.itemid and a.triggerid=f.triggerid and h.status=0;
delete t from hosts h, items i, functions f, triggers t where h.hostid=i.hostid and i.itemid=f.itemid and t.triggerid=f.triggerid and h.status=0;
delete i,f from hosts h, items i, functions f where h.hostid=i.hostid and i.itemid=f.itemid and h.status=0;

[mconigliaro]

i am deleting items in that script. its the last table i delete from.

[charles]

Indeed. not sure how I missed that

charles