FOG 1.3 persistent groups


  • Moderator

    I still have a need to get persistent groups working in fog 1.3.x. While I understand that it may be possible in FOG 2.0, that software is not here today.

    First the problem I’m trying to solve. In my AD structure I have different base OUs for different locations. Within those location OUs I have different sub OUs for different types of devices (workstations, instruments, kiosks, etc). These different OUs could have different images, or the same image used for multiple OUs. I can do exactly what I need with post install scripts detecting the subnet where the target device is and what image is pushed to it. I can take this calculated info and populate the unattend.xml script during images. This all works fine. But its a bit cryptic, requires a programmer to make adjustments and I loose out of some of the functionality of FOG because the database fields are out of sync with the target device. If I can’t get the persistent groups to work as I need, I do have this route as a fall back method.

    I think I figured a way to hack a solution together that won’t break 1.3.0 (when its released). Right now I’m just posting this here for a sanity check before I go about messing with FOG’s innards.

    The idea I had is to create a manually registered host. Lets call this a host template (with an invalid mac address like 00:00:00:00:00:00). The name of that host template will match exactly the text name of the group I’ve created previously. (it would be much easier if I could just create a new integer field in the hosts table and call it TemplateID. And that TemplateID would point to a GroupID. But again I don’t want to break fog 1.3.0 or mess with the db schema). As I register new new hosts, I’ll assign them to a primary group when doing a full registration. This will create a new entry in the groupMembers table (so far this is all normal processes on how we register machines today).

    Now I have a linking between a host template record (in pseudo code: [template.name == group.name] and [group.id==host.id] ) I can create a mysql database trigger after insert to fire off a sql script to copy the host template fields to the inserted host where the inserted host fields are null (or empty).

    So far the path sounds logical and somewhat easy(ish).


  • Senior Developer

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


  • Moderator

    @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?


  • Moderator

    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.


  • Moderator

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


  • Testers

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

    drop trigger new_groupmember_added;
    

  • Testers

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


  • Moderator

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


  • Testers

    @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?


  • Moderator

    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 ;
    

  • Moderator

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


  • Moderator

    @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… ???


  • Developer

    Great work George!


  • Moderator

    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.

  • Moderator

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


  • Moderator

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


  • Moderator

    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 ;
    
    

  • Moderator

    I didn’t forget about this project (hack), I’ve been tied up with a few commitments for the last few days so I haven’t been able to push to far. I’m going to post what I have so far (just the concept) so I don’t forget what has been done so far.

    I’ve looked into how I can best augment what is in place to allow some kind of persistence so newly added hosts will be updated with the values I’ve defined in the host template.

    I’ve looked through the db structure for FOG and identified several tables that could help me in my quest. These tables are (hosts, groupMembers, printerAssoc, snapinAssoc, moduleStatusByHost). My approach will a specific database event that is triggered when a record is added to the groupMembers table. This trigger will fire after a new record is added to this table. The trigger will check to see if the group which is associated to the host, has a matching template (also stored in the hosts table). The trigger will attempt to find a hostname that matches the group name (exactly). If it finds this template, it will copy the contents of certain fields from the template record to the host record. After that update is done it will copy the snapins, printers, and modules from the matching host template to the host. In theory this “should work”. If a new group association is added and there is no matching host template then nothing is updated. The only down side I can see with this approach is if you have an established host with custom settings and you assign that host to a group where there is an associated host template, existing values will be overwritten with template values.


  • Moderator

    @george1421 Well then… Sounds like your approach is far superior. If you can handle this in the DB alone somehow, go for it man. I will always opt for a smaller technology stack than a larger one to get something done.

    Just fill in the rest of us wondering how it works ;-)


  • Moderator

    @Wayne-Workman While I haven’t started doing anything yet (waiting for some sanity), I think I’m pretty close with the existing infrastructure. That sql trigger will do all of the dirty work. I’ve coded in sql for about 20 years. Unless mysql is really “out there” that part shouldn’t be that difficult (mssql and oracle can do this, no problem).


Log in to reply
 

382
Online

38974
Users

10712
Topics

101674
Posts

Looks like your connection to FOG Project was lost, please wait while we try to reconnect.