Create a report to display duplicate users

[mattyb]

Hi all,

Im trying to generate a report to Identify users that have duplicate entries. For us, these are most often caused by the user emailing before service now gets their information.

So far, i have gotten a decent list of users but i am wondering how to compare them to the current users in the system. As of right now, i use location and employee type as empty which yields a majority of them but, i would like to compare them against the rest of the user base such as a same as which checks a last name on the list with other names that didnt meet my criteria.

If this doesnt make sense, please let me know.

Regards

MattyB

[mattyb]

Hmmm, any reason you can think of that it would't work in the demo instance?

https://demo006.service-now.com/navpage.do

That one to be specific if you have a second. I appreciate the help as this is my first experience with servicenow.

[justin_drysdale · Accepted solution]

Hi mattyb,

I logged into the demo instance you linked.   I suspect the duplicate finder script didn't work for you because the field name in my instance is different from the field name in the Demo instance.

The code was looking for this field (in bold):

	while(users.next()) {
	arr.push(users.name.toString());
	}

In the demo instance, name is undefined.   The field is actually user_name.   I attached a screenshot highlighting the demo field name:

So to make it work you'd have to adjust the field in the script:

	while(users.next()) {
	arr.push(users.user_name.toString());
	}

Of course to make it show results you will have to have some existing duplicate user_name's in the instance.

The whole thing:

//highlight duplicate users

var arr = [];

var users = new GlideRecord('sys_user');

	users.addActiveQuery();
	users.query();
	while(users.next()) {
	arr.push(users.user_name.toString());
	}

//sort:

	arr.sort(function(a, b) {
	if (a<b) { return 1 }
	if (a>b) { return -1 }
	return 0;
	});

//remove duplicates:

      for(var j=0, len = arr.length, count=0, dupes=[]; j != len; j+=1) {

	if ( arr[j] == arr[j+1] ) {//if the name is the same as the name in the next arr index
	++count;
	dupes.push(arr[j]);
	}

    }

//Output:

    gs.log("There are " + count + " duplicate users detected.\nUsers found: ")

    for (var k=0; k!=dupes.length;k+=1) {

    gs.log(dupes[k]);

    }    

I went to the Demo instance and tried creating some duplicates but I ran into a DB error:

So instead of creating user's with duplicate user_name's, we can just point the script at first_name instead to show first name duplicates.   The concept is the same.

Here I created 2 users with the first_name of 'TEST':

Next I edited the script to push the first_name field (instead of user_name):

	while(users.next()) {
	arr.push(users.first_name.toString());
	}

Finally I ran the background script (TEST is in the list):

[mattyb]

//highlight duplicate users

var arr = [];

var users = new GlideRecord('sys_user');

users.addActiveQuery();

users.query();

while(users.next()) {

arr.push(users.first_name.toLowerCase()+" "+users.last_name.toLowerCase());

}

//sort:

arr.sort(function(a, b) {

if (a<b) { return 1 }

if (a>b) { return -1 }

return 0;

});

//remove duplicates:

      for(var j=0, len = arr.length, count=0, dupes=[]; j != len; j+=1) {

if ( arr[j] == arr[j+1] ) {//if the name is the same as the name in the next arr index

++count;

dupes.push(arr[j]);

}

    }

//Output:

    gs.log("There are " + count + " duplicate users detected.\nUsers found: ")

    for (var k=0; k!=dupes.length;k+=1) {

    gs.log(dupes[k]);

    }  

This is the script with a few tweaks such as checking first and last name. It also ignores case if anyone wants to use it. thanks to justin for his help.

[shane_davis]

Matt - Once the duplicate entries are found, do you know a way to merge user accounts based on userID (user_name)?   We have many users in our sys_user table that are the same user with 2 user records.   An example is below.

Same person; two accounts.   I need to merge based on userID.   I would also want all the tickets (INC, REQ, CHG) for both accounts to be merged.

USER ID                     NAME

jdoe1234                   Doe, John

JDOE1234             Doe, John