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]

var gr= new GlideRecord('sys_user_grmember');

gr.query();

while(gr.next()){

var gr1=new GlideRecord('sys_user_group');

gr1.addQuery('sys_id',gr.group);

gr1.query();

if(gr1.next()){

}

else{

gr.deleteRecord(); // be careful while using this statement, I recommend to use gs.print() in the background scripts first and see if it is pulling the correct records

}

}

[venkatiyer1]

I would recommend adding an active check to the first query and also modifying the code to check for active flag

var gr = new GlideRecord("sys_user_grmember");
gr.addActiveQuery();
gr.query();
while (gr.next()) {
   var grGroup = new GlideRecord("sys_user_group");
   grGroup.get(gr.group.sys_id);
   if(!grGroup.active) {
   gr.deleteRecord();
   }
}

[jamesmcwhinney]

It seems to only delete the first record and then gets kicked out of the loop...

Any idea why?

[jamesmcwhinney]

And is there a way to do this using deletemultiple() so that it is more performance friendly?