What is your most useful automation ? Here is mine.

[Flavien Gache]

Hi everybody.

I sometimes have automations to set for clients.

I wanted to share one particular automation because I often see people on Community struggling with it and it saved me a lot of time.

Let's say you want to base your story points value on the sum of two other fields : Priority and Impact.

For example, you have the two following fields (with the associated story points) :

Priority => Cascading list with these possible values : Urgent (5), Major (4), Minor (3).

Impact => Cascading list with these possible values : Significant (6), Moderate (2) , Mild (1).

If you want to automate the calculation of the story points, you have to create three rules.

One which will calculate the value of a custom field called PriorityScore. Basically, it will look like this :

When value changed for Priority

If Priority = Urgent

Then update field PriorityScore = 5 (to do this you have to type {{#=}}5{{/}} in the PriorityScore bar)

Else-If Priority = Major

Then update field PriorityScore = 4 (to do this you have to type {{#=}}4{{/}} in the PriorityScore bar)

etc

Another one which will calculate the value of a custom field called ImpactScore. It is the same logic as the previous rule.

A third one which will sum both values and update story points. 

When value changed for (PriorityScore, ImpactScore, Story points)

Then update the story points field : 

{{#=}}{{issue.PriorityScore|0}}+{{issue.PriorityScore|0}}

The |0 determines the default value to avoid problems when some fields are empty.

In the settings for this third rule, don't forget to check the "Check to allow other rule actions to trigger this rule. Only enable this if you need this rule to execute in response to another rule.".

Do you have any easy automation of that kind you often propose to your clients ?

[Bill Sheboy]

Hi, community!  How about Age of WIP (work item age)?

(1) Add a custom numeric field, AgeOfWip

(2) Create the rule with a daily schedule, looking only at "in progress" and non-cancelled items

(3) Edit the custom field to calculate in calendar days

{{#=}}ROUND({{issue.statuscategorychangedate.toDate.convertToTimeZone(reporter.timeZone).diff(now.convertToTimeZone(reporter.timeZone)).millis}} / (1000*60*60*24), 3){{/}}

(4) Add the field to a dashboard, work items, reporting, board colors, etc.

(5) Use Age of WIP to monitor for stalled work, violations of Levels of Service, mis-use of Expedite urgency, etc.

Please look here to learn more about this measure:

https://www.scrum.org/resources/blog/4-key-flow-metrics-and-how-use-them-scrums-events

[Tom Williams]

Nice use of automation here Flavien. I can see myself using this rule to automatically change the ranking of issues on the backlog. With my Agile hat on, I'd argue though that a Story Point is not a measure determined by Priority and Impact rather consensus in the effort. 

[Guilhem Dupuy]

Hi Flavien !

What a nice automation rule you built there ! It looks pretty neat !

An easy one which is often required by my clients is an automation rule to automatically close an issue when its subtasks are all closed.

It's a pretty easy one, but I though I could share it with you !

Here is an instance of it I use in a Demo project :

Any thoughts ?

[Kiruthika]

Got the related info from https://support.atlassian.com/jira-software-cloud/docs/smart-values-date-and-time-functions/ 

And Thanks @Bill Sheboy , I'm done with configuring this custom field "Age of tickets" and can now include in various reports.. :) 

[Kiruthika]

hi @Bill Sheboy - Can you pls let me know if there is any documentation to refer for the formula generation? 

I need to find difference between current date and the "created date" from JIRA

[Bill Sheboy]

Hi @Danish Magrey 

Those sections dynamically generate the JSON to remove/add the labels.  They are used inside of the list, iterator for walking the from/to labels in changelog.

For more details:

• Please see the advanced edit for JSON help on remove/add of labels, as that was used to make the template you note.  Specifically note how only single curly brackets were used to contain each remove/add as the list is walked.  https://support.atlassian.com/cloud-automation/docs/advanced-field-editing-using-json/#Labels
• The {{.}} is a list smart value-placeholder, and it returns whatever value the list was walking; in this case, it is a label.  Please look here for more details and an example: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-lists/
• And for this section {{^last}}, {{/}} we need to make well-formatted JSON, and so we to dynamically add a comma-followed-by-a-space after each entry...except the last one.  This is another feature of list iterators, which you can see in the Combined Function Examples section of the help: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-lists/#Combined-function-examples

Kind regards,
Bill

[Bill Sheboy]

Here is one:

Synch labels from epic to child issues, allowing the children to keep any additional labels they added.

This uses the change log, created variables and JSON, dynamically built, to first remove the old labels and then to add the new ones.  To learn about this method, please take a look at the linked documentation above.

• trigger: value changes for Labels
• condition: Initiator is not "Automation for Jira" user.  This is a precaution in case this rule runs slower for some reason or the concepts around rule triggering change, to prevent it from calling itself.
• condition: issue type is Epic
• action: create variable
  • name: varRemoveLabels
  • smart value: {{#changelog.labels}}{{#fromString.split(" ")}}{ "remove": "{{.}}" } {{^last}}, {{/}}{{/}}{{/}}
• action: create variable
  • name: varAddLabels
  • smart value: {{#changelog.labels}}{{#toString.split(" ")}}{ "add": "{{.}}" } {{^last}}, {{/}}{{/}}{{/}}
• branch: for stories (or other issues) in Epic
  • action: edit issue to change the labels using the dynamic JSON

{
  "update": {
    "labels": [
      {{#if(exists(varRemoveLabels))}}{{varRemoveLabels}},{{/}}
      {{varAddLabels}}
    ]
  }
}

How it works...

To pass down the epic changes to labels and preserve and different values in the child issues (e.g. stories) we need to remove the old (before) epic labels and then add the new (after) epic labels.  Ideally, we want to do this in one step to avoid repeated changes to the same issue.

When an issue changes, information is captured in the change log, including the before and after values.  For labels, the changes are stored as a list of values with spaces between each label change.  So we can capture the before values with {{#changelog.labels}}{{fromString}}{{/}} and then split them with the embedded spaces.  We can do the same thing to get the after values with {{toString}}

When using the JSON edits, we need to use the specific format which allows both adding and removing changes to do this in one step.  For labels this looks like this:

{ 
  "update": { 
    "labels": [ 
      { 
        "remove": "Label1" 
      }, 
      { 
        "add": "Label2" 
      } 
    ] 
  } 
}

The wrinkle for synching labels is we do not know how many will be added or removed, and so we dynamically build the bolded sections above for the {{varAddLabels}} and {{varRemoveLabels}}

The final piece is the "remove" section could be empty, and so that conditional logic test helps prevent a stray comma from breaking things.

[Tony Georgiadis]

Hi Bill,

Thank you for your response.

Your description of the problem is accurate; as you said, I'd like to measure full calendar time and account for non-working weekend days, in order to measure WIP age excluding weekends.

Essentially, the initial solution works for me, but not for items that are carried over from one week to the next.

I've managed to find a somewhat inelegant workaround, which is to manually check whether an item is carried over, by checking the previous 7 days (from present) to verify whether a weekend day occurred. If it did occur, then I subtrack 2 days (the weekend days) from the AgeWIP. Our average cycle time is below 3 consistently so this workaround will work for us.

Best wishes,
Tony

[Bill Sheboy]

Hi @Kiruthika 

The link you provided is the one I use for date/time calculations, and the others for text and math are linked on that same page.  For everything else, I end up creating lots of experiment rules with logging to figure things out.  :^)

Please note that some of the date or date/time fields are text and not numeric.  As in my Age of WIP example, those need to be converted to a date first.

Best regards,

Bill

[Flavien Gache]

Very useful indeed. I will remember that ;) Thanks for sharing !

[Bill Sheboy]

How to pretty print a time measure into business days, hours, minutes

There are built-in date time functions to perform difference calculations and format the result.  They can even "pretty print" into people-friendly time units.

For example, the time difference between issue created and updated:

{{issue.created.diff(issue.updated).prettyPrint}}

Producing something like this: 4 days 23 hours 33 minutes 24 seconds

That is showing 24 hours / day, not business days (e.g., 8 hours / day).  That is great for some applications, but not others...

What if we wanted business days?

{{issue.created.diff(issue.updated).businessDays}}

Producing something like this: 3

That helps, showing just the working days based on the dates.  But it is unable to produce working hours...as the global and project settings are not available to the functions.  Again, this may be great for the particular use case.

What if instead we are not performing a date / time difference, and have a duration value, such as from a time tracking sum from issues in a Lookup Issues result?

Let's assume there are 24 hours in a day (not working hours).  That could be formatted by adding the number of seconds to {{now}} and performing a diff on original value, and using the prettyPrint difference format: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Date-difference---

{{now.diff(now.plusSeconds(lookupIssues.timetracking.timeSpentSeconds.sum)).prettyPrint}}

That works, however when people enter time tracking it is often in working hours; they do not convert / scale to calendar days just to log time.  Yet some may want to view the tracked time relative to calendars, without converting each time they look.

So let's use those ideas, converting the time tracking into something more people-friendly, by scaling to 24 hour days to leverage how diff works.

First, let's see the solution, and then explain further:

{{now.diff(now.plusSeconds(lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor.multiply(3).multiply(28800).plus(lookupIssues.timetracking.timeSpentSeconds.sum.minus(lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor.multiply(28800))))).prettyPrint}}

How does that thing work?!?!

• The logged time sum can be grabbed in seconds, which people normally enter in working time, not calendar time
• Let's assume an 8h work day, and so the "trick" is to separate the whole 8h days, scale that up to 24h days, add in any partial days, and we have an adjusted total
• Once we have that value, it can be added to any date / time, such as {{now}} to perform a difference calculation...leading to a pretty print option
• To break down the above expression further:

(A) Total time spent seconds = {{lookupIssues.timetracking.timeSpentSeconds.sum}}

(B) Seconds in an 8h day = 8h * 60m / 1h * 60s/ 1m = 28800 seconds

(C) Dividing those gives the 8h day count: {{lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800)}}

(D) But we want the whole days only, so we add the floor function: {{lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor}}

(E) Each 24h day has the equivalent of 3 x 8h, so we multiply by 3: {{lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor.multiply(3)}}

(F) And multiply by 28800 to get back to seconds: {{lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor.multiply(3).multiply(28800)}}

(G) We now need to find the partial 8h so we can get the hours and minutes. To find that, we need to subtract the seconds in whole-8h days from the original total.  {{lookupIssues.timetracking.timeSpentSeconds.sum.minus(lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor.multiply(28800))}}

(H) Adding together (F) and (G) we have the correct total seconds.

(I) Finally returning to the original technique, we just add these to {{now}} and perform a diff, formatting with pretty print.

Whew!  This could be simpler, except I do not believe there is an inline version of the modulo function for rules.

Kind regards,
Bill

[admin-nwalter]

Hello Bill,

This was an amazing find. Very interesting to learn about. 

Would this be applicable to use between 2 custom date/time fields? I tried changing the solution you provided to apply for my situation but the results came out blank.

[Bill Sheboy]

Hi @admin-nwalter 

The intent of that solution was to "pretty print" an amount of time, such as from the time tracking fields.

To do what you ask for calendar / clock time differences, calculate the difference in seconds between the two date / time fields and use that in place of the time tracking amount.  That approach would not work for business days / hours.  That would need a different algorithm.

If that suggestion does not help, please create a new question, adding a link back to your message here, and include images of the rule for context:

• an image of the complete rule in one image file for continuity
• images of the relevant trigger, actions, conditions, and branches
• and image of the audit log details showing the rule execution

Kind regards,
Bill

[Danish Magrey]

Hi @Bill Sheboy 

This automation is really helpful :)

Could you help me understand as to what they below do:

• • { "remove": "{{.}}" } {{^last}}, {{/}}{{/}}{{/}}
  • { "add": "{{.}}" } {{^last}}, {{/}}{{/}}{{/}}

[Bill Sheboy]

You are correct, Tony: that unit of measure is whole days.

It seems like you want to measure full clock/calendar time *and* account for non-working for weekends.  I do not believe there is anything which can do that out-of-the-box at millisecond resolution.

You could try a more complex work-around, using working days of week/hours and math operations to start with the total value (e.g. including weekends), and then subtract out embedded weekends and non-working hours.

What problem are you trying to solve by measuring this?  Knowing that may help the community suggest other options.

[Tony Georgiadis]

Hi @Bill Sheboy

Thank you for your welcome, and your prompt response.

It seems like businessDays provides a rounded integer and therefore it hasn't provide enough accuracy for capturing a more accurate WIP age.

Looking in the documentation, it seems like using businessDays does not give the required granularity. If I am missing something here please let me know.

Thank you again for your support.

Best wishes,
Tony

[Bill Sheboy]

Hi @Tony Georgiadis -- Welcome to the Atlassian Community!

That is correct, as you are using a diff() with units of measure of milliseconds.  Please try businessDays instead to count Monday-Friday, as described here: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/

I do not believe this feature accounts for any defined non-working days or the working hours in the site configuration.

Kind regards,
Bill

[Tony Georgiadis]

Hi @Bill Sheboy and all,

It seems like the action below, for tickets that are left open during the weekend, it takes into account the time passed during the weekend. I wonder whether this is something others have encountered?

{{#=}}ROUND({{issue.statuscategorychangedate.toDate.convertToTimeZone(reporter.timeZone).diff(now.convertToTimeZone(reporter.timeZone)).millis}} / (1000*60*60*24), 3){{/}}

Thank you in advance,

Tony

[Lissa Meade]

We have a Celigo integration that takes time logs from Jira and pulls them into NetSuite, but all tickets need epic links. The problem was that our QA team was creating Bugs with a "relates to" link to the ticket it stemmed from, and so we were getting tons of errors since there was no epic link. (We can't use child issues, as you can only have one child issue type in Team Managed project and that is already being used for subtasks.)

I created this automation rule to look for the parent (epic) on a newly linked issue and add it to the Bug, as long as an epic link doesn't already exist. (The action is "Trigger Issue")

[Bill Sheboy]

Determine if issue is from a next-gen (or classic) project inside a rule

There does not appear to be a built-in way to determine if an issue is from a classic or next-gen project from inside of an automation rule. Projects have a "style" attribute, and this appears to only be available on the project list and in REST API methods at this time. When your automation rules have different behavior based upon classic or next-gen, it can help to determine this within a rule.

Automation rule details:

• Trigger: whatever you need
• Action: send web request to call the REST API Get all projects method.  Or, just get the project for this one issue.
• Action: log the response status for error logging {{webhookResponse.status}}
• Action: create a variable to hold the project keys and styles, calling it varProjects

{{#webhookResponse.body}}{{key}}:{{style}}{{^last}}, {{/}}{{/}}

• Action: log the list of next-gen projects (or switch to classic if you wish)
  • Split the varProjects variable into a list
  • Find any matching next-gen styles with RegEx and match()
  • Remove the string "next-gen" string to create a list

Next-Gen Projects: {{varProjects.split(",").match("(.*next-gen)").replace(":next-gen","")}}

• More actions... If you need to check the issue, compare {{issue.project.key}} for presence in the list

References:

https://community.atlassian.com/t5/Jira-articles/Automation-for-Jira-Send-web-request-using-Jira-REST-API/ba-p/1443828

https://docs.atlassian.com/software/jira/docs/api/REST/1000.824.0/#api/2/project-getAllProjects

https://docs.atlassian.com/software/jira/docs/api/REST/1000.824.0/#api/2/project-getProject

[Bill Sheboy]

How about a no-addons required, histogram plot of issue data?

TL;DR: Using lookup issues, format and RegEx functions, count the issues meeting your criteria. Padding functions add the histogram data points. This works for up to 100 issues (due to the rule execution limit), and can only access the fields within the lookup.

In this example, let's frequency plot the hours issues were created. Here is an example output image.

Experiment to create a histogram of issue create times.
00: 
01: 
02: 
03: 
04: 
05: 
06: 
07: ••••••••••••••••••••••••••••
08: •
09: ••••••••••••••••
10: •••••••••••••••••
11: ••••••
12: •••••••
13: ••••••••••
14: ••••••
15: ••••••
16: •••
17: 
18: 
19: 
20: 
21: 
22: 
23: 

The how-to for the rule is:

• Trigger: scheduled, or can be what you wish
• Action: Lookup Issues with your criteria, such as JQL
• Action: Create Variable with a dummy value, such as {{varEmpty}} set to null
• Action: let's use an HTML formatted email
  • Contents of the email body:

<body>
<div style="font-family:courier">
<p>Experiment to create a histogram of issue create times.<br>
00{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^0$)").split(",").size.plus(2),"•")}}<br>
01{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^1$)").split(",").size.plus(2),"•")}}<br>
02{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^2$)").split(",").size.plus(2),"•")}}<br>
03{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^3$)").split(",").size.plus(2),"•")}}<br>
04{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^4$)").split(",").size.plus(2),"•")}}<br>
05{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^5$)").split(",").size.plus(2),"•")}}<br>
06{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^6$)").split(",").size.plus(2),"•")}}<br>
07{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^7$)").split(",").size.plus(2),"•")}}<br>
08{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^8$)").split(",").size.plus(2),"•")}}<br>
09{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^9$)").split(",").size.plus(2),"•")}}<br>
10{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^10$)").split(",").size.plus(2),"•")}}<br>
11{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^11$)").split(",").size.plus(2),"•")}}<br>
12{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^12$)").split(",").size.plus(2),"•")}}<br>
13{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^13$)").split(",").size.plus(2),"•")}}<br>
14{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^14$)").split(",").size.plus(2),"•")}}<br>
15{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^15$)").split(",").size.plus(2),"•")}}<br>
16{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^16$)").split(",").size.plus(2),"•")}}<br>
17{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^17$)").split(",").size.plus(2),"•")}}<br>
18{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^18$)").split(",").size.plus(2),"•")}}<br>
19{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^19$)").split(",").size.plus(2),"•")}}<br>
20{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^20$)").split(",").size.plus(2),"•")}}<br>
21{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^21$)").split(",").size.plus(2),"•")}}<br>
22{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^22$)").split(",").size.plus(2),"•")}}<br>
23{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^23$)").split(",").size.plus(2),"•")}}<br>
<br></p>
</div>
</body>

Explanation of what is happening:

• Some HTML formatting around the data to force to a mono-spaced font
• Let's look at one specific line of the histogram output

00{{varEmpty.replace("null",": ").rightPad(lookupIssues.created.convertToTimeZone(lookupIssues.first().reporter.timeZone).format("k").match("(^0$)").split(",").size.plus(2),"•")}}<br>

• Using the dummy varEmpty variable, we replace the contents with a delimiter, ": "
• Right-pad that delimiter with some dots, based upon the count of issues matching our criteria
• To get that count of created-hour issues, we first convert to the Time Zone of the reporter. Lookup Issues is a list, so we must only access one reporter, such as the first() one
• Formatting the created date/time, we grab only the 24-hour time, from 0-23
• Using RegEx, we match on the hour we are interested in for the row, resulting in a CSV string
• Which we then convert to a list using split(",")
• And, we are right-padding a string which already has the ": " delimiter, so we add 2 for the length
• Finally wrapping up with an HTML line break

There are several potential use cases for this one. The key challenge is the 100 issues processing limit; so if your use case fits in that limit I hope this can help you.

Best regards,
Bill

[Bill Sheboy]

Hi @Elizabeth Dutton 

Are you able to use incoming webhooks to do that?  Please take a look here for more information for Jira Server/Data Center:

https://confluence.atlassian.com/automation/triggers-993924804.html/#Triggers-incomingwebhookIncomingwebhook

[Elizabeth Dutton]

These are brilliant.  Anyone got any ways of using automation for Jira to transition issues based on Bit Bucket pull requests.  There is functionality on Jira Cloud, but we have Jira Server and moving to Data Center.