Finding and Deleting Duplicate grmember (UserGroup membership) Entries

MGanon · ‎06-27-2019

We use an LDAP data source to populate users, groups, and group memberships. We have identified that the sys_user_grmember table has duplicate entries. For example, if user A should belong to group X, there may be more than 1 A/X entry in the sys_user_grmember list, making the group member list appear longer than necessary. This does not happen often. I will investigate the root cause separately. In the meantime, I need to remove the duplicates.

The https://community.servicenow.com/community?id=community_question&sys_id=91864725db1cdbc01dcaf3231f9619d9&view_source=searchResult posting is helpful but I want to find duplicate records in the source table and remove them.

How is an effective method to find those duplicate entries in a ServiceNow table given a set of criteria?

Brian Lancaster · ‎06-27-2019

You can probably use something like this. We were getting users with duplicate email because of multiple sources of records and we used this script to be able to find them.

var userGr = new GlideAggregate('sys_user');    // replace with your table name
userGr.groupBy('email');                 //  field on which you want to find duplicate
userGr.addHaving('COUNT', '>', 1);
userGr.query();
while(userGr.next()){
      gs.print(userGr.email);      // adjust field name as per your table
}

View solution in original post

MGanon · ‎07-09-2019

Hi Ankur,
You are correct that I need to identify duplicates for a concatenation of multiple fields.

andreaszemla · ‎07-01-2022

I'm a bit disappointed that the ServiceNow sys_user_grmember table doesn't have a distributed primary key that prevents duplicates from being created. Alternatively, the SOAP or REST service could take over. Additionally, ServiecNow does not support group nesting, which most operating systems do.

G_30 · ‎11-22-2022

Hi Good day! I would just like to ask what is the root cause for this? and did the script works on duplicate entries? did you just use business rule before query for the script?

Brian Lancaster · ‎11-22-2022

I don't know what the root cause of the original question would have been. However the script I provider was just something to run in script background so you can find the duplicates.

Tonyskin · ‎02-22-2023

Do you have the part that will then remove one of the duplicate entries? I tried to run the following script, but it does nothing (no error, just doesn't remove the duplicates thought it will list the duplicate groups I am a member of.)

var userGr = new GlideAggregate('sys_user_grmember');

userGr.groupBy('group');

userGr.addEncodedQuery('user=62c5d5721bff1dd8cc6554ab234bcb88'); // My sys_id

userGr.addHaving('COUNT', '>', 1);

userGr.query();

while(userGr.next()) {

var grpGR = new GlideRecord('sys_user_grmember');

grpGR.addEncodedQuery('user=62c5d5721bff1dd8cc6554ab234bcb88^group='+userGr.group); // My sy_id

grpGR.setLimit(1);

grpGR.query();

while (grpGR.next()); {

gs.print(grpGR.group.name);

grpGR.deleteRecord();

}