...

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.

...

Return contents of Instrument custom field (cascading select list)

SELECT cfo1.customvalue "Parent Option",
cfo2.customvalue "Child Option"
FROM customfield cf, customfieldoption cfo1, customfieldoption cfo2
WHERE cfname = 'Instrument'
AND cf.id = cfo1.customfield AND cf.id = cfo2.customfield
AND cfo1.parentoptionid is null
AND cfo2.parentoptionid = cfo1.id

Return all instrument tickets with their summary and instrument name field (JiraDWH was designed for this but here is the raw SQL)

SELECT p.pkey||'-'||j.issuenum as Key,summary,cfv.stringvalue AS "Instrument Name"
FROM jiraissue j
join issuetype i on j.issuetype = i.id
join customfieldvalue cfv on cfv.issue = j.id
join customfield c on cfv.customfield = c.id
join project p on j.project = p.id
where i.pname = 'Instrument'
and c.cfname = 'Instrument Name'

Return changeitems (including before and after values, as well as modification date) for issue TEST-106

SELECT
    p.pkey||'-'||i.issuenum 				as "KEY",
    ci.field,
    cg.created 								as Modified,
    dbms_lob.substr(ci.oldstring, 4000, 1) 	as OLD_String, 
    dbms_lob.substr(ci.newstring, 4000, 1) 	as NEW_String,
    dbms_lob.substr(ci.oldvalue, 4000, 1) 	as OLD_Value,
    dbms_lob.substr(ci.newvalue, 4000, 1) 	as NEW_Value,
	ci.id 									as Change_Item_Id
FROM changeitem ci
JOIN changegroup cg 						on ci.groupid = cg.id
JOIN jiraissue i 							on cg.issueid = i.id
JOIN project p 								on i.project = p.id
WHERE p.pkey||'-'||I.issuenum in ('TEST-106')

... And to update an item that gets returned (e.g. to redact PHI)

UPDATE changeitem 
SET OldString = 'Set Text Here'
where changeitem.id = 1819534

...

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 		CF.cfname, count(CV.ISSUE) 
FROM 		customfield CF 
JOIN 		customfieldvalue CV on CF.ID = CV.CUSTOMFIELD
GROUP BY 	CF.cfname

Return remote links (other Jiras,Confluence,Websites) and what page they're on

SELECT p.pkey||'-'||j.issuenum as Key, a.title, a.url,
       a.iconurl, a.icontitle, a.relationship, a.applicationtype, a.applicationname
  FROM remotelink a
  JOIN jiraissue j on a.issueid=j.id
  JOIN project p on j.project = p.id

Return screens where

...

a given field is used

...

Code Block
language sql
SELECT screen.id, screen.name, layoutitem.fieldidentifier FROM fieldscreenlayoutitem layoutitem layoutItem INNER JOIN fieldscreentab tab onON layoutItemlayoutitem.fieldscreentab = tab.id INNER JOIN fieldscreen screen onON tab.fieldscreen = screen.id WHERE layoutitem.fieldidentifier IN ('customfield_13978','customfield_13979')

Return saved filters that have a given field set as a column

SELECT SR.filtername, CLI.fieldidentifier 
FROM columnlayoutitem    CLI
INNER JOIN columnlayout  CL  ON CLI.columnlayout = CL.id
INNER JOIN searchrequest SR  ON CL.searchrequest = SR.id
WHERE CLI.fieldidentifier IN ('summary','customfield_16165'); --'supervisor' custom field

Return custom fields using a certain type key (e.g. finding Broad custom fields)

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 = 'priority p.id
where 
a.actionbody like '%Tableau-sap%' 
OR j.summary like '%Tableau-sap%' 
OR j.description like '%Tableau-sap%'