OK this trigger was updated to include a check to see if the location table is there, if not then it won’t run the code to update the location (I’m unsure if there is an error in a trigger if the remainder of the code is executed). AND this new code will remove any existing settings for the host before adding the settings that match the template host.
DELIMITER $$
CREATE TRIGGER `new_groupmember_added`
AFTER INSERT ON `groupMembers`
FOR EACH ROW
BEGIN
SET @myHostID = `NEW`.`gmHostID`;
SET @myGroupID = `NEW`.`gmGroupID`;
SET @myTemplateID = (SELECT `hostID` FROM `groups` INNER JOIN `hosts` ON (`groupName` = `hostName`) WHERE `groupID`=@myGroupID);
IF (@myTemplateID IS NOT NULL) AND (@myHostID <> @myTemplateID) THEN
UPDATE `hosts` `d`, (SELECT `hostImage`, `hostBuilding`, `hostUseAD`, `hostADDomain`, `hostADOU`,
`hostADUser`, `hostADPass`, `hostADPassLegacy`, `hostProductKey`, `hostPrinterLevel`, `hostKernelArgs`,
`hostExitBios`, `hostExitEfi`, `hostEnforce` FROM `hosts` WHERE `hostID`=@myTemplateID) `s`
SET `d`.`hostImage`=`s`.`hostImage`, `d`.`hostBuilding`=`s`.`hostBuilding`, `d`.`hostUseAD`=`s`.`hostUseAD`, `d`.`hostADDomain`=`s`.`hostADDomain`,
`d`.`hostADOU`=`s`.`hostADOU`, `d`.`hostADUser`=`s`.`hostADUser`, `d`.`hostADPass`=`s`.`hostADPass`, `d`.`hostADPassLegacy`=`s`.`hostADPassLegacy`,
`d`.`hostProductKey`=`s`.`hostProductKey`, `d`.`hostPrinterLevel`=`s`.`hostPrinterLevel`, `d`.`hostKernelArgs`=`s`.`hostKernelArgs`,
`d`.`hostExitBios`=`s`.`hostExitBios`, `d`.`hostExitEfi`=`s`.`hostExitEfi`, `d`.`hostEnforce`=`s`.`hostEnforce`
WHERE `d`.`hostID`=@myHostID;
SET @myDBTest = (SELECT count(`table_name`) FROM information_schema.tables WHERE `table_schema` = 'fog' AND `table_name` = 'locationAssoc' LIMIT 1);
IF (@myDBTest > 0) THEN
DELETE FROM `locationAssoc` WHERE `laHostID`=@myHostID;
INSERT INTO `locationAssoc` (`laHostID`,`laLocationID`)
SELECT @myHostID as `laHostID`,`laLocationID`
FROM `locationAssoc` WHERE `laHostID`=@myTemplateID;
END IF;
DELETE FROM `printerAssoc` WHERE `paHostID`=@myHostID;
INSERT INTO `printerAssoc` (`paHostID`,`paPrinterID`,`paIsDefault`,`paAnon1`,`paAnon2`,`paAnon3`,`paAnon4`)
SELECT @myHostID as `paHostID`,`paPrinterID`,`paIsDefault`,`paAnon1`,`paAnon2`,`paAnon3`,`paAnon4`
FROM `printerAssoc` WHERE `paHostID`=@myTemplateID;
DELETE FROM `snapinAssoc` WHERE `saHostID`=@myHostID;
INSERT INTO `snapinAssoc` (`saHostID`,`saSnapinID`)
SELECT @myHostID as `saHostID`,`saSnapinID`
FROM `snapinAssoc` WHERE `saHostID`=@myTemplateID;
DELETE FROM `moduleStatusByHost` WHERE `msHostID`=@myHostID;
INSERT INTO `moduleStatusByHost` (`msHostID`,`msModuleID`,`msState`)
SELECT @myHostID as `msHostID`,`msModuleID`,`msState`
FROM `moduleStatusByHost` WHERE `msHostID`=@myTemplateID;
END IF;
END;
$$
DELIMITER ;
There is still one todo left. If the template host is a member of several group then those groups need to be applied to the destination group.
There is one caveat with this template or persistent group. A host can be members in several persistent groups, but only the last persistent group wins for updating the settings. You CAN NOT merge the settings from two different persistent groups onto one target host.
Now there is something else you have to be aware of, if you remove a host from a persistent group the settings will stick to the host and not be removed just because the host is no longer a member of the persistent group.