Find duplicate users in two groups

[Thomas G]

Hi,

It may sound simple but I can't wrap my head around this one at the moment.

I have two groups and need to find users that are members of both. How can I show those in a report? I guess I should look at sys_user_gr_member table. It is easy to find all users from both groups but how can I find only those that are members of both?

Best regards

Thomas

[Gunjan Kiratkar]

Hi @Thomas G ,

This requirement seems to be difficult to fulfill with normal reporting. You can make use of performance Analytics and create indicator to collect that information. In that Indicator their is option to write down script and we have to create the logic in that script to get those user list.

For better understanding of PA, read my below blog :-

Performance Analytics : Advance Reporting Tool of ServiceNow 

[Thomas G]

Hi Gunjan,

Thanks for your reply. 

Best regards
Thomas

[Thomas G]

Would it be possible by calling a Script Include in a report?

Best regards
Thomas

[Sebastian L]

Hi Thomas,

Yes that could be a way. 

So if you create a script include (and it needs to be Client callable!), then it could look like this: 

var usersMemberOfTwoGroups = Class.create();
usersMemberOfTwoGroups.prototype = {
    initialize: function() {

    },
    getUsers: function() { //You can also pass the sys_ids here in the function to make it more dynamic, but this is just for example
        var users = [];
        var agg = new GlideAggregate('sys_user_grmember');
        agg.addAggregate('COUNT', 'user');
        agg.orderBy('user');
        agg.addQuery('group', 'sys_id_grp1').addOrCondition('group', 'sys_id_grp2');
        agg.query();
        while (agg.next()) {
            var userCount = agg.getAggregate('COUNT', 'user');
            if (userCount > 1) {
                users.push(agg.getValue('user'));
            }
        }
        return users;
    },
    type: 'usersMemberOfTwoGroups'
};

Then you can create a report on the sys_user table with this condition: