Jira userlist

[Jani Jani]

Hi,

How can I get userlist (realnames/usernames) out of the Jira. I need to submit such forward to check for old employees etc.

[Lasse Langhorn]

You can use the Jira User Export app for Jira Server. It is free to download and use: https://marketplace.atlassian.com/apps/1220535/jira-user-export?hosting=server&tab=overview

Regards

Lasse Langhorn

[Nic Brough -Adaptavist-]

Depends on what access you have and what you need. Are you a Jira admin? Do you have read access to the database? (If the answer to both of those is "no", then you probably can't do it anyway)

[Jani Jani]

Yes and Yes

I just need a quick way to list users in some sensible form so I can get a decent list out.

[Jani Jani]

Hi,

Jira in question is old 4.0 upgraded from 3.xx

it still old userbase+membershipbase etc.

[mlassau_atlassian]

OK - the table structure was pretty ugly back then :(

For username and full name run this:

select username, psfn.propertyvalue as full_name
from userbase u
join propertyentry pefn on pefn.entity_id = u.id and pefn.ENTITY_NAME = 'OSUser' and pefn.property_key = 'fullName'
join propertystring psfn on psfn.id = pefn.id

If you want the email address as well, do this:

select username, psfn.propertyvalue as full_name, psem.propertyvalue as email
from userbase u
join propertyentry pefn on pefn.entity_id = u.id and pefn.ENTITY_NAME = 'OSUser' and pefn.property_key = 'fullName'
join propertystring psfn on psfn.id = pefn.id
join propertyentry peem on peem.entity_id = u.id and peem.ENTITY_NAME = 'OSUser' and peem.property_key = 'email'
join propertystring psem on peem.id = psem.id

[mlassau_atlassian]

For DB schema see https://developer.atlassian.com/display/JIRADEV/Database+Schema

Note that the user table changed in v4.3, so if you are on v4.2 or earlier see http://confluence.atlassian.com/display/JIRA040/Database+Schema#DatabaseSchema-UsersandGroups

[Jani Jani]

I am looking to get list of inactive users from OSUser, like this (for confluence): http://alturl.com/3qov8