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

1 vote
Accepted answer

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
Community showcase
Published Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,304 views 12 19
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