Group Members has groups that no longer exist

[Robert Campbell]

When I look at the Group Members table, there are groups listed that no longer exist. Is there a way to clean this up?

I don't want to go through them one-by-one to find them. I was trying to do a report but if I choose Group Member as the table, I can't choose Groups as a related table because they don't relate "that way". If I choose Groups as the table, I can then choose Group Members as a related table but this is essentially a left join on Group's table. 

[Robert Campbell]

I replaced gr.deleteMultiple(); with gs.print('Group Name: ' +gr.group.name); because I only want to see the groups right now but it won't show the group name.  If I use gs.print('User Name: ' +gr.user.name); I do get the user's name. The only two fields are user and group.

Also, the results is the same as 

[Siddhesh Gawade]

Hello @Robert Campbell ,

I made a changes to fix script. Now it should work for you.

var gr = new GlideRecord('sys_user_grmember');
gr.addEncodedQuery('group.name=');
gr.query();
while(gr.next()){
	gr.deleteMultiple();
}

[Siddhesh Gawade]

@Robert Campbell ,

Have you tried this new script? This should resolve your issue.

[Robert Campbell]

I have tried this to get the list of groups:

var gr = new GlideRecord('sys_user_grmember');
gr.addEncodedQuery('group.name=');
gr.query();
while(gr.next()){
	gs.print('Group Name: ' +gr.group);
}

But I need to group by the group and show the name but changing '+gr.group' to '+gr.group.name' doesn't work. 

var gr = new GlideAggregate('sys_user_grmember');
gr.addEncodedQuery('group.name=');
gr.orderBy('group');
gr.groupBy('group');
gr.query();
while(gr.next()){
	gs.print('Group Name: ' +gr.group);
}

I think this worked but it would be better to see the names. I have used the sys_id to check the record and the few I checked all gave the error 

[Robert Campbell]

I doubt there are 33k bad records. 34297 records in the table and the script returned 33475. I don't think that is accurate.

On the sys_user_grmember table, the group column only has a few empties. They need to be cleaned up also but the records I want to clean up are those records that have a name in the Group column but that group doesn't actually exist.