Duplicate record

[#1]

I have some duplicate records in my table "sys_user". How to make one record as inactive based on some field ( like email because in one record email field is empty) & to add prefix like old-username .....

[sherman_1206]

You can do a report to determine the list of users in question. Once you have this list depending upon the size you could do a mass change on that table (the report table containing all users you wish to have deactivated) by displaying the active column and changing this field to inactive.

There are also ways to script this, personally I would steer towards web services as thats my niche. A simple SOAP script could update the user records easily.

Anyone know a way to do this within the tool itself?

[Jay_Ford]

I would use a Background Script to find and list all of these duplicates, then once you are sure you want to change the records that are returned, modify the script a bit to change the values you wish to change. If you are using LDAP synchronization you need to make sure you think about the coalesce field also. Here is a script that will find all duplicate user_name 's and print them on screen for you. You can modify it a bit to pickup what you need, then after you're sure it's only picking up records you want, modify it to change each of the records and update().

gatherDupes();

function gatherDupes() {
var ga = new GlideAggregate('sys_user');
ga.addAggregate('COUNT', 'user_name');
ga.addQuery('user_name', '!=', '');
ga.groupBy('user_name');
ga.addHaving('COUNT', '>', 1);
ga.query();
var dupNames = [];
gs.print('The sys_user table has ' + ga.getRowCount() + ' duplicates based on user_name field...');
while (ga.next()) {
gs.print(ga.user_name);
fixDuplicates(ga.user_name);
}
}
function fixDuplicates(userName) {
   var gr = new GlideRecord('sys_user');
   gr.addQuery('user_name', userName);
   gr.orderByDesc('sys_created_on');
   gr.query();
while (gr.next()) {
gs.print('-sys_id- ' + gr.sys_id + ' -email- ' + gr.email + ' -user_name- ' + gr.user_name);
}
}

[sherman_1206]

Is there a wiki regarding background scripts? Or is this basically just any scripting you would do within Service now that you would manually invoke by defining script in background-script and running it?

Thanks.

[Jay_Ford]

I haven't seen a wiki article devoted solely to Background Scripts. It's basically a way manually invoke a script. It's very good for changing large numbers of records, but that on the flip side can be dangerous without proper testing of the script before modifying records. This is why the example I provided simply prints the results of the queries in the script without making changes.