How to find Time Spent for a specific user

Question

Jira Data Center.  Is there a way to show the hour logged by a specific user?  We tried worklogAuthor=john AND Sprint = 12345.  The output showed Time Spent that included time other people who worked on the issue.  For example, john and other folks worked on an issue, John spent 5 hours, others spent 9 hours.  When we ran our query, it showed 14 hours.  We want to see only John's hours.

Tomasz Korusiewicz_SolDevelo · Answer

Hi @yms

If you want to easily and efficiently find the time logged by a specific user on a particular date range, you can always skip the JQL method and try out one of the apps offered on the Atlassian Marketplace.

As a SolDevelo representative, I can suggest our app, developed in-house called 'Worklogs - Time Tracking, Time Reports, Timesheets'.

Worklogs allow you to quickly filter out specific users' logged time within a specific date range, and other groupings such as work items, user groups, projects, and many other supported Jira fields:

Our app also allows you to log your time straight from the report view, meaning that all time logging work can be done straight from Worklogs:

Moreover, it is worth adding that if the time was previously logged into the issue through Jira, then these logs should already be visible in Worklogs, as it makes use of Jira's native time tracking features.

Feel free to try it out on our marketplace page!

Cheers!

Tomasz
SolDevelo Team

Marcelo Viana de Siqueira · Answer

Hi @yms !

Great question, this is a common scenario for teams that need to report on hours logged by individual users in Jira Data Center.

As you noticed, the worklogAuthor field in Jira’s advanced search will return all issues where the user has logged work, but it does not filter the time spent by author in the results. If multiple users have logged work on the same issue, the total time shown will be the sum of all worklogs, not just the hours for the user you specified.

Because of this, it’s not possible via JQL to directly get the time spent by a specific user in a sprint or set of issues.

As an alternative, if you have access to the Jira database, you can run the following SQL query to retrieve exactly the worklogs for a specific user (in this example, john) and a specific sprint (with ID 12345). This way, you can easily analyze and sum only the hours you want:

select wl.issueid,
       i.summary,
       wl.timeworked,
       wl.worklogbody as worklogComment,
       au.lower_user_name as worklogAuthor,
       wl.created as worklogCreated,
       wl.updated as worklogUpdated,
       sprint."NAME" as "sprint"
  from worklog wl
 inner join jiraissue i on wl.issueid = i.id
 inner join app_user au on wl.author = au.user_key
 inner join customfieldvalue cfv on cfv.issue = i.id
 inner join public."AO_60DB71_SPRINT" sprint on sprint."ID" = cast(cfv.stringvalue as bigint)
 where cfv.customfield IN (select id from customfield where cfname = 'Sprint')
   and sprint."ID" = 12345
   and au.lower_user_name = 'john'
  order by wl.updated asc;

Just adjust the sprint ID (sprint."ID") and the user (au.lower_user_name) as needed. The result will show each individual worklog, so you can separate and sum only the hours for the user you want.

If you need help adapting the query to your scenario, feel free to ask!

Hope this helps.

yms · Answer

It does not work. Here is what I observed:
Jira issue MYTASK-100 was assigned to userA
Multiple people logged their time in that issue.
userA logged Time Spent 15 minutes (first time spent entered)
userB logged Time Spent 30 minutes (second time spent entered)
userA logged Time Spent 1 hr 15 minutes (third time spent entered)

Run: worklogAuthor = userA AND Sprint = 32892 and timespent > 0
It found MYTASK-100 and showed Time Spent = 1 hr 15 mins.

Run: worklogAuthor = userB AND Sprint = 32892 and timespent > 0
It found MYTASK-100 and showed Time Spent was also 1 hr 15 mins.

Looked like it always showed the last Time Spent entry for an issue. I saw the same behavior in all tickets from that query. It did not make a difference if I used "timespent > 0" or not.