Time spent( Work logged) by CurrentUser()

[Warren McInnes]

Hi, Is there any way using JQL to find "all issues of currentUser() where timespent/log work? Sometimes work is logged by a user that has not been the assignee of an issue at any stage. We are using the "Time sheet Gadget" to show logged work, but seems it only shows if you are the assignee when work is logged. Our filter used for time sheet gadget is "assignee = currentUser()"

[Deleted user]

Hi Warren,

I just wrote this exact query for an internal requirement ; glad to share. Assuming you have the most recent version of Jira, the following should work for you...

This Month (remove AND... for all time):

worklogAuthor = currentUser() AND worklogDate >= startOfMonth()

Specific date (replace date with endOfDay() for Today):

worklogAuthor = currentUser() AND worklogDate = "2015/07/10"

Note: Valid date formats include: 'YYYY/MM/DD', 'YYYY-MM-DD', or a period format e.g. '-5d', '4w 2d'.

Hope this helps.

[Warren McInnes]

I will have a look at this once we have upgraded our JIRA, We are currently on 6.2 and I have not tested this yet.

[Arif Ahmed Khan]

Hi Carl,

Many thanks for the above but it also shows work done on another day and adds it for the day it was logged. For e.g.  if I worked on an issue yesterday and today, today's report will show a sum of work logged yesterday and today. Can we not have it showing the time added for a specific day only?

Many thanks for your time and inputs on this one.

Arif

[Benjamin Dains]

@[deleted] thanks for that JQL.

--

I used the worklogDate to search between two dynamic dates like this:

(worklogDate >= startOfWeek() AND worklogDate <= endOfWeek())

Pop the, "Time Spent" field into the filter to see the total time spent on the item within the time period.

Note: You cannot ask to see multiple issues listed if someone enters time logged on a Monday & Wednesday it will show you the total time spent. If someone really had specific questions, then they will have to open the work log and view when the time was logged.

[jhgorse]

Can you clarify what you mean by Pop the "Time Spent" field into the filter?

[Benjamin Dains]

@jhgorse on the filter screen, add the, "Time Spent" field as a column. 

Using pop as a verb signifies you're performing a normal action in a easy and simple way.

[jhgorse]

@Benjamin Dains Ah, well I was hoping that pop meant something more along the lines of return this data for the query.

I use Google Sheets do to time tracking, invoicing, and accounting. I am looking at using JIRA in the workflow.

In order to do that, I am exploring the interfaces of the Atlassian data I would need. JIRA Query Language + Filter (Columns) gets me about there.

Now connecting that to Google Sheets, perhaps via REST.

[Andriy Zhdanov]

Hi Joe,

As author of Timesheet Reports and Gadgets for Jira, I'd like to let you know about extracting time tracking data from Jira via REST, please see https://www.primetimesheet.net/cloud/REST_endpoint.html.

Beside that, I've recently created script that can be run standalone, or deployed to cloud (e.g. heroku), please see it here, and feel free to use it as you wish.

Note, I'm often asked for functionality to import timesheet data into Google Sheets, so it could be useful, if you share more details about your use case, and I eventually implement it in add-on, or you share with community your code for connecting it with Google Sheets.

Thank you.

[jhgorse]

Andriy,

Thanks for the scripts!

For lightly extending JIRA, I believe it makes sense to expose project-specific data to spreadsheet front-end. Even if as an intermediary toward a headless automated system, the spreadsheet is a useful tool for visualizing data.

Each client has a different workflow. The first, I bill tasks against a PO until the hours are spent, invoice with the work log. So it would be useful to know the total hours toward an account and the remaining hours left.

The second is monthly billed to multiple accounts, which I separate by tasks. JIRA is sufficient for generating this type of reporting so long as I generate new tasks for each month due to the limitation described below.

The JQL resolution for worklogDate is not granular to when work is actually logged. So if I create a query for >= date, with some time logged before and after that date, it will show all of the hours associated with that task in the query report. It should show only the hours and activity during the time period specified.

