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!! 🙏