Hi,
How can I get userlist (realnames/usernames) out of the Jira. I need to submit such forward to check for old employees etc.
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)
Yes and Yes
I just need a quick way to list users in some sensible form so I can get a decent list out.
I am looking to get list of inactive users from OSUser, like this (for confluence): http://alturl.com/3qov8
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
Jira in question is old 4.0 upgraded from 3.xx
it still old userbase+membershipbase etc.
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
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
It looks like you're new here. Sign in or register to get started.
