Retrieve reporters, participants and their email addresses from the database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
The following query will give you a list of reporters and their respective email addresses a specific project.
This was written in PostgreSQL syntax, so you may need to tweak it depending on the database you are using
1
2
3
4
5
select distinct cw.user_name as "Reporter", cw.lower_email_address as "EmailAddress"
from project p join jiraissue j on p.id = j.project
join app_user au on j.reporter = au.user_key
join cwd_user cw on au.lower_user_name = cw.lower_user_name
where p.pname = 'Project One';For a more detailed result, i.e. the reporter and their email addresses for each issues within the project
1
2
3
4
5
6
select j.id as "IssueID" , p.pkey||'-'||j.issuenum as "IssueKey", cw.user_name as "Reporter" , cw.lower_email_address as "EmailAddress"
from project p join jiraissue j on p.id = j.project
join app_user au on j.reporter = au.user_key
join cwd_user cw on au.lower_user_name = cw.lower_user_name
where p.pname = 'Project One'
order by j.id asc;The query below will return the list of participants and their email addresses for a specific Jira Service Management project.
1
2
3
4
5
6
7
select distinct cw.user_name as "Participants", cw.lower_email_address as "EmailAddress"
from customfieldvalue cv join customfield cf on cv.customfield = cf.id
join app_user au on cv.stringvalue = au.user_key
join cwd_user cw on au.lower_user_name = cw.lower_user_name
join jiraissue j on cv.issue = j.id
join project p on j.project = p.id
where cf.customfieldtypekey = 'com.atlassian.servicedesk:sd-request-participants' and p.pname = 'Project One';Was this helpful?