...

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.

...

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')

...

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 screens where a given SYSTEM field is used (not for custom fields)

SELECT screen.name 
FROM fieldscreenlayoutitem layoutItem 
INNER JOIN fieldscreentab tab 	on layoutItem.fieldscreentab = tab.id
  INNER JOIN fieldscreen screen on tab.fieldscreen = screen.id
  WHERE fieldidentifier = 'priority'

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

SELECT
    screen.id,
    screen.name,    
    layoutitem.fieldidentifier
    
FROM
    fieldscreenlayoutitem   layoutitem
    INNER JOIN fieldscreentab          tab ON layoutitem.fieldscreentab = tab.id
    INNER JOIN fieldscreen             screen ON 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 = p.id
where 
a.actionbody like '%Tableau-sap%' 
OR j.summary like '%Tableau-sap%' 
OR j.description like '%Tableau-sap%'