question Re: Active groups with no members and Roles in ITSM forum

Active groups with no members and Roles

ashikcsures — Tue, 05 Jul 2022 05:16:17 GMT

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?

Re: Active groups with no members and Roles

Voona Rohila — Tue, 05 Jul 2022 05:25:54 GMT

Hi Ashik

How did you acheived the first one? where are you using this filters?

group roles and group members are stored in seperate tables.

Group Roles - sys_group_has_role, Group Members - sys_user_grmember

If you are using reports then you can use related list conditions to filter no members and no roles.

Re: Active groups with no members and Roles

kartiksethi — Tue, 05 Jul 2022 05:29:15 GMT

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

Re: Active groups with no members and Roles

ashikcsures — Tue, 05 Jul 2022 06:05:54 GMT

The purpose of this is to create a report on Active groups with no members and roles. I navigated to 'sys_user_group' and done the following :

Here i couldnt mention roles as empty. If it could be done with a related list condition, could you pls show me

Re: Active groups with no members and Roles

ashikcsures — Tue, 05 Jul 2022 06:09:14 GMT

The purpose of this is to create a report on Active groups with no members and roles. I navigated to 'sys_user_group' and done the following :

Here i couldnt mention roles as empty. If it could be done with a related list condition, could you pls show me

Re: Active groups with no members and Roles

ashikcsures — Tue, 05 Jul 2022 06:12:22 GMT

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

Re: Active groups with no members and Roles

Voona Rohila — Tue, 05 Jul 2022 06:37:13 GMT

add this related list condition along with active is true and group members is 0 filter

Re: Active groups with no members and Roles

Murthy Ch — Tue, 05 Jul 2022 06:45:08 GMT

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

Re: Active groups with no members and Roles

Murthy Ch — Tue, 05 Jul 2022 06:47:47 GMT