...
- 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.
| No Formatcode |
|---|
|
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
| Code Block |
|---|
|
noformat |
selectSELECT distinctDISTINCT p.pkey || '-' || i.issuenum
fromFROM propertyentry pe
innerINNER joinJOIN fileattachment fa on pe.entity_id = fa.id
innerINNER joinJOIN jiraissue i
on fa.issueid = i.id
innerINNER joinJOIN project p on i.project = p.id
where
WHERE pe.property_key = 'lucidchart.attachment.id'; |
...
Get the add-on key information from the Manage add-on administration page:
| No Formatcode |
|---|
|
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)
| Code Block |
|---|
|
noformat |
SELECT CF.cfname, count(CV.ISSUE)
FROM customfield CF
joinJOIN customfieldvalue CV on CF.ID = CV.CUSTOMFIELD
groupGROUP byBY CF.cfname |
Return screens where a SYSTEM field is used (not for custom fields)
| No Format |
|---|
select| Code Block |
|---|
|
SELECT screen.name from
FROM fieldscreenlayoutitem layoutItem
innerINNER joinJOIN fieldscreentab tab on layoutItem.fieldscreentab = tab.id
innerINNER joinJOIN fieldscreen screen on tab.fieldscreen = screen.id
whereWHERE fieldidentifier = 'priority' |
...