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

[Eya BEN OTHMEN]

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 · Accepted solution]

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

[Eya BEN OTHMEN]

Hello Mehmet,

Thank you for your help :)

it works for JIRA, however for confluence I don't have access to the database, would it be possible to use a macro to export the number of active users by group please?

Thanks in advance :)

[Rinaldi James Michael]

(COMMENT DELETED)

[Mohamed Benziane]

Hi,

You can take a look at this thread:

https://community.atlassian.com/t5/Jira-questions/Jira-mySQL-query-Active-users-that-belong-to-a-specific-group/qaq-p/1089863

and this KB for confluence

https://confluence.atlassian.com/confkb/how-to-get-a-list-of-active-users-counting-towards-the-confluence-license-298978076.html

[Marta Woźniak-Semeniuk]

Nice :) I did not know that was possible - thnx @Mohamed Benziane 

[Marta Woźniak-Semeniuk]

Hey @Eya BEN OTHMEN - welcome to Atlassian Community :)

If you have confluence as well as jira then you can add a “/user list” to a confluence page for a given group. The list is pulled every time the page is refreshed. 

Hope that helps:)