SQL get local and LDAP users without duplicates

Hi,

I'm trying to get a list of all users in my JIRA instance (local and LDAP users), but when I use the following query :

SELECT cu.user_name AS username, cu.display_name AS displayname, cm.lower_parent_name AS group, cu.email_address AS email 
    FROM cwd_user AS cu
    INNER JOIN cwd_membership AS cm
        ON cu.directory_id=cm.directory_id
        AND cu.lower_user_name=cm.lower_child_name
        AND cm.membership_type='GROUP_USER'
    WHERE cm.lower_parent_name LIKE 'zz%'
	ORDER BY cu.user_name;

I have duplicate users because one exists as a local JIRA user (first one) and the second one is my LDAP user (which JIRA merged)

username                    |      displayname          |  group      |     email       |

-------------------------------------------------------------------------------------------

firstname.lastname      | Firstname Lastname  | zz group  | f.l@a.com   |

Firstname.Lastname   | Firstname Lastname  | zz group  | F.L@a.com  |

(I do hope my formatting stays alive after posting this).

I just want one of these users (preferably the first one), so I tried the following SQL query:

SELECT t.user_name, cu.display_name, cm.lower_parent_name, cu.email_address 
FROM (
	SELECT cu.user_name 
	FROM cwd_user AS cu 
	INNER JOIN cwd_membership AS cm ON cu.directory_id=cm.directory_id 
	AND cu.lower_user_name=cm.lower_child_name 
	AND cm.membership_type='GROUP_USER' 
	WHERE cm.lower_parent_name LIKE 'zz%' 
	GROUP BY LOWER(cu.user_name)
) u JOIN cwd_user t ON t.user_name = u.user_name ORDER BY t.user_name;

but with no luck. My SQL isn't that great, so i'm stuck here.

Any thoughts?

2 answers

1 accepted

I ended up making two queries to get the different kind of users in a bash script.

Could you post the bash script? I have the same issue and I'd rather not re-invent the wheel.

Run the following query to see the ids and names of your jira directory list: 

Select ID,DIRECTORY_NAME from CWD_DIRECTORY;

 

Then run the following command with the id of the required directory:

select * from CWD_USER WHERE DIRECTORY_ID = theID;

 

Set the following parameters to match the results you get in the first query:

  • theID

 

Example:

Select ID,DIRECTORY_NAME from CWD_DIRECTORY;

Output:

ID

DIRECTORY_NAME

1

JIRA Internal Directory

300

Active Directory server

 

select * from CWD_USER WHERE DIRECTORY_ID = 300;

Hi Noam,


Thanks for your response. This gives a part of the solution (and I figured it out on my own), because I want to get local and LDAP users.
For the sake of simplicity, I created two queries which satisfy my needs and put them in a bash script. Not the prettiest but it does the job.

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Tuesday in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

514 views 1 18
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