JQL query to find issues in multiple closed sprints without using add-ons

[meerm]

I am looking for a JQL query to find issues that were in more than one sprint. I know we can use below query:

Sprint = openSprints() and Sprint = closedSprints()

this get me the current open issues which are in open and previously closed sprint. This is not going to solve my problem. 

I need a query which finds issues which were in more one closed sprints. Basically I am looking for stories which spilled over to next sprints in last 6 months

[Bill Sheboy]

Hi @meerm 

Yes, this is possible.  The how-to depends on how frequently you need to do this and what you want to do with the information...

Need this one time: Create a filter to find your issues, export them to a spreadsheet, and use formulas or pivots to count them.  When issues are exported, a column is created for each sprint, so please take that into account.

Need this often: Discuss with your site admin about adding a custom field to count these occurrences and then add an automation rule triggered on Sprint Started to count the values and update the field.  If your admin will not allow you to add the field, you can use an entity property instead, created/updated in the rule.

Kind regards,
Bill

[meerm]

Hi @Bill Sheboy 

Thanks for the response. I need this often., can you give some examples on how and what to set entity property in this case.  

[Bill Sheboy]

An entity property can be set at various levels, including for the issue.  And automation rules can set a property using an action: https://support.atlassian.com/cloud-automation/docs/jira-automation-actions/#Set-entity-property

So let's say you add a property named sprintcount to the issue, and in the rule the value is set based on the number of sprints with this:

{{issue.sprint.size|0}}

That value would be the number of sprints noted in that field with a default of 0 when there are none.

You could later use JQL to query your entity property, as described here: https://developer.atlassian.com/cloud/jira/platform/jira-entity-properties/

[meerm]

Hi @Bill Sheboy

I have created an automation for assigning the entity value as "sprintcount".

Although the value is getting set and I am able to access the value in automation by populating it in comments or assigning through smart value, but when I am trying to access using JQL query as

issue.property[issuecount] > 1

I am not getting any value. Is there anything I am missing

[Bill Sheboy]

I note your JQL is referencing issuecount but your new entity property key is named sprintcount.

Even with that change, I also am not able to get this to work with JQL, and cannot find my earlier saved examples.  It appears I used the entity property manager (free marketplace addon) to originally get the field searchable.  Although that would open this up globally for changes due to a defect in the addon.

At this point, I recommend either:

• using the custom field or
• using a scheduled rule which generates a periodic report for issues in more than one sprint.  The same counting technique I noted earlier could be used with the Lookup Issues action to do that.

[Jehan Bhathena]

Hi @meerm ,
Below was an attempt I took at your use case,

"T2CM Sprint 1" is closed, while "T2CM Sprint 2" is open. Are you looking for the issues like the one I have highlighted or is it some other scenario that you are looking for.

[meerm]

Hi @Jehan Bhathena

Thanks for your response.

Yes I am getting the results using this query, but I also need to list issues having two closed sprints and not in open sprint.