FOG 1.3 persistent groups

[Sebastian Roth]

Great work George!

[Wayne Workman]

@george1421 said:

Do NOT make the host template a member of the group you are trying to make persistent. I could see a loop being created by the trigger trying to reference a template that it is currently trying to update.

Any way to add rules so it doesn’t apply if the template is a member of the group?

Or…

Should we force the template to be a member of the group and just disallow settings applying to the template… ???

[george1421]

@Wayne-Workman said:

Any way to add rules so it doesn’t apply if the template is a member of the group?

Or…

Should we force the template to be a member of the group and just disallow settings applying to the template… ???

Thinking about it, we probably could update the check for a null template id to also ensure the @myTemplateID variable does not match the @myHostID. If it does then not execute the update. [edit] wow there was to many double negatives in that statement. I should say its possible if we update the conditional if check[/edit] That should do it.

[george1421]

While this trigger is not supported by the developers, Tom did look at it and updated it so the fields were escaped properly.

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;

   INSERT INTO `locationAssoc` (`laHostID`,`laLocationID`)
   SELECT @myHostID as `laHostID`,`laLocationID`
   FROM `locationAssoc` WHERE `laHostID`=@myTemplateID;

   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;

   INSERT INTO `snapinAssoc` (`saHostID`,`saSnapinID`)
   SELECT @myHostID as `saHostID`,`saSnapinID` 
   FROM `snapinAssoc` WHERE `saHostID`=@myTemplateID;

   INSERT INTO `moduleStatusByHost` (`msHostID`,`msModuleID`,`msState`)
   SELECT @myHostID as `msHostID`,`msModuleID`,`msState`
   FROM `moduleStatusByHost` WHERE `msHostID`=@myTemplateID;

 END IF;

END;
$$

DELIMITER ;

[JJ Fullmer]

@george1421 So if I understand this correctly, If I go login to mysql and select/use the fog database, then copy paste that nifty script. I will have hackish persistent groups and be able to make templates?

[george1421]

@JJ-Fullmer That is the idea/plan. You must name your persistent group the same exact name as the ‘fake’ host you will create. The reason why I said it this way, the group names have much more flexibility than the host names. Your group name must be consistent with host naming conventions.

This script creates a database trigger when you add a host to the persistent group. Just be aware if it is a setting the persistent group manages, it will be overwritten by the value in the persistent group. Meaning if you manually set a certain parameter for a host and then assign that host to a persistent group, the value in the persistent group wins and overwrites the value you manually added to the group. You can manually change the value post group assignment but not before. (I kind of lost myself in that explanation).

The key is only on group assignment is when the value are copied from the persistent host template.

As for the application of this script you probably need this following

1. Log into the mysql server on your fog server as root using mysql -u root
2. Then switch to the fog database (because that is where the trigger is needd) use fog;
3. Then paste the script into the mysql console (you may have to it enter on the end of the paste to get it to take).

That should be it. Now just create your host template and create your persistent group naming them exactly the same (case and everything). Then take a host and assign it to persistent group.

[JJ Fullmer]

@george1421 Well this may just work perfectly for me. Since each of my groups shares a common abbreviation at the start of their hostname related to their group. I am excited to play with this.

[JJ Fullmer]

To uninstall/disable the feature…Obviously only would be used when updating the trigger

drop trigger new_groupmember_added;

[george1421]

@JJ-Fullmer Yep, there have been a few database changes since 8 months ago. Hopefully with the help @JJ-Fullmer we can get the trigger updated for the new fields. JJ also reported the snapins are not being associated with the new client.

[george1421]

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.

[Wayne Workman]

@george1421 said in FOG 1.3 persistent groups:

A host can be members in several persistent groups, but only the last persistent group wins for updating the settings.

How is the last group determined? How can we manipulate this via the web GUI so a particular group is last or not last? Or should the issue not be so common to worry about?

[Tom Elliott]

@Wayne-Workman the group idea is currently, the host name that matches that of a group name will apply that matching host settings to any host that gets added.

The last group a host is inserted into (the latest group you added a host to) would define the host to have that new groups settings.

It’s basically your addition. Currently it only does this adding when and if a hostname matches the group name.