Finding and Deleting Duplicate grmember (UserGroup membership) Entries

[MGanon]

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 · Accepted solution]

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
}

[MGanon]

"GlideAggregate" was the key that I needed to search. In addition to the helpful links provided by Mr. Kumar, I also found:

https://docs.servicenow.com/bundle/jakarta-application-development/page/script/glide-server-apis/concept/c_GlideAggregate.html

I will experiment using groupBy('Field1', 'Field2');                 

[Prateek kumar]

Take a look at these two blogs.

https://snprotips.com/blog/rvicenowprotips.com/2015/12/detecting-duplicate-records-with.html

https://www.servicenowelite.com/blog/2013/11/22/duplicate-record-scripts

[Ankur Bawiskar]

Hi Ganon,

unfortunately the scripts shared in the post will help you to determine duplicate only on 1 column; but here you want to find duplicate based on 2 columns i.e. user + group

Mark Correct if this solves your issue and also mark Helpful if you find my response worthy based on the impact.
Thanks
Ankur