How to populate BU value in User table based on user table source column values and group table Name

[e__rajesh_badam]

Hi Everyone,

Need help to populate BU values in user Profile.

Challenge is the values which need to consider are present in sys_user and sys_user_group table.

Source is the column in User table, Have to use one of the value in Source column i.e., OU= PHA (only three letters) and need to check that value in  sys_user_group table in group table column i.e., Name =PHA, and we need to check value which is present in the Parent column and that value need to place it into user BU column. In Above Scenario the value present in Parent column of PHA is Philippines. Need to pull this value and need to store that value into the BU column of sys_user table.

Like we have around 60 OU's by tagging with parent Names. I need to populate the Value accordingly into user profile based on Source column and by pulling parent value.

Looking for help to achieve it.

Final code (issue resolved) one added in the Loop, please checkout.

[Ankur Bawiskar · Accepted solution]

@e__rajesh_badam 

u_bu is reference field pointing to sys_user_group?

If yes then try this, but please enhance it further as per your requirement

(function executeRule(current, previous /*null when async*/ ) {
    // Step 1: Extract the 3 characters from the source field
    var sourceValue = current.source_field; // Replace 'source_field' with the actual field name
    var extractedValue = sourceValue.substring(3, 6); // Adjust the indices as needed

    // Step 2: Query the sys_user_group table with the extracted value
    var groupGR = new GlideRecord('sys_user_group');
    groupGR.addQuery('name', extractedValue); // Replace 'name' with the actual column name if different
    groupGR.query();
    if (groupGR.next()) {
        // Step 3: Get the parent group record
        var parentGroup = groupGR.parent; // Adjust if the parent field is different

        // Step 4: Store the parent group in the bu field of the sys_user table
        current.u_bu = parentGroup;
    }
})(current, previous);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar · Accepted solution]

@e__rajesh_badam 

which field in sys_user_group holds the 3 characters?

As per your earlier image it was name field so I gave name field

is u_business_unit a reference field? if yes then enhance below

you can enhance as per your requirement

var groupGR = new GlideRecord('sys_user_group');
groupGR.addQuery('source','ldap:'+ extractedValue); // are you comparing the correct value here
// groupGR.addQuery('')
groupGR.query();
if (groupGR.next()) {
// Step 3: Get the parent group record
var parentGroup = groupGR.parent.sys_id; // use sys_id if u_business_unit is reference to Group
gs.addInfoMessage('parent:'+parentGroup);// Showing respective Parent value but not loading it into User BU column.

// Step 4: Store the parent group in the bu field of the sys_user table
current.u_business_unit = parentGroup;

}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[e__rajesh_badam · Accepted solution]

Thanks for your support @Ankur Bawiskar ,

For Everyone reference the final code is

(function executeRule(current, previous /*null when async*/ ) {

    // Extract the Required characters from the source field

    var sourceValue = current.source;

    var extractedValue = sourceValue.substring(sourceValue.indexOf("OU="));

   // Query the sys_user_group table with the extracted value

    var groupGR = new GlideRecord('sys_user_group');

    groupGR.addQuery('source','ldap:'+ extractedValue);

    groupGR.query();

    if (groupGR.next()) {

        // Step 3: Get the parent group record

        var parentGroup = groupGR.parent;

        var parentGroup1 = groupGR.parent.name;

        // Step 4: Store the parent group in the bu field of the sys_user table

        current.u_bu = parentGroup;

        current.u_business_unit = parentGroup1;

    }

})(current, previous);

[e__rajesh_badam]

Hi @Ankur Bawiskar ,

Before moving code into Prod we tested and it was worked, but the Problem happening now is BU value not populating automatically when source value is populated in User profile

1. Source value will get populate/add in user profile when user login from his end.

2. I did changes in code as suggested, where am comparing User profile source value with Group table source Value. if both matches then am populating the BU value in user profile.

When I tested manually BU value is populating but yesterday evening two people logged in and Source Value got added in user profile when they logged in but BU value not adding automatically in User profile level.

I tried below BR with condition (Source changes) and without condition while doing testing and it worked. But now the value is not populating automatically.

Please provide your suggestion.

[Ankur Bawiskar]

@e__rajesh_badam 

check by adding logs if they are able to query group table

If my response helped please mark it correct and close the thread so that it benefits future readers.