2

I have a set of Epics that of which some do and some do not have a value for 'Epic Name'. The field is a required field, but it was not when these issues were created. I want to query all Epics for which Epic Name is not filled out.

What I have tried so far:

  1. These queries

    • type=Epic AND "Epic Name" = ""
    • type=Epic AND "Epic Name" = empty
    • type=Epic AND "Epic Name" is empty
    • type=Epic AND "Epic Name" = " "

  2. Unlocked the 'Epic Name' field in the database

Digging around in the database leads me to believe that these issues to not have an associated custom field 'Epic Name' at all, so it doesn't have a value of Null - it just doesn't exist.

How could I find my Epics that do not have an Epic Name associated?

jessehouwing
  • 106,458
  • 22
  • 256
  • 341
Spork
  • 1,631
  • 1
  • 21
  • 37
  • If you make Epic Name a valid field for all issues then you should be able to use: "Epic Name" is empty – mdoar May 13 '14 at 17:20
  • @mdoar Then I suppose it isn't a valid field for all my Epics because of the underlying reason: they do not all have an associated Epic Name – Spork May 14 '14 at 08:31

2 Answers2

2

The following query will show you all Epic-type issues in the database, along with their summaries and the associated epic name (if any):

SELECT p.pkey || '-' || ji.issuenum AS issueid, ji.summary AS issuesummary,
cfv.stringvalue AS epicname
FROM jiraissue ji
LEFT JOIN project p ON p.id=ji.project
LEFT JOIN customfieldvalue cfv ON cfv.customfield=(SELECT id FROM customfield
    WHERE cfname LIKE 'Epic Name') AND cfv.issue=ji.id
WHERE ji.issuetype=(SELECT id FROM issuetype WHERE pname LIKE 'Epic')
ORDER BY p.pkey, ji.issuenum;

If you're using JIRA 6.0 or earlier, you'll have to replace the first "p.pkey || ... as issueid" with "ji.pkey as issueid" and adjust the ORDER BY to match. If using JIRA 6.1+ but using MySQL, the first argument should instead become "concat(p.pkey,'-',ji.issuenum)".

I don't have any no-name epics in my system, so I have no way to test a query that returns only the nameless epics (as opposed to all of them)...but adding this to the WHERE clause should do it:

AND (CFV.stringvalue like '' OR CFV.stringvalue is null)
Scott Dudley
  • 3,256
  • 1
  • 18
  • 30
  • Works like a charm! I'll need to ask the off-site DB admin to run the query for me, but I'll be able to create a snipped of UPDATE sql using your SQL. Thanks! Awesome help – Spork May 13 '14 at 13:45
  • I'm glad it worked! But be careful if you're planning to do any UPDATE queries on the JIRA dB. At a bare minimum, you should shut down JIRA before running any such queries, and then you'll also have to reindex Lucene afterward once you restart JIRA...but it's really safer to do this by fixing the epic names by hand (if there aren't hundreds of them), or else using something like the [JIRA CLI](https://bobswift.atlassian.net/wiki/display/JCLI/JIRA+Command+Line+Interface) which does it through defined APIs which are guaranteed to be safe. – Scott Dudley May 13 '14 at 13:51
  • That sounds well-thought out. I have been reassured about the number of these epics, which are seemingly contained to the project I'm working with currently. I suppose the number of them is within the acceptable range to do it by hand, considering the re-index. – Spork May 13 '14 at 14:02
0

Works for me:

project = "some name" AND type = Epic and "Epic Name" is empty
ouflak
  • 2,458
  • 10
  • 44
  • 49
Brijesh Shetty
  • 27
  • 6