What is the correct entity relationship to query JIRA user groups?

What is the valid SQL among the following two?

Shall I join by IDs or parent/child names? These two SQLs yield different row counts.

select *
from
  cwd_user u,
  cwd_group g,
  cwd_membership m
where
  m.child_id = u.id and
  m.parent_id = g.id;
  
select *
from
  cwd_user u,
  cwd_group g,
  cwd_membership m
where
  m.lower_child_name = u.lower_user_name and
  m.lower_parent_name = g.lower_group_name;

2 answers

4 votes

Use child_id and parent_id. Note that if you have nested groups enabled, there may GROUP_GROUP relation in the membership table as well!

0 vote
Yilin Mo Atlassian Team Dec 09, 2012

May you can tell me what exactly you want to count? the number of user counted in the license maybe?

This is for auditing purposes. Follwing are my required outputs:

  • Users counted in the license & having access to JIRA.
  • Users having bulk change permission.
  • Internal users (for those who have been granted the permission to browse users)

My requirement is a list of users that can be exported to a spreadsheet program.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,334 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot