How to lists all groups with number of group members?

[bbf3562]

As SN administrator, I want to list all queue groups with number of group members in the system, set a field for number of members and order the data by count so that I can find number of members in group. Also I want to be able to set categories of large and small numbers of members if possible.

Where in ServiceNow that I can create that data view and I believe I can use SQL script using "having" clause?

Here is mock example desired to be on list sort on count(number of members),

GJK-QUEUE NAME-IT-SYSTEM(25) <--- 25 is number of members.

[bbf3562 · Accepted solution]

I have found a way to be able to sort out list based on number of members is by creating a new field for total number of members.

First, I create a 2 new columns in sys_user_group like Total of Group Member and Total of Active Group Member.

Second, I create a script in Scheduled Job to run and populate data in 2 new columns to show results number of members. Screenshot show below

Third, I create a report based on group and show list of group name, total of group members, and total of active group members. Now I can sort out the numbers. Here screenshot example,