...

Audience: JIRA admins and other users that have access to the an on-prem JIRA production db.

This page contains a non-exhaustive list of SQL queries that can be done from JIRAfor an on-prem Jira instance. For other examples provided by Atlassian, see Information link below the list.

...

To create a comma separated list from an excel column e.g. if you have a bunch of JIRA records:

• New way to convert to comma separated list:
  • Use this google sheet and just paste in your list of issues! https://docs.google.com/spreadsheets/d/1rd9xpSlvnHIUKOQMMv_S3CIqYz7G4Upzekynqfbyp0Y/edit#gid=0
• Old :
  • Copy the desired column out of excel and paste directly into Word (text only).
  • To paste back in JIRA: In Word, press Ctrl+H (find and replace). Find ^p and replace with ,
  • To paste in a SQL query (below):In Word, press Ctrl+H (find and replace). Find ^p and replace with ',' . Do a tiny bit of fudging with the start and end of the string.

SELECT 	p.pkey||'-'||I.issuenum as "KEY",
		summary,
		created,
		updated,
		reporter,
		creator
FROM JIRAISSUE i
JOIN project p ON I.project=p.id
WHERE 	p.pkey||'-'||I.issuenum in 
('PICO-5910','KIT-98')

...

SELECT
    *
FROM
    customfield
WHERE
    customfield.customfieldtypekey LIKE 'org.broadinstitute%'
ORDER BY
    customfield.customfieldtypekey

Return comments, summary, descriptions containing certain text 

text e.g. tableau-sap

select p.pkey||'-'||j.issuenum as key,a.actionbody,j.summary,j.description
from jiraaction a
join jiraissue j on a.issueid = j.id
join project p on j.project = p.id
where 
a.actionbody like '%Tableau-sap%' 
OR j.summary like '%Tableau-sap%' 
OR j.description like '%Tableau-sap%'