Find Initiatives Without Epics

[Mathew Lederman]

Simple question: Can somebody help me with a query to find all initiatives without epics? 

Assumptions:

• Jira Cloud
• Hierarchy: Initiative > Epic > Story/Task > Sub-Task
• SriptRunner is available, but I'd prefer not to use it if possible

What I've tried, perhaps incorrectly:

• issueLinkType is empty
• portfolioChildIssuesOf
• childIssuesOf

[Trudy Claspill]

Hello @Mathew Lederman 

Jira does not provide a native Jira JQL function that will get you what you want.

What do you want to do with the results?

How many Initiatives do you have overall?

Are the Initiatives all stored in one project or in multiple projects?

Are the child issues of Initiatives, that would be of interest, all in one project or in multiple projects?

One solution that doesn't involve ScriptRunner would be to use an Automation Rule. 

You could create a Scheduled rule that uses a JQL to select all Initiative type issues. Then use a Lookup Issues to execute a JQL to find the child issues of each Initiative

issuekey in portfolioChildIssuesOf("{{issue.key}}")

Then have a Smart value Condition to check the count of issues found:

First value: {{lookupIssues.size|0}}
Condition: equals
Second value: 0

And if that condition is true, use the Send Email action to send an email to somebody with content stating the Initiative {{issue.key}} does not have any child issues.

Another solution would be to add a custom field to the Initiative issue type to hold the count of child issues. Then use an Automation Rule to update that field as child issues are added and removed from each initiative. Then you could use JQL to find all Initiatives where that field is 0.

For a ScriptRunner solution you would use this:

 issueFunction not in parentsOf("issuetype in (Epic)") and issuetype=Initiative

Where I have specified "issuetype in (Epic)" you would include all issue types that can be direct children of Initiatives.