Automation - Sending an email containing multiple JQL search queries

[Liam Cass]

Good morning!

I am trying to create an automated email to send out meeting agendas every week. Each agenda that I make uses issues from five different JQL searches, and lists the results of each search in the email.

Currently, I am doing this manually, and the emails look like this:

-------------------------------------------------------------------
Good morning! Here are the topics we will look at today

[filter-one-name]
issue-result-1001    issue_summary_here   custom_field_value
issue-result-1002    issue_summary_here   custom_field_value

[filter-two-name]
issue-result-2001    issue_summary_here   custom_field_value

[filter-three-name]
issue-result-3001    issue_summary_here   custom_field_value
issue-result-3002    issue_summary_here   custom_field_value

I'll see you later!
-------------------------------------------------------------------
And eventually I was thinking about using HTML in the emails to make the columns prettier.

So far, I looked into using an automation rule to send an email, however I was only able to produce something like the below, using results from a single JQL search and the {{issues}} smart value.

-------------------------------------------------------------------
Good morning! Here are the topics we will look at today

[filter-one-name]
issue-result-1001
issue_summary_here
custom_field_value

issue-result-1002
issue_summary_here
custom_field_value

I'll see you later!
-------------------------------------------------------------------

I am not quite sure how to send an email and include information on more than one search query. I've also looked into using filter subscriptions as a way to compile and send filter results, though I don't know how to automatically compile more than one filter result into an email, AND to include a header and footer.

I was wondering if there was any way in out-of-the-box Jira to automate sending these emails?

If it is not possible for out-of-the-box Jira, I also have access to script runner, and would appreciate some tips on making a script for this automation!

[Bill Sheboy · Accepted solution]

Hi @Liam Cass  -- Welcome to the Atlassian Community!

A couple of ways to do this (including one you mentioned) are: a dashboard and automation rules.

Dashboard (pull approach): Create a dashboard listing/visualizing your different queries and manually send an email (or use an email scheduler/meeting invite) with a link to the dashboard for people to view.  A couple advantages of this approach are it is not limited to 100 issues, as are automation rules actions, and visualizations may help see aspects of the issues.

Automation rule (push approach):

• You are using Jira Cloud (correct?) and so you can use the Lookup Issues and Create Variable actions to help
• For each query, call Lookup Issues with JQL, and save the results in a created variable...adding any formatting (HTML, line breaks, etc.) you wish.  Automation rule actions are limited to 100 issues, so if you expect more this may not help you.
• Use the Send Email action to then reference the pre-formatted text from the created variables.

Kind regards,
Bill

[Liam Cass]

Hi Bill! Thank you for the help, though my instance does not seem to have access to the 'create variable' OR the 'Lookup Issues' actions. (I might not be on Jira Cloud, I'm not certain, which is perhaps why)

And as this email will occasionally be sent to people who do not have access to the dashboard I am pulling from, the dashboard would not be an ideal option.

Good news, It is extremely unlikely that this email will contain more than 100 issues. 

Currently, I am attempting to write a ScriptRunner script to send the email, though this is slow-moving. 

Would there be any way in 'Server Jira' to create those variables that I can use for an email automation action?

[Bill Sheboy]

Short answer, probably not for Jira Server/Data Center...

My understanding is Server/Data Center versions' automation rules do not support Lookup Issues or Created Variables yet.

The equivalent in Server/Data Center for Lookup Issues is the bulk handling feature of branches and scheduled triggers: https://confluence.atlassian.com/automation/bulk-handling-of-issues-993924653.html  That would not seem to help your use case as: you can only have one set of {{issues}} at a time, and there is no place to store intermediate results easily (like created variables)  A complicated work-around would be to store the intermediate results from a branch/JQL in a known issue's custom fields, repeat branches as needed, call the re-fetch action, and then use those custom fields in the email.  (I know...quite messy.)

Other work-arounds could be:

• buy a marketplace addon for reporting improvements
• use automation rules (or subscribed filters) to send yourself multiple emails, and use email tool rules to forward them to the recipients
• build something by calling the REST API and programming tools

Sorry I could not be of more help for your Jira version.

[Alex Osana]

Hi @Bill Sheboy ,

I am having a similar case (Jira cloud) where I wish to report via email issue group by status.

I have my querie ready but I am having trouble parsing the {{#lookupIssues}} or run different queries and store them in separate variables (variable do not accept {{#lookupIssues}} as valid value.

Any advice?

[Bill Sheboy]

Hi @Alex Osana 

First thing...in generally it is a good idea to create a new question and link back to older threads.  Otherwise only the people following this one will see it, thereby limiting the number of ideas suggested.  Thanks!

Okay, back to your question...

What is the trouble you are having using lookup issues?  For example, is it not filtering the list to the group  correctly for your email?

Would you please post images of your complete rule and the audit log details showing the rule execution?  Those may provide context for what is happening.

Kind regards,
Bill

[Alex Osana]

Hello @Bill Sheboy

Noted, you are right.

Ok, I am totally new to this, so bear with me if I miss something:

Case: I have (kanban) projet and I wish to inform some users by email on the status of issues in the Kanban every couple of days.

How I approached it: I created a lookup Issues action to select the issues I need. Then I used the {{#lookupIssues}} clause to structure every record in the email as show in the screenshot.

Request: My wish is to organize the results by status within the email (first Show all form Status A, the from Status B, etc). I tried to see if LookupIssues has properties (such as .status) but didn't work. Then I tried to do different lookupIssue actions and store them in a variable but don't seem to be able to store anything other than a smart value in the value field (while I was hoping to store the whole lookup result). 

What is your advice on the best approach?

Thanks in advance

[Bill Sheboy]

All of the fields should be present in the Lookup Issues result.

Did you take a look a the link I provided for list filtering?  Using that feature, you could filter the results of a single Lookup Issues result, and show the issues in groups, by status, if you want.

Also looking at the JQL for your lookup result, you could improve that using the IN statement, such as with this:

project = myProjectName AND issueType = Task AND status IN (BACKLOG, PREANALYSIS, SIZING, ANALYSIS) ORDER BY status ASC

[Alex Osana]

Hi @Bill Sheboy,

 
Order By seems to do the work just fine in my case. Tried filtering too but was redundant at the end of the day.

Many Thanks, much appreciated

[Ana Luiza Chagas]

Hi @Liam Cass,

Were you able to create an automatic email to send meeting agendas? I'm trying a similar implementation in my project, but so far I don't quite understand how I can approach this rule in Jira Automation.

Thanks!