FOG 1.3 persistent groups

[george1421]

Here is the sql script as I have it so far. It does work as advertised. Right now I’m doing additional testing on my dev FOG server before its put on the production server. This trigger will fire when a new host is associated with a group and there is a host with the same exact name as the group name.

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 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
   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 ;

[Wayne Workman]

@george1421 That’s pretty sweet looking. I didn’t know about triggers till this thread, actually.

[george1421]

@george1421 Well after fine tuning the sql query for the trigger I tested it on my dev box this morning. It worked exactly as advertised. When you associate a device with a group name, where there is a host (template) with the same exact name, the contents of the host (template) will be copied to the device that was just associated with that group. If you associate a host with a group that has no matching host (template) nothing is changed for that associated host.

There were a few caveats that I found, more like rules than caveats.

1. Your group name must conform to the rules set out for hosts. In that your Group name may not contain spaces or more than 15 characters. The edit box for the host restricts these the group name does not.
2. For every host template you must define a unique mac address, duplicates are not allowed. So for my first host template I entered 00:00:00:00:00:01 and for the second 00:00:00:00:00:02 and so on.
3. When you make a host group association all of the fields (even empty ones) will be cloned to the associated host. All existing setting will be overwritten on the association.
4. After you have made the host group association, if you make a change to the host template those new settings are not updated on all hosts associated what that group (but you can still do this via the normal group update function)
5. 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.

While I say this works, I have not attempted this (hack) on our production server just yet. I feel confident that it will work without issue. Since we are not making group associations every day (as you would if you added new hosts every day) I don’t see any performance issues.

[george1421]

Final thoughts.

While this is a hack(ish) solution. The proper solution is for the FOG management GUI to support this directly. If the developers wanted to use the same concept of just using a host as a template. I would probably do it this way.

1. Add a check box to the host configuration page and call it ?? “Make this a host a template”
2. Once the host is a template it should no longer be displayed in the list of hosts but in a new list of templates.
3. For the groups, make a new drop down list called “host template” and only show hosts where the template flag is set.
4. Then in the php code do the actions of the trigger after a host association.
5. Then you could key off if the host template was updated, ask the user if the fog application should update all hosts associated with this template.

[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.