Versions Compared

Version Old Version 34 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
selectSELECT
    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
fromFROM changeitem ci
joinJOIN changegroup cg 						on ci.groupid = cg.id
joinJOIN jiraissue i 							on cg.issueid = i.id
joinJOIN project p 								on i.project = p.id
whereWHERE 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

...

OR who has dashboard with id 12272 as a favorite

No Formatcode
languagesql
SELECT 	f.username, 
		p.pagename as Dashboard, 
		p.id
FROM favouriteassociations f
JOIN portalpage p ON f.entityid = p.id 
WHERE f.entitytype = 'PortalPage' 
andAND p.pagename = 'QTP Overview'
--andAND f.entityid = 12272


Return who has 'MyFilter' or filter id of 10001 saved as a favorite:

noformat
Code Block
language
sql
SELECT 	f.username, 
		sr.filtername, 
		sr.id
FROM favouriteassociations f
JOIN searchrequest sr ON f.entityid = sr.id 
WHERE f.entitytype = 'SearchRequest' 
andAND sr.filtername = 'MyFilter'
--andAND f.entityid = 10001


Return a user's (biasella) favorite filters along with the JQL behind the filter

No Formatcode
languagesql
SELECT 	si.filtername, 
		dbms_lob.substr( si.reqcontent, 4000, 1 ) as JQL
FROM favouriteassociations fa
JOIN searchrequest si ON fa.entityid = si.id
WHERE fa.username = 'biasella';

...

Return filter name and JQL given a filter id e.g. filter=15191

noformat
Code Block
language
sql
SELECT 	si.id, 
		si.filtername, 
		dbms_lob.substr( si.reqcontent, 4000, 1 ) as JQL
FROM favouriteassociations fa
JOIN searchrequest si ON fa.entityid = si.id
WHERE si.id = 15191


Return private filters

Code Blocknoformat
languagesql
selectSELECT * 
fromFROM searchrequest
whereWHERE id not in 
    (selectSELECT entityid 
    fromFROM sharepermissions 
    whereWHERE entitytype = 'SearchRequest')

...

Return private dashboards

select
No Format
Code Block
languagesql
SELECT * 
fromFROM portalpage
whereWHERE id not in 
    (selectSELECT entityid 
    fromFROM sharepermissions 
    whereWHERE entitytype='PortalPage')


Return information from jiraissue table given a list of quoted comma separated issue keys 

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

select distinct
No Format
Code Block
languagesql
SELECT DISTINCT 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:

 


Code Blocknoformat
languagesql
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
  inner  INNER joinJOIN 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

Code Block
SELECT  where fieldidentifier = 'prioritySR.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%'