...

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.

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

...

No Format
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

...

No Format
select * from searchrequest where id not in (select entityid from sharepermissions where entitytype = 'SearchRequest')

Return private dashboards

...

No Format
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:

...

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

...

No Format
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. 

...

No Format

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'

...