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

Amy Sliwinski May 7, 2024

Hello, 

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

For example: 

Screenshot 2024-05-07 at 7.50.41 PM.png

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.

Screenshot 2024-05-07 at 7.56.21 PM.png

 

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!

 

5 answers

1 accepted

1 vote
Answer accepted
Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 8, 2024

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:

Kind regards,
Bill

Amy Sliwinski May 8, 2024

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!

 

Screenshot 2024-05-08 at 4.17.26 PM.pngScreenshot 2024-05-08 at 4.19.08 PM.png

Screenshot 2024-05-08 at 4.20.09 PM.png

Amy Sliwinski May 8, 2024

And, yes, I am using Jira Data Center.

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 8, 2024

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 May 8, 2024

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.

 

Screenshot 2024-05-08 at 8.02.14 PM.pngScreenshot 2024-05-08 at 8.02.06 PM.pngScreenshot 2024-05-08 at 8.00.32 PM.png

 

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

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 9, 2024

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 May 22, 2024

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

Like Bill Sheboy likes this
0 votes
Victor - Modus Create May 28, 2024

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

0 votes
Heenaben Sardarbhai Chaudhary May 7, 2024

you can use something like below into JQL search

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

Amy Sliwinski May 8, 2024

Thanks for the suggestion, Heenaben! 

0 votes
Ajay _view26_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 7, 2024

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 May 8, 2024

Thanks for the suggestion, Ajay! 

0 votes
Alex Wills May 7, 2024

Hey Amy, 

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

Screenshot 2024-05-08 135626.png

Suggest an answer

Log in or Sign up to answer