SQL Queries for JIRA

CAUTION: heavy queries could cause crashes! BE CAREFUL.

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

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