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. 

[Siddhesh Gawade]

Hello @Robert Campbell ,

Please check XML of record and check the group sys_id of record.It shouldn't be present. If it not you can modify the fix script query to check for records where group.sys_id is empty. 

[Robert Campbell]

In MySQL I would do it like this:

SELECT
  a.name
FROM
  table_a a
LEFT JOIN
  table_b b
ON
  a.name = b.name
WHERE
 b.name is null;

[Luiz Lucena]

HI Robert, 

Maybe the query you want to use is:

group.sys_idISEMPTY

If the Group Sys_ID is empty, that means, there is no group.

I'm querying this in the sys_user_grmember table as we also have some groups that gone and the relationship still listed there.

Try to query that way in your table and see what happens, however, you won't be able to see the groups because they gone. It would show up like group name column in blank and user name filled.

EDIT: Forgot to mention, if you query by Group Name is Empty, it would not be that accurate because it would show groups that exists in your table, but with blank name. The Sys_ID in contrary must exist for an existent group. 

[Robert Campbell]

This opened up a whole new subset of errors.

However, these show up as (empty). I need those that show up as an actual group name that used to exist but no longer exist. When I click on that group name, it tells me the record doesn't exist on the Group table but it is still referenced on the Group Members table.

[Luiz Lucena]

Good!
Now, what you can do from that list is open one of the user's record (if they still exists), then you go to the Audit History for that user and look for any "relation" removed. From there, you will be able to see the Group Name.