Removing Invalid References

[jamesmcwhinney]

Somewhere along the way, we ended up with a number of users linked to groups which no longer exist.

In the user's profile underneath groups, I see a long list of blank entries.

Could anyone reccomend a good script to clear out entries in a table wherein the reference does not exist.

For example, something along the lines of:

Delete * from sys_user_grmember where group not in (select sys_id from sys_user_group)

Thanks!

[Abhinay Erra]

I believe Group members table does not have active flag. So adding 'addActiveQuery()' will not do anything. I hope the following script helps in deleting multiple records.

//This deletes all the group members whose group is inactive

var groupArray='';

var gr1=new GlideRecord('sys_user_group');

gr1.addActiveQuery();

gr1.query();

while(gr1.next()){

if(groupArray.length>0){

groupArray += ","+gr1.sys_id.toString();

}

else{

groupArray=gr1.sys_id.toString();

}

}

var gr= new GlideRecord('sys_user_grmember');

gr.addQuery('group','NOT IN',groupArray);

gr.addNotNullQuery('group');

gr.query();

gr.deleteMultiple(); // before using this method test it in the background scripts using gs.print()

//This deletes all the group members whose group is empty

var gr2= new GlideRecord('sys_user_grmember');

gr2.addNullQuery('group');

gr2.query();

gr2.deleteMultiple(); // before using this method test it in the background scripts using gs.print()

[venkatiyer1]

Hi James,

I agree with you. deletemultiple would be better if there are more records. In my script for every record there there is a one to one association for user and group and so was deleting it individually. For multiple record deletion, you can may be try this script

// Look for Groups records that have associated group records

var gr = new GlideRecord('sys_user_grmember');

var grSQ = gr.addJoinQuery('sys_user_group', 'group');

// group is the field name we want the primary field to be of the primary table i.e. sys_user_grmember

// And the Group records are "active=false"

grSQ.addCondition('active', 'false');

// Query

gr.query();

Reference to the above can be found here below:-

Scoped GlideRecord API Reference - ServiceNow Wiki

Please log the output to check whether it is fully working before deleting the records.

[jamesmcwhinney]

This seems like its what I need, but strangely it doesn't work.

I tried the below and ended up with the same count for active= true vs active=false.

// Look for Groups records that have associated group records

var gr = new GlideRecord('sys_user_grmember');

var grJ = gr.addJoinQuery('sys_user_group', 'group');

grJ.addCondition('active', 'true');

gr.query();

var Count = 0;

while(gr.next()){

Count = Count + 1;

}

gs.log("Final Count: " + Count);

[jamesmcwhinney · Accepted solution]

This seems to be giving me what I need.

I will use this along with deleteMultiple()

var gr = new GlideRecord('sys_user_grmember');

gr.addEncodedQuery('group.nameISEMPTY');

gr.query();

gs.log("Final Count: " + gr.getRowCount());