SQL Queries for JIRA

CAUTION: heavy queries (e.g. multiple joins) 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
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')

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