Updating group memberships with import set

[hhakkanen]

Hello,

Here is what I need to do:

I have an Exchange distribution list which I regularly export by running

Get-ADGroupMember "<Distribution list name>" -recursive| select samaccountname,name | export-csv c:\temp\exported_names.txt -Encoding unicode -NoTypeInformation -Delimiter ";"

I import this to Excel, add a column for the ServiceNow group name, populate the column with the group name and upload it to ServiceNow.

But comes the part I need help with. If using a transform map to upload the data to sys_user_grmember table it will create duplicate entries and users will have the same group listed multiple times.

I would like to insert the user_id / group   pair only if it doesn't exist in sys_user_grmember table. Ideally, I'd like to remove entries from the table which don't exist in the Excel file. And finally, automate all that by placing the distribution list export somewhere where ServiceNow can fetch it and process automatically.

But first things first, can you help me with the transform map scripting not to create duplicate entries?

Kind regards,

Henrikki

[Brad Tilton]

Hi Henrikki,

In a transform map you can coalesce on multiple entries, so I would just map both the user and group field from your import table to the user and group fields on the sys_use_grmember table and set coalesce to true for both of the field maps. That way it will insert a new record unless both of those values match, and if they do match it will just update the existing record.

Using the Coalesce Field - ServiceNow Wiki

[hhakkanen]

That should work, yes.

However, then if I remove users from my master data (the distribution list), those records will not be removed from the group in ServiceNow.

I tried to add onStart script to my transfor map to first delete all records for that group but it's not working ... :

var key_users = new GlideRecord('sys_user_grmember');

key_users.addQuery('group', 'GLO Key Users');

key_users.query();

while (key_users.next()) {

  gs.print('Key user ' + key_user.name + ' deleted');

  key_users.deleteRecord();

}

Can you tell why teh script is not deleting the existing records before inserting the new ones?

[Brad Tilton · Accepted solution]

Hi Henrikki,

The group field on the grmember table is expecting a sys_id of a group, so that line could read either:

key_users.addQuery('group.name', 'GLO Key Users');

or:

key_users.addQuery('group', 'sysid of group');