Background Script to remove duplicate records from User Role table

Ashish Kumar Ag · ‎08-18-2015

Hi ServiceNow Developers,

I want to remove the existing duplicate records from User Role table(sys_user_has_role) table.

Please Suggest.

Thanks!

andrew_venables · ‎08-19-2015

Firstly don't use a variable names "gr" in a Background Script. There is a known issue with this.

Secondly, you should consider whether this is something you really should be doing?

The platform deliberately creates multiple sys_user_has_role records, it's be design, so by trying to fix you are breaking something else.

For example if John is a member of the Network team and the Database team, he may have the itil role twice (two records in the sys_user_has_role table). Each one will track which group assigned him the role. If you hack the platform so he only has one record, let's say you delete the one corresponding to the Network team, he is only left with the one corresponding to the Database team. If John is then removed from the Database team the platform will remove his remaining sys_user_has_role record and he won't have the itil role anymore even tho he is still in the Networks team!

Thirdly your code is pretty inefficient, optimise it with GlideAggregate:

// Group the user role records by user/role combo


var userRoleGA = new GlideAggregate('sys_user_has_role');


userRoleGA.groupBy('user');


userRoleGA.groupBy('role');


userRoleGA.query();


var userRoleGR;


while (userRoleGA.next()) {


     // Fetch the records for this user/role pair


     userRoleGR = new GlideRecord('sys_user_has_role');


     userRoleGR.addQuery('user', userRoleGA.user);


     userRoleGR.addQuery('role', userRoleGA.role);


     userRoleGR.query();


     userRoleGR.next(); // Skip the first result


     while (userRoleGR.next()) { // delete the rest


             userRoleGR.deleteRecord();


     }


}

View solution in original post

Gurpreet07 · ‎08-18-2015

By duplicates , do you mean the same role is assigned multiple times to a user or there are multiple rows for a user ?

Ashish Kumar Ag · ‎08-18-2015

Same role assigned to a user multiple times. I have written a BR to prevent it for newly created one.

But for existing records no luck yet.

Gurpreet07 · ‎08-19-2015

Following script should do the job . please test it first for one user record by removing the comment from 2nd line and use a valid user name in here.

var gr = new GlideRecord('sys_user_has_role');

//gr.addQuery('user.user_name','admin') ; //remove comment and test this script for one user

gr.query();

while(gr.next()){

var gr2 = new GlideRecord('sys_user_has_role') ;

gr2.addQuery('user',gr.user);

gr2.addQuery('role',gr.role);

gr2.query() ;

if(gr2.next()){ // this statement will skip the first row

while(gr2.next()){ // Delete other rows

gr2.deleteRecord() ;

}

Ashish Kumar Ag · ‎08-19-2015

I have tried something like below. Suppose i have two records for ITIL then it's deleting both. Its executing for both record, my thought was if 2nd record will be deleted then script will not run for it and 1st one will remain.

var usr = new GlideRecord('sys_user_has_role');

usr.addQuery('role.name','itil');

usr.query();

var i = 0;

while (usr.next()) {

var gr = new GlideRecord('sys_user_has_role');

gr.addQuery('user',usr.user);

gr.addQuery('sys_id','!=',usr.sys_id);

gr.query();

while(gr.next()){

gr.deleteRecord();

i++;

}