nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to show all tickets with their status via automated mail

Radl Glück

Hello, I'm still new in the world of Jira automation. I wanted to create a rule that would give me an overview for all tickets in their different states and how many there are. And how many tickets each user has assigned Like this:

To Do: 4

In Progress: 6

Done: 12

User 1: 'x' number of tickets are assigned to you

User 2: -....-

I tried to make it work with "Create Variable". But I'm not able to figure it out.

Currently, I am only able to set this up for just one status per rule. But I want multiple statuses and user assign to apply to one rule for one email only. Please let me know if this is possible and how can I make it work. Thanks!

Accepted answers

Bill Sheboy

Hi @Radl Glück -- Welcome to the Atlassian Community!

Adding to the suggestions from @Jack Brickey ...

How many issues would you expect for these counts? Do you want to count all of the issues in the project, in a sprint, matching some specific JQL, etc.?

These questions are important as the Lookup Issues action can only return up to 100 issues maximum: https://confluence.atlassian.com/automation/jira-smart-values-issues-993924860.html#Jirasmartvaluesissues-lookupissueslookupIssues

Let us assume that 100 count is sufficient. You could call the Lookup Issues action repeatedly, with different JQL, and save each count with the Create Variable action for use later.

Or, you could perform a call to Lookup Issues one time, and then use list filtering for each count you need, with a math expression to count them up. This approach would likely execute faster as there is only one data base call.

For example, let us assume you want the issues in the current Sprint, and so the JQL for the Lookup Issues action would be:

project=yourProjectName AND sprint IN openSprints()

To get the count for the issues in the "To Do" status, that would be:

{{#=}}0{{#lookupIssues}}{{#if(equals(status.name, "To Do"))}}+1{{/}}{{/}}{{/}}

This works by:

iterating over the issues in the lookup
filtering for ones with a status name of "To Do", and adding +1 to the count when found
finally wrapping that in a math expression, with a default value of 0 at the front

The same approach to count the issues assigned to me would be:

{{#=}}0{{#lookupIssues}}{{#if(equals(assignee.displayName, "Bill"))}}+1{{/}}{{/}}{{/}}

Using this approach, no created variables are needed as each status and assignee count could be added directly to your email body.

Kind regards,
Bill

All comments

Jack Brickey

Hi @Radl Glück , not a direct answer to your question, but would like to offer a different solution than automation. Have you considered creating a dashboard and using a two dimensional gadget? This gives you the ability to have the same information at your fingertips whenever you want it. Maybe there is something about your used case that requires an email?

Radl Glück

Hello @Jack Brickey the thing is we want it via automation mail so that I can send the information to different teams aswell.

Jack Brickey

Ok. To do this in automation, you will need to perform a lookup issues action for each status and store that size of the result in a variable. You can use the size function on the lookup issues to determine the number returned in that function.

then simply reference the variables in the email, e.g.

To Do count = {{numToDo}}
Done count = {{numDone}}

etc...

Jack Brickey

If you run into issues LMK and I can give it a try in a bit. There are a number of posts similar to yours in the Community I think that can assist as well.

Bill Sheboy

Hi @Radl Glück -- Welcome to the Atlassian Community!

Adding to the suggestions from @Jack Brickey ...

How many issues would you expect for these counts? Do you want to count all of the issues in the project, in a sprint, matching some specific JQL, etc.?

Let us assume that 100 count is sufficient. You could call the Lookup Issues action repeatedly, with different JQL, and save each count with the Create Variable action for use later.

For example, let us assume you want the issues in the current Sprint, and so the JQL for the Lookup Issues action would be:

project=yourProjectName AND sprint IN openSprints()

To get the count for the issues in the "To Do" status, that would be:

{{#=}}0{{#lookupIssues}}{{#if(equals(status.name, "To Do"))}}+1{{/}}{{/}}{{/}}

This works by:

iterating over the issues in the lookup
filtering for ones with a status name of "To Do", and adding +1 to the count when found
finally wrapping that in a math expression, with a default value of 0 at the front

The same approach to count the issues assigned to me would be:

{{#=}}0{{#lookupIssues}}{{#if(equals(assignee.displayName, "Bill"))}}+1{{/}}{{/}}{{/}}

Using this approach, no created variables are needed as each status and assignee count could be added directly to your email body.

Kind regards,
Bill

Radl Glück

Hello @Bill Sheboy thank you for your detailed contribution.

For the question how many issues my JQL search will count: to an maximum approximately to 90 for every development cycle.

I really like the approach with just one Lookup Issues action.

I managed to make the automation work with your example. Below is now just an test for the automation.

Screenshot 2024-12-11 100933.png Screenshot 2024-12-11 101229.png

I say thank you for your awesome help.

Radl Glück

Hello @Bill Sheboy I have one more question regarding this issue. I made my automation up and running and my question is, if I could add an custom field in my list filtering? For clarification in our issues we have three different fields for assignee with different names. Is it possible to sum them up aswell?

I used this:

{{#=}}0{{#lookupIssues}}{{#if(equals(customfield_16500.value.displayName, "blah"))}}+1{{/}}{{/}}{{/}}

I still get a zero out.

Thank you for your help.

Bill Sheboy

Unfortunately, that is not possible for Jira Data Center's version of the Lookup Issues action as it only supports a limited number of fields.

There are two possible workarounds, depending upon rule structure:

use the bulk-handling feature of Scheduled triggers and branches with the plural {{issues}} smart value in place of {{lookupIssues}}
use the REST API endpoint to search issues with JQL using the Send Web Request action

For your case, you could use #1 as the rule has no other branching and does not use the results / issues to edit another issues.

To learn more about these methods, please see this article / message post:

https://community.atlassian.com/t5/Jira-articles/How-Can-I-Sum-Up-Multiple-Issues-into-a-Parent-Number-Field/bc-p/2883071#M16701

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-data-center

automation

data-center

jira-automation

mail

automation-list