labtech Database

Parameters

Name	Type	Mode

Definition

BEGIN
	/* add network devices into groups from last executed searches */
	INSERT IGNORE
	INTO SubGroupsNetworkDevices(GroupID, DeviceID) (
		SELECT MasterGroups.GroupID,
			Searches.NetworkDeviceID
		FROM MasterGroups
		JOIN Searches ON Searches.SearchID = MasterGroups.NetworkJoin
		WHERE NetworkJoin > 0);
	/* remove network devices from groups where the search options were excusionary */
	DELETE SubGroupsNetworkDevices
	FROM SubGroupsNetworkDevices
	JOIN MasterGroups USING (GroupID)
	WHERE (MasterGroups.NetworkJoinOptions = 1
		AND DeviceID NOT IN (
			SELECT NetworkDeviceID
			FROM Searches
			WHERE SearchID = MasterGroups.NetworkJoin))
		OR DeviceID NOT IN (SELECT DeviceID FROM `networkdevices`);
	/* create temp table of (groupid, deviceid) where group is not a master and device belongs to another group that is a master */
	CREATE TEMPORARY TABLE IF NOT EXISTS GNDevice
	SELECT *
	FROM SubGroupsNetworkDevices
	WHERE GroupID IN (
			SELECT GroupID
			FROM MasterGroups
			WHERE `master` = 0)
		AND DeviceID IN (
			SELECT DeviceID
			FROM SubGroupsNetworkDevices
			JOIN MasterGroups USING (groupID)
			WHERE MasterGroups.NetworkJoin > 0
				AND MasterGroups.Master = 1);
	/* use above temp table to remove devices from non-master groups if they're already in other master groups */
	DELETE SubGroupsNetworkDevices
	FROM SubGroupsNetworkDevices
	JOIN GNdevice USING (DeviceID, GroupID);
	DROP TEMPORARY TABLE GNDevice;	/* goodbye temp table */
END

sp_AutoJoinNetworkDevices

Parameters

Definition