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?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.