Cheers,
Joe

[jhgorse]

This REST function, Get ids of worklogs modified since, of the Jira API appears to be the most relevant for filtering worklogs by date:

https://docs.atlassian.com/software/jira/docs/api/REST/7.1.2/#api/2/worklog-getIdsOfWorklogsModifiedSince

curl -D- -u "user:passwd" -X GET -H "Content-Type: application/json" http://localhost:8080/rest/api/2/worklog/updated?since=1518487098103

From here, we get a list of worklog ids to query for the user, time, and comment. That is all that's necessary to generate the worklog report with ongoing time spent on issues.

[jhgorse]

One small snag is that it is not possible to extract the Issue Summary from the worklogs, so we have to worklogIds -> issueIds -> jql : issue in (<issue ids>) separately and match them up later. Three REST queries to get the data in this fashion.

There is probably a more nuanced approach which could do a single JQL worklogDate REST query then filter the individual worklog entries by comparing timestamps.

[Michael Clark]

Why would there not be out of box functionality to filter by worklog author only?  As it is now, that doesn't exclude other worklog authors even if you specify to not include them. time spent result includes the total for all work logged.

[Casey Maynard]

So I am using worklogAuthor is not EMPTY to get all of the time logged, but how do I get the column for this to see who logged the time?

[Tarek Faham]

Is there a way we can get a report of the number of hours logged for a user by date? I want to see the details. I can see the sum of hours logged on the search results, but this number will give you all hours logged for all users, not for the users who specified on the filter (worklogAuthor filter). How I can see the details of the hours logged by the user, and see the related ticket also?

[Mitali Seal]

I am also looking for a jql query which will return number of hours logged by an user on specific dates for a week.

Thanks in advance for any help.

[Jenny Nelson]

@Tarek Faham and @Mitali Seal were you able to find a jql query to return number of hours logged by a user on a specific date?

[Agnieszka Kornas]

Hi @Benjamin Dains - your short query still saves lives! Greetings from 2020:) 
@Jenny Nelson  - you can solve it with creating the filter and than using workload pie chart on some confluence page

[Tarek Faham]

I am still not able to find the number of hours worked on a given day or date range for a give user, and what are the related ticket. There must be an easy way to get such info.

[Shridhar]

Hi @Tarek Faham @Jenny Nelson @Agnieszka Kornas @Mitali Seal @Casey Maynard 

Have you tried https://marketplace.atlassian.com/apps/1221570/jassistant?tab=overview&utm_source=cforum&hosting=cloud?

It is absolutely free and easy to setup / use. No Jira Admin permissions needed. Visit https://www.jiraassistant.com/ for more details.

[Disclaimer] I am the developer of this addon.

[Andriy Zhdanov]

Hi Warren,

Why do you use the filter? If you don't use, timesheet gadget will show you all time spent for all isues worked by current user actually.

Thank you.

[Warren McInnes]

Thank you, this is correct! Thanks Andriy

[Namrata Parab]

Time sheet gadget isn't showing assignee or user name wise.. I am naive so if you could please help or redirect me to the solution? 

[Andriy Zhdanov]

Hi Namrata,

Could you please check https://primetimesheet.atlassian.net/wiki/spaces/KB/pages/327709/Display+the+timesheet+table+by+user+and+date if it helps.

Thank you.

[Gregor Kasmann_Actonic]

just have a look at this solution https://marketplace.atlassian.com/apps/1216997/worklog-simple-report?hosting=server&tab=overview

[Hector David]

use (Tempo) time tracking software add-on

[Jobin Kuruvilla [Adaptavist]]

JQL Tricks plugin (paid) provides this functionality. You can then use the new JQL functions with any gadgets, filters etc.

[Warren McInnes]

Hi Jobin, thanks for your input, Is there another way? preferably free way to do this? JQL unfortunately is very limited. Can anyone advise?

[Subrata Mallik (subu)]

There is a tool to manage your jira work log with auto timer 
https://app.jiraautolog.com

[Andre Serrano]

:)