How do you find duplicate user records in ServiceNow that have the same email address

[matthew_hughes]

Hi,

We've discovered an issue in Servicenow if a user has more than one ID with the same email address, it stops from receiving emails about requests pending their approval.

So what I'm wanting to do is find a way of searching for all users that have the same email address in more than one user record. I was just wondering how this can be done.

[matthew_hughes]

Hi. How do you run a background script?

[Aniko Hegedus2]

Paste this into a background script

var ga = new GlideAggregate('sys_user');
ga.addAggregate('COUNT', 'email');
ga.orderByAggregate('count', 'email');
ga.query();

while(ga.next()) {

gs.info(ga.email + " identicalEmails " + ga.getAggregate('COUNT', 'email'));

}

[matthew_hughes]

Hi. How do you run a background script?

[RudhraKAM]

Please mark as correct if my solution helps fixing your issue

[Aniket Narode]

//Iterate through whole records
var gr = new GlideAggregate('sys_user');
gr.addAggregate('COUNT', 'email'); // Email is a Unique key
gr.groupBy('email');
gr.addHaving('COUNT', '>', 1);
gr.query();
while (gr.next())
{

gs.log("Numb of Duplicates: " + gr.getAggregate('COUNT', 'email') + " => " + gr.user_name, 'TEST');
var dup = new GlideRecord('sys_user');
dup.addQuery('sys_id',gr.sys_id); // Get Sysid of record
dup.addActiveQuery();
dup.query();
if(dup.next())
{

var del = new GlideRecord('u_alm_mobile');
del.addQuery('sys_id','!=',dup.sys_id); // Keep Active Record
del.addQuery('email',dup.email); // Search for Duplicate Email Ids Record
del.query();
while(del.next())
{
del.deleteRecord(); // Delete Duplicate Id's Record.
}
}
}

If Possible mark it helpful or Correct.

Thanks,

Aniket