Iterate through JQL result of issues, extract certain fields, and send a formatted Slack message

[Kristján Geir Mathiesen]

Hi all

I'm over my head with this ask, that I got last week. Quite specific:

- Every Friday run a JQL query to list up open "Blockers" (scheduled automation rule can do that)

- For each issue, extract the key, Assignee, and Summary

- Send a single Slack message to a shared channel with these 3 fields (1 line per issue) and also tag the Assignee in that line

I've messed around with {{lookupIssues}} but not found a way to format it. Let alone match the Atlassian user ID with Slack user ID...

The Slack message should look something like:

===

Outstanding blockers:

ABC-100 Jane Doe (Slack tagged) Improve the existing API
ABC-101 John Doe (Slack tagged) Finish the new brochure

===

Any ideas?

Takk,
KGM

[Bill Sheboy · Accepted solution]

Hi @Kristján Geir Mathiesen 

There are two parts to this one: the Slack user ID and the issue field listing.

How many users do you have for Jira and Slack to map to one another? 

1. When you have just a few, you could use a Lookup Table to map them, using the Jira user's account ID as the key and the Slack ID as the value.  Or, use a long chain of replace() functions.
2. When you have many users, it may be better to use a separate process to add an entity property for each Jira user, and look them up.  This article describes the method to bulk add them: https://community.atlassian.com/t5/Automation-articles/Mention-someone-directly-in-Slack-via-Jira-Automation/ba-p/1528605

That second approach is better for the next step: listing the fields.  The reason is when iterating over the data in the Lookup Issues result, outside data is not visible.  And if the Slack ID is already present for the Assignee data, they can easily be mentioned.

I'm not using Slack a lot currently, so I recommend looking at the necessary formatting in their information.  Perhaps something like this:

{{#lookupIssues}}
* {{key}} - {{summary}} assigned to {{assignee.displayName}} <@{{assignee.properties.metadata.slack_id}}>
{{/}}

Kind regards,
Bill

[Kristján Geir Mathiesen]

Takk, @Bill Sheboy ! Love it! I didn't know how to list them up with more than just the issue key.

I think we might only have a few users so I'll look into the Lookup Table

Thanks a million!
KGM

[Kristján Geir Mathiesen]

Sorry, I am not able to access the "Value" in the table (and as you can see in the pictures, I've simplified the value and the Slack message to try to troubleshoot).

What am I doing wrong, @Bill Sheboy ?

Takk, KGM

[Kristján Geir Mathiesen]

I can access the table with

{{nafn.entries}}

and print the results in the Slack message but somehow it doesn't work in the loop/iteration.

Takk again, KGM

[Bill Sheboy]

Hi @Kristján Geir Mathiesen 

My apologies I was not more clear in my original post!  Here is more context to help...

Once inside an iterator (e.g., {{#lookupIssues}} ... {{/}} ) only data from the lookup results and lower in scope is visible.  And so the table is not visible.

The lookup table approach works for a single issue.  To iterate over the lookup issues the two possible techniques are:

• a series of chained replace statements for each Jira user-to-Slack ID
  • for example, replace("12345", "ABCDE").replace("67890", "FGHIJ")...
  • This is difficult to maintain and prevent errors
• using entity properties (But wait, there is a relatively easier way to add these...)

To do the entity property method without a lot of hassle you may use a tool to add entity properties or use 2 rules.  Let's go over the 2-rule approach:

• Use a one-time rule to add the entity property for each user from a lookup table.  This rule could be used when updates are needed for the properties.  That rule could look like this:
  • trigger: scheduled with no JQL.  Although the trigger is "scheduled" the rule only needs to be run one time to set up the properties, and then it may be disabled.  It can be updated / re-run when needed.
  • action: create a lookup table, mapping the Jira accountID values to the Slack ID values.  Let's call this table varJiraIdToSlackId
  • advanced branch: iterate over the table values
    • branch variable name: varUserIds
    • branch smart value: {{varJiraIdToSlackId.entries}} 
      • action: Send Web Request, calling the REST API to add the property to each user
        • for example, the URL could be:
          • yourJiraURL/rest/api/3/user/properties/metadata?accountId={{varUserIds.key}}
        • and the data could be
          • { "slack_id": "{{varUserIds.value}}" }
      • action: log the accountID, Slack ID, and the results of the call
  • References:
    • How to call the REST API from a rule: https://community.atlassian.com/t5/Jira-articles/Automation-for-Jira-Send-web-request-using-Jira-REST-API/ba-p/1443828
    • The REST API endpoint needed: https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-user-properties/#api-rest-api-3-user-properties-propertykey-put
• Update the rule which sends the Slack messages to reference the entity property from the assignee in the issues from the lookup issues result

{{#lookupIssues}}
* {{key}} - {{summary}} assigned to {{assignee.displayName}} <@{{assignee.properties.metadata.slack_id}}>
{{/}}

When new people / users are added, the first rule may be updated, run again to refresh the entity properties, and then disabled.

UPDATED: I changed the entity property attributes to better align with that earlier article from Atlassian: https://community.atlassian.com/t5/Automation-articles/Mention-someone-directly-in-Slack-via-Jira-Automation/ba-p/1528605

[Kristján Geir Mathiesen]

Thank you so much, @Bill Sheboy  for clarifying. Makes sense now, after all my testing yesterday and not getting any value from the table. I had concluded that this code wasn't getting the value from the table at all.

So you are suggesting that the "extra" rule add a new attribute/property to our Atlassian user profile called "slack_id" and populate that. This attribute is then accessible within the iterator loop {{#lookupIssues}} ... {{/}}  yes?

Takk, KGM

[Bill Sheboy]

Yes, that is correct.

That first rule takes the place of the bulk-loading suggested in the article by @Daniel Eads and would work for a smaller number of users (although requires manually entering values in the table).

[Kristján Geir Mathiesen]

Thank you again, @Bill Sheboy  for all your help. If I may ask, does the field/property:

slack_id

exist already on the Atlassian user profile or do we have to create it somehow?

Takk, KGM

[Bill Sheboy]

I do not believe it exists by default, and I just did a quick test with my free-license site to confirm that.  You may do the same by:

• identify your user's account ID
• enter this URL in a browser tab to load the entity properties
  • yourJiraURL/rest/api/3/user/properties?accountId=yourAccountId
• search on the page for slack_id

And...we are adding the entity property under the metadata one to reduce the risks of collision and keep things nicely organized.  For example, if other properties were added for the user by the customer.

[Kristján Geir Mathiesen]

Thank you again, @Bill Sheboy , for all your help! I ended up using an Automation rule to populate my zslackid property on the Atlassian users:

Have a great day! KGM

[Kristján Geir Mathiesen]

Or you, brother @Darryl Lee ?

[Kristján Geir Mathiesen]

Hey @Bill Sheboy  Any ideas?

Takk, KGM