Versions Compared

Version Old Version 36 New Version Current
Changes made by

Rafael Sanchez (Deactivated)

Amy Biasella (Deactivated)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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.

...

Info

More examples: SQL queries for Jira from Atlassian


Return contents of Instrument custom field (cascading select list)

Code Block
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)

Code Block
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

Code Block
languagesql
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)

Code Block
languagesql
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:
  • 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.
noformat
Code Block
language
sql
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')

...

Returns issues that have a Lucidchart diagram

noformat
Code Block
language
sql
selectSELECT distinctDISTINCT p.pkey || '-' || i.issuenum
fromFROM propertyentry pe
innerINNER joinJOIN fileattachment fa   	on pe.entity_id = fa.id
innerINNER joinJOIN jiraissue i
  			on fa.issueid = i.id
innerINNER joinJOIN project p   			on i.project = p.id
where
 WHERE pe.property_key = 'lucidchart.attachment.id';

...

Get the add-on key information from the Manage add-on administration page:

 


noformat
Code Block
language
sql
SELECT 
  	workflowscheme.name 		AS "Workflow Scheme",
  		workflowschemeentity.scheme,
  		issuetype.pname 			AS "Associated Issue Type",
  		workflowschemeentity.issuetype
FROM 
  	issuetype,
  		workflowschemeentity,
  		workflowscheme
WHERE 
issuetype.id = workflowschemeentity.issuetype and
workflowscheme.id = workflowschemeentity.scheme and
 
workflowschemeentity.workflow in
    (SELECT 
        jiraworkflows.workflowname
    FROM 
        jiraworkflows
    WHERE 
        jiraworkflows.descriptor like '%org.swift.jira.cot%');

...

Return custom fields and how often they're used (not null in issues)

noformat
Code Block
language
sql
SELECT 		CF.cfname, count(CV.ISSUE) 
FROM 		customfield CF 
joinJOIN 		customfieldvalue CV on CF.ID = CV.CUSTOMFIELD
groupGROUP byBY 	CF.cfname


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

Code Block
languagesql
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
languagesql
SELECT
    screen.id,
    screen.name,    
    layoutitem.fieldidentifier
    
FROM
    fieldscreenlayoutitem   layoutitem
    INNER JOIN fieldscreentab          tab ON layoutitem.fieldscreentab = tab.id
    innerINNER joinJOIN fieldscreen             screen onON tab.fieldscreen = screen.id
WHERE
    layoutitem.fieldidentifier IN  where fieldidentifier = 'priority('customfield_13978','customfield_13979')


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

Code Block
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)

Code Block
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

Code Block
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%'