CAUTION: heavy queries could cause crashes! BE CAREFUL.

Audience: JIRA admins and other users that have access to the JIRA production db.

This page contains a non-exhaustive list of SQL queries that can be done from JIRA. For other examples provided by Atlassian, see Information link below the list.

More examples: SQL queries for Jira from Atlassian

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

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:

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 screens where a 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'

SQL Queries for JIRA