CAUTION: heavy queries could cause crashes!
...
| No Format |
|---|
UPDATE changeitem SET OldString = 'Set Text Here' where changeitem.id = 1819534 |
Return saved filters that contain the text In Post LC
| No Format |
|---|
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
| No Format |
|---|
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
...
| No Format |
|---|
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:
| No Format |
|---|
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
| No Format |
|---|
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
| No Format |
|---|
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
...
- 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 ,
| No Format |
|---|
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)
| No Format |
|---|
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)
| No Format |
|---|
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' |
...