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

This widget could not be displayed.

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!

This widget could not be displayed.
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
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

146 views 2 0
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