Retrieve reporters, participants and their email addresses from the database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
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
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.
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';
Last modified on Feb 2, 2024
Powered by Confluence and Scroll Viewport.