How to get list of all users in a project

Question

I have a project from which I need to retrieve the list of distinct users that are reporters or assignees. I saw some answers about a REST API that returns the users in a project role (e.g. jira-users) but I'm not sure it will work for me as this is an old project so there are many reporters and assignees who are no longer in the company, so maybe they're not in  the jira-users group anymore.

I can easily do this:

1. Run a Jira issue search, select these 2 columns and export to a CSV.

2. Remove all duplicate users - either by an Excel function in the CSV, or by a simple Python script run on the CSV.

However, I wonder whether it's possible to go straight to the horse's mouth, meaning run an SQL snippet on the Jira database, something like this (very pseudo-SQL):

select distinct assignee from my_issue where (my_issue = select issue from all_jira_projects where project = 'MyProject']

Thanks

Amir Katz (Outseer) · Answer

You must have missed the first part of my question where I listed the CSV route as the first option...

Also, a downside of the CSV method is that you are limited to exporting up to 1000 results at a time (this is a Jira hard limit).

My project has ~8100 issues, so I will have to run 9 subsequent queries (key >= foo-1 and key < foo-1000, etc.), merge all 9 CSV files together and then remove the duplicates.

I think this explains why I'm looking for a direct SQL query which does not have a limit on the number of results.

Also, it's a one-time job and no one else needs access to it.

Brant Schroeder · Answer

Why don't you just use JQL and get the list then export to excel and remove duplicates to get your distinct users?  This will be easier then API or SQL calls and everyone has access to it.