nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL get local and LDAP users without duplicates

Marc Mast

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?

Accepted answers

Marc Mast

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

All comments

noamdah

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;

Marc Mast

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.

Marc Mast

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

Logan G Hawkes

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

sql

jira

postgres