Where are all JIRA accounts stored in database?

[Błażej O_]

I'm trying to pull all Jira accounts from database. Where can I find exactly the same account list as through the User Management in Jira?

Distinct from cwd_user shows twice as much user accounts as User Management.

Distinct from external_entities shows ~30% more user accounts than User Management.

[Nic Brough -Adaptavist- · Accepted solution]

It's cwd_user.

I suspect your "distinct" might be confusing things, although you have not told us what you're doing with it - users can be duplicated across directories with the same name, which could well explain what you're seeing.

As an example, I set up a client's JIRA recently.  I'm nic in the internal directory.  I'm nic in their main site's internal directory, I'm nic-s in another site's directory and I'm nic in the Adaptavist directory.  Doing a "distinct" based on the login id will give you 2 answers, which is wrong.

[Błażej O_]

Nic, I have used distinct exactly to avoid getting many records for users that come from multiple user directories :

select distinct lower_user_name
from cwd_user

Still the query shows many accounts that are not visible in User Management.

OK, I have found what's wrong. cwd_directory shows 7 directories. Those are the same directories that I can see in Jira in User Management. However when I selected:

select distinct directory_id
from cwd_user

I got 8 directories. It seems that on the beginning of the instance someone created a user directory and removed it right away. When I excluded this directory_id from search I got exactly the same number of users as in User Management in Jira.

[Nic Brough -Adaptavist-]

That makes sense - although the "distinct" is wrong because it's hiding user accounts, the users JIRA sees are masked - in my case, the Adaptavist nic is the one of the three that is used because that directory is higher than the others.

An extra directory would explain your results.