How would you create a report to show all groups without roles?

[#1]

How would you go about creating a report to show a list of all the groups in the system that don't have at least one role assigned? I'm having problems finding which table to draw this from.

Thank you!

[CapaJC]

I'll let someone else tackle the report question, but running this script in Scripts - Background would give you the info:

doit();
function doit() {
  var gr = new GlideRecord('sys_user_group');
  gr.orderBy("name");
  gr.query();
  while (gr.next()) {
    var role = new GlideRecord("sys_group_has_role");
    role.addQuery("group", gr.sys_id);
    role.query();
    if (!role.hasNext())
      gs.print("Group: " + gr.name + " has no roles");
  }
}

[#3]

CapaJC,

Thank you! That worked perfectly!

I don't really care about making this a report, this was a one-time thing to see which groups didn't have the ITIL role attached to it and it works awesome.

Thank you!

[ReginaldFawcett]

Just in case anyone stumbles upon this old question. Here is another solution to this question.

Create a report on the Group table where Active is true.

Add a Related List condition on the Roles table, and set the record condition to Less then 1

Hope this helps.