How to remove multiple users from all the assinged groups

[Sontel]

I have a report of users who are not logged into their SN account for past 30 days. I would like to remove all the groups they're part of. Apart from manually going into each user record and removing the group, Is there any other way by which we can remove all the assigned group from all those user records?

[Lukasz Bojara]

Hi Sonesh,

What you can do is to remove all records for the user from the sys_user_grmember table. This table holds all group membership. This is an example of a script that removes 1 user from all groups where he is a member:

var grUser = new GlideRecord('sys_user_grmember');

grUser.addQuery('user','5137153cc611227c000bbd1bd8cd2005');
grUser.query();

grUser.deleteMultiple();

You could create a Scheduled Job that will first search for such users and than removes their group membership.

Or if not scripted just go to the list view of this table, create a filter with user names, run filter, mark all resulted records and delete them.

Hope this helped.

Best regards,
Łukasz

[vinothkumar]

Hi,

I have created a scheduled job and we implemented similar requirement in our organization. Modify it according to your requirement.

var thisInstanceName = gs.getProperty("instance_name");
if(thisInstanceName == "prodinstance")
{
licenseCleanUp();
}
else
{
gs.log("Scripted not executed because the instance is " + thisInstanceName + ". It will run only in PROD" );
}

function licenseCleanUp()
{
gs.log("Inside License clean up script");

var grUser = new GlideRecord('sys_user');
var m = "roles=itil^ORroles=certification^ORroles=scrum_story_creator^last_login_time>javascript:gs.beginningOfOneYearAgo()^last_login_timeRELATIVELE@dayofweek@ago@31";
grUser.addEncodedQuery(m);
grUser.query(); // Querying the active user record, who has not logged into the system for more than 31 days
while(grUser.next())
{
if(!gs.getUser().getUserByID(grUser.user_name).isMemberOf("f0f1c798db90630027f7f3051d961913")) // Not a member of ITIL License Exclusion group
{
var grmember = new GlideRecord('sys_user_grmember');
grmember.addQuery('user', grUser.sys_id); // Checking group membership
grmember.query();
while(grmember.next())
{
var grmember2 = new GlideRecord('sys_user_grmember');
grmember2.addQuery('group', grmember.group);
grmember2.addQuery('user', grmember.user);
grmember2.query();
if(grmember2.next())
{
var grpsysID =grmember2.group;
// Before deleting the membership, check the group has ateast one member in it.
var b;
var grmember3 = new GlideRecord('sys_user_grmember');
grmember3.addQuery('group', grpsysID);
grmember3.query();
if(grmember3.next())
{
b = grmember3.getRowCount();
if(b > 1)
{
gs.eventQueue('licensecleanup',grUser,grUser.sys_id, grmember2.group);
grmember2.deleteRecord();
gs.log("Aug 2018 Month License to be removed " +grmember2.user.name + " Group Name " +grmember2.group.name + "Last Login Time" + grmember2.user.last_login_time);

}
}

}


}

}
}

}

[Sontel]

Thanks Guys!! Your suggestions were helpful , I created new script following how you designed and able to get the requirement fulfilled. Here is the snippet for anyone else looking for solution 

var grUser = new GlideRecord('sys_user');
var q = "nameLIKEKaushal^active=true";   //User list from our report( ITIL Users - no login within last 30 days, for testing filtered only name like Kaushal)
grUser.addEncodedQuery(q);
grUser.query();   // Querying the user record, who have not logged into the system for more than 30 days

var i =0;
while(grUser.next())
{
i++;

var grMem = new GlideRecord('sys_user_grmember');  //querying the group membership table to get the list of groups a user is part of
grMem.addQuery('user', grUser.sys_id);
grMem.addEncodedQuery('group!=197267bd1b9d3f00651386ae6e4bxxxx'); //if you would like to exclude any specific group then add here
grMem.query();
while(grMem.next())
{
gs.print(grMem.user.getDisplayValue() +" "+ grMem.group.getDisplayValue());

}

grMem.deleteMultiple();  //Removing all the groups
}
gs.print('Number of Users executed=' +i);