SQL Queries for JIRA

CAUTION: heavy queries could cause crashes!

Lots of issue related SQL queries 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 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 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.
In Word, press Ctrl+H (find and replace). Find ^p and replace with ,

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 com.company.plugin information from the Manage add-on administration page:

SELECT 
  workflowscheme.name,
  workflowschemeentity.scheme,
  issuetype.pname,
  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%');