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 Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Jira

Mission-critical battery manufacturer fulfills FAA software requirements with Commit Policy Plugin

EaglePicher Technologies is a leading manufacturer of battery systems for diverse industries like defense, aviation, space or medical. As they operate in highly regulated industries, keeping a clear ...

178 views 0 2
Read article

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