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?

[Marc Mast · Accepted solution]

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.

[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.