How to delete ITIL group from users not logged in 90 days ago

vahini
Giga Guru

‎07-26-2023 05:07 PM

Hello all, 

I am working on removing ITIL group from all the users who haven't logged into ServiceNow for last 90 days ago.

after running the filter on user table i got like 128 user records.

I need to remove ITIL group from all these users. 

I am using below code in the back ground script to achieve it but it only deletes itil group from one user at a time.

I have to run back ground script 128 times if i use below code which takes lot of time.

 

Does any body have a code to mass delete ITIL group from all these users at a time and also please tell me how to build an  automation to delete itil group from a users if he /she not logged in to ServiceNow for last 90 days. 

 

Thanks in advance for your time in  reading my post and responding. Appreciate your help.

 

not logged in 90 days.PNG

Code: 

var gr = new GlideRecord('sys_user');
var queryString = "roles=ITIL^last_loginRELATIVELT@dayofweek@ago@90^active=true^nameNOT LIKESystem Administrator^nameNOT LIKEESLA Alert^nameNOT LIKEESLA User";
gr.addEncodedQuery(queryString);

gr.query();
//gs.print("Count : " + gr.getRowCount());

while(gr.next())
{

gs.print("User Name:"+ gr.user_name+ " " + "User Name:" +gr.name +" " + "sys_id:" + gr.sys_id);
var groupGR = new GlideRecord('sys_user_grmember');
groupGR.addQuery('user', gr.sys_id);
//groupGR.addEncodedQuery('group.roles=itil');
groupGR.query();
while (groupGR.next()) {
//gs.print("User is in group::"+groupGR.getDisplayValue('group'));
groupGR.deleteRecord();
//groupGr.deleteMultiple(); -->  tried this also, doing the same, deletes only one record.
}
}

 

Preview file
42 KB
0 Helpfuls
  • 446 Views
2 REPLIES 2

Bert_c1
Kilo Patron

‎07-26-2023 05:39 PM

Hi vahini,

 

this sounds like the issue using 'gr' as a GlideRecord variable, see:

 

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0713029

 

instead of 'gr' edit script to use 'sugr' (or something different) in your script.

0 Helpfuls

Tony Chatfield1
Kilo Patron

‎07-26-2023 05:45 PM

Hi, you should not have to query individual users and could refine your sys_user query to return all users impacted and then loop through them, or you could run your query directly from sys_user_grmember if you are not updating the user records.
Also, I think you would also need to give some consideration to users who many never have logged in, if their group relationship was created more than 90 days ago? and I would exclude the active = true query so that you are sure any inactive users are also cleaned up.

Using this PDI list as a guideline.

/sys_user_grmember_list.do?sysparm_query=user.last_login_timeRELATIVELT%40dayofweek%40ago%4090%5Euser.roles%3Ditil%5ENQsys_created_onRELATIVELT%40dayofweek%40ago%4090%5Euser.last_login_timeISEMPTY%5Euser.roles%3Ditil&sysparm_view=

Something like this should work.

var myQuery = 'user.last_login<javascript&colon;gs.beginningOfLast90Days()^NQsys_created_onRELATIVELT@dayofweek@ago@90^user.last_loginISEMPTY';

var groupGR = new GlideRecord('sys_user_grmember');
groupGR.addEncodedQuery(myQuery);
groupGR.query();

groupGR.deleteMultiple();

 

To automate, you would simply create a scheduled job to run periodically IE daily or weekly
and include your final script in the job.

0 Helpfuls