How can I write a JQL query to find issues closed during a sprint, but not in the sprint

[Chris Hamilton]

During a sprint, it's not uncommon to create new issues, and resolve them as part of the sprint work.  But it's up to the user who creates the issue to remember to add it to the current sprint (since the effort affects the scope of the current sprint).  In order to catch cases where new tickets are created but the user forgets to add them, I need to find a simple way to query for them.   

What I'd imagine is a query that might look like:

  project = MyProj AND status = Closed and sprint not in ("CurrentSprint") AND closedDate > startDateOf("CurrentSprint") AND closedDate < endDateOf("CurrentSprint")

... or something along those lines.  What I'm not able to find right now is any way to expose sprint start or end dates, which I might then use to narrow my issue search.

Note that I don't want to just search for "sprint is EMPTY" across the entire project, because there are a ton of old tickets that were handled before we started using a scrum method.  There may be other reasons we want to ignore some tickets not being in the sprint as well-- the point of the query is to trigger a closer look, just to catch mistakes.  If it matches too much, then we aren't looking at potential mistakes anymore, and the query isn't useful.

[Bill Sheboy · Accepted solution]

Hi @Chris Hamilton -- Welcome to the Atlassian Community!

I am observing two things you may want to resolve:

1. visibility to issues completed which were missed/added after sprint planning; and
2. process improvement for sprint planning to reduce the number of these items. 

#2 sounds like a conversation with the team, together deciding what to do.

For #1, how about a gentle reminder to the person moving an issue to "done" when the sprint is not set?  You could do this with Automation for Jira (A4J).

Jira Cloud has built-in automation rules which would allow you to detect this condition and send the reminder.  You could also tag the issue to help with reporting, say as an input for a team retrospective.  For example:

• trigger: issue transitioned to your done status
• condition: sprint is not in the current sprint, or is empty
• action: set some flag, label, etc. to help with reporting
• action: ping the person who did this {{initiator}} to remind them to check the sprint field.  You can do this with MS Teams, Slack, email.

Server/Data Center version also has automation as an addon, with a free (Lite) version or a paid (Pro) one.  Either should work to do this.

Best regards,
Bill

[Chris Hamilton]

Automation is a very helpful suggestion!  Thanks, Bill.   I think that will certainly help to gently remind folks to check this, and that may, over time, help reinforce better habits.  However, mistakes will still be made.  What Automation does not do is give me (as a Scrum Master) the ability to quickly and easily identify tickets that might have fallen victim to this mistake, which is still necessary because folks may not pay attention to the email warning from A4J.

You also suggested setting a label as one option to track these issues -- that would certainly give me a quick query that is real-time updated.  But here also, I have a little concern.  I've seen people clone issues many many times, and completely ignore the labels that are on the source issue they cloned from-- so their newly-cloned issues carry wrong labels.  I've also seen folks go into "clean-up" mode, and just discard labels they don't recognize from their tickets.  (I've even done that myself, I'm ashamed to say.)  Over time, because of the propensity folks have to fail to maintain labels, I've come to view their use as a very short-term tool with a rapidly decaying signal-to-noise ratio.

The idea solution in my mind is to be able to access start/end date properties of a sprint in JQL.  But I can make do with a combination of labels, automation, and a sliding time window.

[Bill Sheboy]

Thanks for clarifying, Chris.

I picked labels as that is easy to use (with no setup like a custom field).  You and the team could pick another notification option as desired.

Regarding your visibility to the issue as scrum master, you could copy yourself in on automation emails, or just use a daily subscription on a saved filter, checking for issues resolved today which are not in the current sprint.  You may want to add criteria to exclude issues the team has "abandoned".

project = myProject
 AND (sprint IS EMPTY OR sprint NOT IN openSprints())
 AND resolved >= startOfDay()
 AND resolved <= endOfDay()

Re-inforcement is helpful to lead to team discussion: why is this happening, and what problem is it solving? 

Another approach some in the community have used is a different type of automation rule: if an issue starts work/moves to done during an active sprint and it isn't already in scope, add it to the sprint scope.  That would then appear on board and on the sprint report and provide data to inform the daily scrum and later retrospective.

[Chris Hamilton]

Philosophically, I like the idea of an automation rule to add closed issues to the active sprint.  That's ideally what we are going for.  However, if my understanding of sprints is correct, it might be a little heavy-handed to do that... Am I correct in understanding that:

1. An issue can only belong to one active sprint (ignoring the idea of parallel sprints, which I can't enable), and
2. Once an issue is in an active sprint, it is *forever* associated with that sprint?

If those two things are correct, then I don't ever want to automate adding an issue to an active sprint, because that leaves permanent scars if someone makes a mistake (wrong component, in our case, since our sprints are done at component level, not at project level).

[Bill Sheboy]

Yup, I agree...one person's heavy-handed is another's ease-of-use to save work.  I was offering another idea; I personally would not do it as it conceals the symptom.

I believe both of your things (one active sprint, and immutable sprint history) are correct.  (I have not tried to add the same issue to multiple sprints when that issue is visible on multiple scrum boards in different projects.)

Given your concerns, perhaps try the notification as an experiment and see what changes/improves.

[Nir Haimov]

Hi @Chris Hamilton 

You can use:

status changed to "done" after 2021-07-01 before 2021-07-30

[Chris Hamilton]

Thank, Nir.  But that assumes the current spring started on 07/01 and ends on 07/30.  I'll have to manually revise that query for each and every sprint, which seems redundant (and error-prone) to me.