Jira Automation to Send a Report of Issues That Have Been in 3 or More Sprints

[Amy Sliwinski]

Hello, 

Sometimes, our team has tickets that carry over from one sprint to the next.

For example: 

My Product Owner is asking if there is a way to create an Automation that would send her a list or report of issue type: Story tickets that have been in 3 or more sprints, so we can try to proactively target finishing the last bits of work needed in order to be able to close them. 

Is anyone aware of a way to do this? 

I think I've found a way to set up the "When" and "If" portions of the rule, but am unsure of how to set up the email action to include some kind of a report of the search results.

If the automation is not possible, I suppose I could just provide her with a Saved Filter, but an automated report email would be awesome!

[Bill Sheboy · Accepted solution]

UPDATE as of 2024-May: the solution with the Lookup Issues action will not work with Jira Data Center as the Sprint field is not supported in the results yet: link to suggestion to add the field.  Please see the bottom of this thread for other solution approaches.

Hi @Amy Sliwinski 

You are using Jira Data Center, correct?

If so, you can do what you ask using the Lookup Issues action and smart value, list filtering...assuming you are only looking at 100 issues, or fewer.  For example:

• trigger: some trigger...let's assume scheduled without JQL
• action: lookup issues with JQL to find issues in closed sprints

project = yourProjectName AND sprint IN closedSprints()

• action: send an email with the list of issues in 3 or more sprints

Issues in multiple sprints:
{{#lookupIssues}}
  {{#if(sprint.join("~").split("~").size.gt(2))}}* {{key}} -- {{summary}}{{/}}
{{/}}

How that works...

1. The sprint field is a list, but does not always act like one in rules, particularly inside of an iterator
2. And so we force the sprint field to join the values with a known delimiter, split them into a list, and count the entries with size
3. When there are greater than 2 entries, we return the key and summary fields
4. All of that is repeated as it is inside of the lookup issues iterator

Please look here to learn more about those action / rule features:

• https://confluence.atlassian.com/automation/jira-automation-actions-993924834.html#Jiraautomationactions-lookup-issuesLookupissues
• https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588

Kind regards,
Bill

[Amy Sliwinski]

Bill, thank you SO much for your help! You got me a lot closer than I was before!

I'm having some challenges with my smart values in my email body though. It is not generating any content in the body of the email, unfortunately. 

Any chance you see what I am doing wrong? Thanks again!

[Amy Sliwinski]

And, yes, I am using Jira Data Center.

[Bill Sheboy]

Your expression is missing the pound signs before lookupIssues and before the if() expression.

Please review my earlier post, add the missing characters, and then re-test.  Thanks!

[Amy Sliwinski]

Thanks, Bill. Sorry, I should have explained that I tried that first.

When I add the pound signs, the audit log says the rule was executed successfully - but the email message comes through blank.

Any ideas on how to fix this issue? Thank you for all of your help so far!

[Bill Sheboy]

Sorry, for my mistake: you are using Jira Data Center, and the lookup issues action does not yet contain the sprint field (which I find surprising).  Here is the suggestion to add the other fields to lookup issues: https://jira.atlassian.com/browse/JIRAAUTOSERVER-877

The only fields currently supported are shown here: https://confluence.atlassian.com/automation/jira-smart-values-issues-993924860.html#Jirasmartvaluesissues-lookupissueslookupIssues

For workarounds...

1. You are only using the values to write an email.  And so instead of lookup issues, you may use a branch on JQL with the bulk-handling option of branches.  With that, replace {{#lookupIssues}} with {{#issues}} (note that smart value is plural) https://confluence.atlassian.com/automation/run-a-rule-against-issues-in-bulk-993924653.html
2. If you were instead trying to use the data to edit an issue (e.g., summing a field) the other technique is to call the REST API function for issue search with the Send Web Request action.

Please try the first workaround and let me know what happens.

[Amy Sliwinski]

Thank you, Bill! I was able to use the branch JQL as a way to make this work. I appreciate your help!

[Victor - Modus Create]

Hi @Amy Sliwinski ,

In case this is helpful to other users, and based on my experience, you can find what you're trying to achieve using Notification Assistant for Jira Cloud.

- https://marketplace.atlassian.com/apps/1211069/notification-assistant-for-jira-email?hosting=cloud&tab=overview

Hope it helps.
Cheers,
Victor

[Heenaben Sardarbhai Chaudhary]

you can use something like below into JQL search

issueType = Story AND resolution = Unresolved AND Sprint in closedSprints() AND sprint in openSprints()

[Amy Sliwinski]

Thanks for the suggestion, Heenaben! 

[Ajay _view26_]

Hi @Amy Sliwinski 

You could use the Jira JQL Filter subscription feature for your use-case. The only downside is that you would need to update the JQL every-time you create a new Sprint.

Cheers

Ajay

[Amy Sliwinski]

Thanks for the suggestion, Ajay! 

[Alex Wills]

Hey Amy, 

This is tough as the closedSprints() field doesn't support anything other than "IN , NOT IN" as you can see here.