Send Notification when Total Billable Project Time Exceeds X hours

[Mayra Hernandez]

Hello!

I have the following workflow but I am trying to create a flow where it would send a notification to a specific user when total billable project hours exceeds a specific number of hours for the current month.

https://capture.dropbox.com/VSRaRsnxb2lbnsoG

Billable Hours field from Tempo - but if this is not possible, total hours logged is fine too

https://capture.dropbox.com/r0FjyuaBfhaCNaXz

Unfortunately, I can not find a formula for this.

Thank you!

Mayra

[Susan Wu]

Hi @Mayra Hernandez ,

Maybe this KB article can help with your use case if you are using Tempo Accounts.
https://tempo-io.atlassian.net/wiki/spaces/KB/pages/2249589149/Handling+time+buckets

Please take a look.

Best regards,

Susan Wu
Tempo Product Expert

[Mayra Hernandez]

Hi Susan!

Thanks so much, this looks like exactly what we need, however I am getting the following error when accessing the API: https://capture.dropbox.com/8y4wwxuULDcjv59O

Let me know your thoughts there?

Thank you!

Mayra

[Mayra Hernandez]

Nevermind, I was able to resolve this by following the instructions here! https://apidocs.tempo.io/#section/Base-Paths-for-API-Access

Will confirm if any other questions.

Thank you again!!

[Mayra Hernandez]

Hi Susan, one last issue I've encountered: https://capture.dropbox.com/UgZwVuHN3lkpC1HH

I am getting a 404 here, there is 100% worklogs attached to this task and the account has been linked to this task as well so not sure why I am still getting a 404 here.

Let me know if you can advise here.

Thank you!

Mayra

[Mayra Hernandez]

Also receiving a 401 retrieving account lead for some reason: https://capture.dropbox.com/5UEWpRXAprrQq4oT

[Mayra Hernandez]

Hey Susan! Just wanted to check in above. Thank you for your help!

[Susan Wu]

Hi @Mayra Hernandez 

Please make sure you have Tempo Account Admin permission and view all worklogs permission on the project(s) for the user account running the automation. 

Best regards,

Susan Wu
Tempo Product Expert

[Mayra Hernandez]

hey Susan! 

Confirming this permission is setup as I checked the permission settings and am able to view all worklogs.

[Mayra Hernandez]

hey @Susan Wu I am getting super close here, thank you for your help!

Would you know how I can adjust this "{{worklog.started.endOfMonth.jiraDate}}" so it only pulls Billable time?

This is how my web request URL looks now: https://api.tempo.io/4/worklogs/account/{{webhookResponse.body.key}}?from={{worklog.started.startOfMonth.jiraDate}}&to={{worklog.started.endOfMonth.jiraDate}}&limit=5000

[Bill Sheboy]

Hi @Mayra Hernandez 

Are you trying to check the total time spent for a single issue exceeding the 120h threshold, for a group of issues, or something else?

Perhaps if you provide a specific example, and explain how your rule is not working as expected, that will help the community to offer suggestions.  Thanks!

Kind regards,
Bill

[Mayra Hernandez]

Hi Bill!

Thanks so much for your response. I am looking for the total time spent within the "current month" which would include many issues.

The rule I put in place may not be the best example but currently our organization runs of monthly retainers. So X client spends 40 hours a month. I would like a notification sent to me when I have reached lets say 80% or 90% of that total.

[Bill Sheboy]

Thanks for that information, and I should have provided a disclaimer that I am not using Tempo; my suggestions are based on what I know about automation rules.

Next, I recommend reviewing the Tempo documentation first to determine if that tool already has this reporting / notification capability.  The reason I suggest that is...

If we assume your rule structure of checking the limit for a specific issue each time work is logged, it is possible to either:

1. Use a complicated smart value expression to sum the time within a data range.  This one could be difficult to maintain / explain to others.  A better approach would be...
2. Call a REST API function with the Send Web Request action to load only the change logs needed for the date range, and then sum the results.  https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issue-worklogs/#api-rest-api-3-issue-issueidorkey-worklog-get

There is in-progress limit change of up to 5000 worklog records per issue, and so if you expect an issue to have more than that amount this sum will not be possible.  Please see this article for that upcoming change: https://community.atlassian.com/t5/Jira-Software-articles/Upcoming-changes-Introducing-per-issue-limits-for-list-fields-in/ba-p/2468177

If you want to try either of these approaches, please let me know and I can offer more suggested details.

[Mayra Hernandez]

hey Bill! Thank you fo this, I've reached out to Tempo support and unfortunately, they do not have this capability :( 

If you would be able to provide any more info on #2 would probably be best if this is what you recommend a better approach would look like!

Thank you,

Mayra

[Bill Sheboy]

After I re-read the REST API function, I saw it requires finding the date / time range using a UNIX timestamp.  Although timestamps can be calculated in a rule, it may be more understandable to others to try approach #1 first, using list filtering.

First, the example solution and then the explanation.  This rule, triggered on work logging, will check if the total worked during the current month exceeds 120h.

• trigger: work logged
• condition: project is your CRO one
• action: create variable
  • name: varHoursLoggedThisMonth
  • smart value:

{{#=}}( 0{{#issue.worklog}}{{#if(and(started.isAfter(now.toDateTimeAtStartOfDay.startOfMonth()),started.isBefore(now.toDateTimeAtStartOfDay.endOfMonth().plusDays(1))))}} + {{timeSpentSeconds}}{{/}}{{/}} ) / 3600{{/}}

• smart value condition:
  • first value: {{varHoursLoggedThisMonth.asNumber.gt(120)}}
  • condition: equals
  • second value: true
• action: send email

How that smart value expression works, with links to the documentation:

1. the worklog is a list of values, and so we iterate over the values
2. each worklog has several attributes, and we want started and timeSpentSeconds
3. we want the values between the start and end of the current month, and so using date / time functions, we find those values
4. and we test if started is between the two, with the isAfter() and isBefore() functions
5. allowing the list of values to be reduced with smart value, list filtering
6. when a worklog is selected, we add the totalSpentSeconds for it
7. summing the results with a math expression, and dividing by 3600s to get hours
8. finally there is a default value of 0 at the front...just in case someone logs zero time

When the value is later tested, I used the 120h figure you showed in your rule image.

If you still want to try approach #2 with the REST API function call, let me know and I will add another post with those details.

[Mayra Hernandez]

Hi Bill - Thanks so much for your advise here. I tried this with a project that has time spent already at 110 hours and it did not trigger when I added an additional 11 hours to a task which reached the total project hours to 121h

I am getting this message in the logs:

https://capture.dropbox.com/popJQK5vZTDj2aDT

Let me know what you think?

[Bill Sheboy]

Without seeing your complete rule it is difficult to known what happened.

First, I recommend adding a write to the audit log after the Create Variable action:

summed hours: {{varHoursLoggedThisMonth}}

That will show what the rule found.

Next, for the issue to which you logged time, review the worklog list of entries, and manually calculate how much time occurred during March to compare the values.

[Mayra Hernandez]

hey Bill!

Thanks for helping troubleshoot here!

1. When I switched to the GC project I logged 12 hours - but it summed 0 https://capture.dropbox.com/S96FLixeNEqXXUBP

2. I switched back to the CRO project and logged 123 hours - and it summed - 123 hours: https://capture.dropbox.com/xlyBWu9hbs3w8cGH

> So for the CRO project - it did not include the other worklogs that were submitted in March only the most recent one I submitted.

Let me know if that helps with troubleshooting further.

Thank you again!

Mayra