CAUTION: heavy queries could cause crashes! BE CAREFUL.

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

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

More examples: SQL queries for Jira from Atlassian

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

Return saved filters that contain the text In Post LC

SELECT 	a.id, 
		a.filtername, 
		dbms_lob.substr(a.reqcontent, 4000, 1 ) as JQL, 
		a.authorname
FROM searchrequest a
WHERE dbms_lob.substr(a.reqcontent, 4000, 1 ) like '%In Post LC%'

Return agile boards that have a swimlane with JQL query containing Nexome

SELECT 	a.name 				as Swimlane_Name, 
		r.name 				as Board_Name, 
		r.owner_user_name 	as Owner, 
		a.query
FROM ao_60db71_swimlane a
JOIN ao_60db71_rapidview r on a.rapid_view_id = r.id 
WHERE a.query LIKE '%Nexome%'

Return who has dashboard QTP Overview as a favorite

OR who has dashboard with id 12272 as a favorite

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

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

SELECT 	f.username, 
		sr.filtername, 
		sr.id
FROM favouriteassociations f
JOIN searchrequest sr ON f.entityid = sr.id 
WHERE f.entitytype = 'SearchRequest' 
AND sr.filtername = 'MyFilter'
--AND f.entityid = 10001

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

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

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

SELECT * 
FROM searchrequest
WHERE id not in 
    (SELECT entityid 
    FROM sharepermissions 
    WHERE entitytype = 'SearchRequest')

Return private dashboards

SELECT * 
FROM portalpage
WHERE id not in 
    (SELECT entityid 
    FROM sharepermissions 
    WHERE 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:
- 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')

Returns issues that have a Lucidchart diagram

SELECT DISTINCT p.pkey || '-' || i.issuenum
FROM propertyentry pe
INNER JOIN fileattachment fa 	on pe.entity_id = fa.id
INNER JOIN jiraissue i			on fa.issueid = i.id
INNER JOIN project p 			on i.project = p.id
WHERE pe.property_key = 'lucidchart.attachment.id';

Finding a certain add on used in workflows

e.g. Bob Swift's Create on Transition add-on.

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

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)

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

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

Analytics

SQL Queries for JIRA