JQL Query to find Stories with More than one Fix Versions that start with LCUS

[Brian Lehmbeck]

I have a project where I am categorizing work into milestones using Fix Versions.

The below are the Fix Versions I have created:

LCUS Phase 1: 30-Jun
LCUS Phase 2: 01-Aug
LCUS Phase 3: 25-Aug
LCUS Phase 4: 22 Sep
LCUS Backlog

I would like to create a JQL query that returns a list of issue type of Story or Task that have more that one Fix Versions that start with LCUS.

Not sure how to do this and would appreciate any help.

[Sagar Mahajan · Accepted solution]

Hi @Brian Lehmbeck With the built in available JQL functions you will not get a issues with more than 1 Fix versions and you will need a plugin to get that like https://marketplace.atlassian.com/apps/1214791/jql-search-extensions-for-jira-the-jql-extensions?tab=overview&hosting=cloud

This provide the function as

issue in fixVersionCountGreaterThan("1")

Or if you fix version list is limited then you can try the combination query like

project = ABC AND fixVersion = (LCUS Phase 1: 30-Jun, LCUS Phase 2: 01-Aug) or fixVersion = (LCUS Phase 1: 30-Jun, LCUS Phase 3: 25-Aug) or fixVersion = (LCUS Phase 1: 30-Jun, LCUS Phase 4: 22 Sep) AND fixVersion ~ "LCUS*"

You can add more combinations to it to get detail result.

Thanks

Sagar

[Brian Lehmbeck]

Thanks Sagar - 

Using JQL Search Extensions and using “fixVersionsCount > 1” as a condition I used your idea above (with some modification) and created the below query as a filter.

project in ("ABC") and issuekey in portfolioChildIssuesOf(ABC-50) and issuetype in (Story, Task) and status != Cancelled AND fixVersionsCount > 1 AND (fixVersion = "LCUS Phase 1: 30-Jun" AND fixVersion = "LCUS Phase 2: 01-Aug") Or (fixVersion = "LCUS Phase 1: 30-Jun" AND fixVersion = "LCUS Phase 3: 25-Aug") Or (fixVersion = "LCUS Phase 1: 30-Jun" AND fixVersion = "LCUS Phase 4: 22 Sep") Or (fixVersion = "LCUS Phase 1: 30-Jun" AND fixVersion = "LCUS Backlog") Or (fixVersion = "LCUS Phase 2: 01-Aug" AND fixVersion = "LCUS Phase 3: 25-Aug") Or (fixVersion = "LCUS Phase 2: 01-Aug" AND fixVersion = "LCUS Phase 4: 22 Sep") Or (fixVersion = "LCUS Phase 2: 01-Aug" AND fixVersion = "LCUS Backlog") Or (fixVersion = "LCUS Phase 3: 25-Aug" AND fixVersion = "LCUS Phase 4: 22 Sep") Or (fixVersion = "LCUS Phase 3: 25-Aug" AND fixVersion = "LCUS Backlog") Or (fixVersion = "LCUS Phase 4: 22 Sep" AND fixVersion = "LCUS Backlog")

Thanks for your help!

Brian

[Hannes Obweger - JXL for Jira]

Hi @Brian Lehmbeck,

if you are open to solutions from the Atlassian Marketplace, this would now be easy to do using the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a number of advanced features, including support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting - as well as the ability to filter issues based on regular expressions. 

With regular-expression-based filtering, you can apply pretty much any logic, including the one that you are after:

Once you've narrowed down your list of issues, you can work on your issues directly in JXL, trigger various operations in Jira, or export your issues with just one click.

Any questions just let me know,

Best,

Hannes 

[Lily Wang]

how can I find the filter? because I am not admin?

[mauricio.groth]

Hi @Brian Lehmbeck

I’m Maurício, a support engineer at Appfire and I’m here to help you.

Unfortunately, using JQL of Jira, you’ll not be able to do it dynamically.

In the app where my team works, JQL Search Extensions for Jira, you can use this query to find all tickets that have more than one Fix Versions that start with LCUS:

fixVersionsCount > 1 AND fixVersion ~ "LCUS*" AND issuetype in (Story,Task)

Please contact our support if you have any other questions about this query.

We’ll be happy to help you!

Best regards,

Maurício

[Bill Sheboy]

Hi @Brian Lehmbeck 

A query like this is not possible with the out-of-the-box JQL features.

How often do you need to do a query like this, and how many issues do you think meet this criteria?

• need just one time
  • You could search for issues which contain that starting text, and then export the issues to refine the search in a spreadsheet
  • Or, you could create an automation rule to identify these
• need often
  • You could investigate the Atlassian Marketplace for JQL addons which have this type of search feature

Kind regards,
Bill

[Brian Lehmbeck]

I am going to use this as a filter in a Jira Structure so will be built into a Dashbaord utilized daily.