Help with Automation and JQL Query for Author Worklogs

[Colin Porter]

I'm hoping someone in the Atlassian community might be able to help me with an automation rule that I'm struggling to configure?

The Goal / Use Case Example

I want to create an automation rule that sends an email notification to a service desk agent at the start of each day, conditional on the number of hours they logged for the previous day.

Let's say 'service desk agent A' works an 8-hour day, and we want it flagged when that user doesn't log at least 75% of their work hours. So in this instance, if the sum of their total worklogs for the prior day is less than 6 hours, a notification is required the next morning warning them of this.

I'll need to create a unique automation for each service desk agent, so we can adjust the target hours that triggers the notice, and also who gets notified.

I know the below JQL Query will return a user's worklogs, but I don't know how to total it (get the sum), and then use this sum to be a condition on my automation rule (i.e.  If [sum] < 6h):

worklogAuthor = [service-desk-agent-a-id] AND worklogDate >= startOfDay(-1) AND worklogDate <= endOfDay(-1)

Any help would very much be appreciated!! 🙏

[Duc Thang TRAN]

Hello @Colin Porter 

You might try combining Lookup issues with an Advanced branch. Using smart values like {{worklog.author.displayName}} and {{worklog.timeSpentSeconds}}, it could be possible to build some logic that sums time per user and then checks if the total is above or below  a 18000 (5h in second) 

Hope this can help :)

[Colin Porter]

I'm sure you must be onto something here, but I'm definitely not experienced enough with this area to know how to put it together!

[Matteo Vecchiato]

Hi @Colin Porter

That Jql will return the work items that contain work logs from the specified author, then it is required to get the list of worklog of resultants work item, go through each of them and sum only the work item is delivered author.

I don't think it is possible doing it with standard automation actions, you should call API from automation (or script). Please have a look on this rest API: https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issue-worklogs/#api-rest-api-3-issue-issueidorkey-worklog-get 

I hope it helps.

Regards

[Bill Sheboy]

Hi @Colin Porter 

The scenario you describe might be possible using automation features if two assumptions are valid for your case:

• no more than 100 work items have updated worklog entries the previous day (i.e., the lookup work items action limit)
• there are no more than 100 worklog entries total per each work item (i.e., the REST API limit used by the lookup work items action)

In that case, this would not be a trivial rule to implement due to the need to send a single message per worklog author, and it will require advanced techniques such as dynamic list searching with regular expressions, as I described in this community article.

Because of this complexity, you may want to discuss this need with your Jira Site Admin to learn about marketplace apps to perform this type of time tracking monitoring / reporting rather than trying to build the rule.

Kind regards,
Bill