Plugin or sql querie to get the number of active users in each Confluence/ Jira group

Eya BEN OTHMEN_zovfl6

Hello Community,

help please!

Is there a plugin or an sql query that allows to have:

• the number of active users in each Confluence group
• the number of active users in each Jira group

Thank you in advance

Eya

Mehmet Şirin Usanmaz_egbzkb

Hi @Eya BEN OTHMEN

You can use the following sql queries

for jira :

SELECT DISTINCT 
cwd_group.group_name, count(cwd_user.id)
FROM cwd_user 
JOIN cwd_membership ON cwd_membership.child_id = cwd_user.id
JOIN cwd_group on cwd_membership.parent_id = cwd_group.id
WHERE cwd_user.active = 1 GROUP BY cwd_group.group_name

 for confluence :

SELECT g.group_name,count(u.lower_user_name)
FROM cwd_user u
JOIN cwd_membership m ON u.id = child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
JOIN cwd_directory d on u.directory_id = d.id
WHERE sp.PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T'
GROUP BY g.group_name