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!

[Bill Sheboy · Accepted solution]

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.

• https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588
• https://confluence.atlassian.com/automation/jira-smart-values-math-expressions-993924866.html

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.

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:

1. use the bulk-handling feature of Scheduled triggers and branches with the plural {{issues}} smart value in place of {{lookupIssues}}
2. 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

[Radl Glück]

Hello @Bill Sheboy

I made it work and I thank you for your great input and help.

[Jack Brickey]

Hi @Radl Glück , can you share your final solution?

[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.

{{lookupissues.size|0}}

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.

[Radl Glück]

Hello @Jack Brickey thank you for your quick reply. The only thing is that I'm not sure how to store the information from my lookup issues action via a variable. Tried it but still failed.

[Jack Brickey]

Bill has provided some excellent input that should help you with this. Let us know and once you have it working please consider sharing your full automation rule via screenshots. This will help others.