Active groups with no members and Roles

[Ashik Suresh]

I wanted to filter out the active groups with no members and roles.

I navigated to sys_user_group and when i tried out the filters, i could get the active groups with no members. But i cant filter out the groups with no roles.

I tried the filter 'Group. Roles' is 'empty'. But not showing up the results.

Could anyone please help me?

[Kartik Sethi]

Hi @Ashik Suresh 

For

• Group roles, you can query on table "sys_group_has_role"
• Member, you can query on table "sys_user_grmember"

So you can use these 2 tables and get the details.

---

Please mark my answer as correct if this solves your issues!

If it helped you in any way then please mark helpful!

Thanks and regards,

Kartik

[Ashik Suresh]

Hi @Kartik Sethi , Thanks for your suggestion. But I wanted to combine these two and create a report on this.

[Murthy Ch]

Hello @Ashik Suresh 

Try something like this and call it in your report:

getActEmptyGrps: function() {
        var arrG = [];
        var grA = new GlideRecord("sys_user_group");
        grA.addActiveQuery();
        grA.query();
        while (grA.next()) {
            var grG = new GlideAggregate("sys_user_grmember");
            grG.addQuery("group", grA.getUniqueValue());
            grG.addAggregate("COUNT");
            grG.query();
            if (grG.next()) {
                if (grG.getAggregate("COUNT") == 0) {
                    var grR = new GlideAggregate("sys_group_has_role");
                    grR.addQuery("group", grA.getUniqueValue());
                    grR.addAggregate("COUNT");
                    grR.query();
                    if (grR.next()) {
                        if (grR.getAggregate("COUNT") == 0) {
                            arrG.push(grA.getDisplayValue());
                        }
                    }
                }
            }
        }
        return arrG.toString();
    },

In Report your filter condition would be like below:

Thanks,

Murthy

[Murthy Ch]