How to get list of worklogs through JIRA REST API

Yan Yan September 17, 2012

Hi guys,

I need to get all worklogs for specific period of time. Now I see only one way:

  • Get all changed issues: rest/api/latest/search?jql=updated>=2012-09-01
  • Get every issue details: rest/api/latest/issue/ISSUE-KEY and extract worklogs from it.

Is there another way to get it? First that I tried was expand. I tried rest/api/latest/search?jql=updated>=2012-09-01&expand=worklog but it doesn't work. I'm trying to find a way to get everything with single query to avoid additional network colloboration. Could you advice anything please?

10 answers

1 accepted

13 votes
Answer accepted
Denis Blanchette January 23, 2020

I realize this question is more than 7 years old, but this seems to still be an issue and I've struggled with this for a while, so I thought I would share my solution.

My first solution was to get all issues with a worklog using a JQL, `worklogDate >= -28d` for example. Then use the /issue endpoint to get the worklogs of each of these issues one after the other. As others have pointed out, this is very slow, because some general issues can have thousands of worklogs (we have one for PTO, for example).

Here is my new strategy, that is ten times faster:

  1. Use GET /rest/api/3/worklog/updated to get the IDs of worklogs in the time period. The timestamp refers to the time the worklog has been created/updated, not the date to which the entry refers. To make sure I have everything, I just go later in the past. The call is paginated, and the response is small, so listing too much is not a big problem. You just need to remove the worklogs you don't want afterwards
  2. Use POST /rest/api/3/worklog/list to get the actual worklogs. The payload is the list of IDs to you got in the first step. This is limited to 1000 entries, but you can call it multiple times
  3. Bonus - If you need the issues for the retrieved worklogs, use POST /rest/api/3/search. You need to use POST, because the query will be very long and does not fit in the URL. You can build the query from the issue ids in the worklogs retrieved in step 2 (`id in (12345, 456789, ...)`

Python code for this solution that handles pagination: https://gist.github.com/dblanchette/b8ed8cf42431f56024c1c70ed5137e0f

Larry Weisberg October 22, 2020

Hi @Denis Blanchette  - Thanks for the shared python code.  I generated a Jira API token for myself, and tested the code and it worked.  This was about 10 days ago.  A few days later I ran the same code and since then have been getting SSL errors like:

  • urllib3.exceptions.MaxRetryError: HTTPSConnectionPool(host='XXXXX.atlassian.net', port=443): Max retries exceeded with url: /rest/api/3/worklog/updated?since=1601801936618 (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:1076)')))

I have written a few more details at https://community.atlassian.com/t5/Jira-questions/REST-api-using-token-ow-gives-SSL-error/qaq-p/1509203#U1509247 but have not gotten a solution as of yet.  Maybe you have some ideas?

 

Thanks,

Larry

4 votes
Paul van den Dool November 20, 2019

For anybody stumbling on this topic like I have when searching for a way to collect worklogs on a specific date for a specific user using the Jira Server Rest API , I've found a way that works for me.

I've stumbled into issues with /rest/api/2/worklog/updated where I couldn't get worklogs for a specific date because the worklogs for a specific date were added on a later date and the "last 1000 updated worklogs" that the endpoint returned wasn't enough in my case.

Other issues occured when trying to get the worklogs following the next steps:

1. /rest/api/2/search with jql that says "where worklogAuthor is mrX and worklogDate is preferedDate" (this returns a list of issues with the last 20 worklogs within that issue)
2. /rest/api/2/issue/{issueId}worklog to get more worklogs for the issues (multiple requests)
3. Filter out worklogs where worklogAuthor is Mr.X

These last requests could be slow for issues used as general task where a lot of work has been logged upon. I had an example where it took 30 seconds!

 

So I thought, how do they do it for the user timesheets in Jira itself?
Checking the network tab of the DevTools showed me this timesheet request:
/rest/com.deniz.jira.worklog/1.0/timesheet/user?startDate=2019-11-18&endDate=2019-11-24&targetKey=Mr.X
This gives the worklogs per issue for a user within a certain date. With a very acceptable response time.

A little further exploration showed me that that request works with the cookie authorization mentioned in the docs for the Jira Server Rest API I linked above.

POST call to /rest/auth/1/session with a body of { username: whatever, password: nottellingyou }. This returns the JSESSIONID you can also pass as a cookie header to that timesheet request.

So even though this is not an official documented endpoint for the Server Rest API and it might be prone to changes because they only seem to be using it within the Jira application itself, it is the best way for getting worklogs for a specific user that I've found.

4 votes
Marius Storm-Olsen January 8, 2015

FYI: Here's the PHP code for getting all worklogs within a specific period.

So, yes, you'll have to iterate through the potential batch of issues, but the key is to narrow down the results as much as possible. I did that by making sure that create date is before the end of the period, the update/modify date is after the beginning of the period, and that some time has been logged at all (timespent field). Then you simply get the full worklog for each item, and keep the entries within the period.

Quite quick on my setup with those constraints.

 

<?php
$server   = 'jira.myserver.com';
$fromDate = '2012-01-01';
$toDate   = '2012-01-31';
$project  = 'X';
$assignee = 'bob';

$username = 'my_name';
$password = 'my_password';


$curl = curl_init();
curl_setopt($curl, CURLOPT_USERPWD, "$username:$password");
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curl, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, 0);


# Give me up to 1000 search results with the Key, where
# assignee = $assignee  AND  project = $project
#  AND created < $toDate  AND  updated > $fromDate
#  AND timespent > 0
curl_setopt($curl, CURLOPT_URL, 
            "https://$server/rest/api/2/search?startIndex=0&jql=".
            "assignee+%3D+$assignee+and+project+%3D+$project+".
            "and+created+%3C+$toDate+and+updated+%3E+$fromDate+".
            "and+timespent+%3E+0&fields=key&maxResults=1000");


$issues = json_decode(curl_exec($curl), true);
foreach ($issues['issues'] as $issue) {
    $key = $issue['key'];
    # for each issue in result, give me the full worklog for that issue
    curl_setopt($curl, CURLOPT_URL,
                "https://$server/rest/api/2/issue/$key/worklog");


    $worklog = json_decode(curl_exec($curl), true);
    foreach ($worklog['worklogs'] as $entry) {
        $shortDate = substr($entry['started'], 0, 10);
        # keep a worklog entry on $key item,
        # iff within the search time period
        if ($shortDate >= $fromDate && $shortDate <= $toDate)
            $periodLog[$key][] = $entry;
    }
}
# Show Result:
#  echo json_encode($periodLog);
#  var_dump($periodLog);
?>
Joost Pastoor June 3, 2016

Very nice, I made a small tool based on your snippet:

https://github.com/jpastoor/jira-worklog-extractor

Will add additional features, filters and output types soon, but the basis is working to get your quick CSV output.

Like # people like this
4 votes
josefsabl March 17, 2014

Yan O's solution will not work as Jira allows to log work in the past and the issue gets updated "now".

This solution also does not allow to check some period in the past, as it only scans issues LAST UPDATED in the given period. You would have to scan ALL the issues which is ridiculously slow.

And lastly, even with these bugs, the solution is terribly slow. I made a little script that pulls out all worklogs of the last week and it takes TWO MINUTES to run.

This situation is terrible and makes API pretty useless for us. Please, can you suggest or implement a usable solution?

PS: No, we can't access DB directly. Isn't the point of API to not need direct access to DB?

1 vote
Oleg February 8, 2021

I have found another solution for this issue. Note that the main issue is that there is no API that allows to retrieve worklogs by start and end date via API.

Another problem is that there is no good widget to overview timesheets in Jira CLoud/Server.

We are using in our company this plugin to solve both points https://marketplace.atlassian.com/apps/294/timesheet-reports-and-gadgets?hosting=cloud&tab=overview

 

It allows not only to have widget for overview of spent time accross all the company, but also has REST Api end point to retrieve timesheets by start and end dates https://www.primetimesheet.net/cloud/REST_endpoint.html

 

Hope this answer will be helpful to anybody struggling with the same

Larry Weisberg February 8, 2021

We've also found a pretty good solution in the Chrome Extension called Jira Assistant.

https://chrome.google.com/webstore/detail/jira-assistant/momjbjbjpbcbnepbgkkiaofkgimihbii

Like Dario B likes this
1 vote
Adam Labus March 24, 2018

Hello guys,

Please check my plugin Extender for JIRA and documentations Get Worklogs.

With my plugin you can now search worklogs for specific user, issue or JQL query using dedicated parameters like date, project kay etc.

Best regards
Adam Labus

1 vote
Bjarni Thorbjornsson
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 17, 2014

Hi,

I know you are asking for a REST resource but you may be able to use Tempo's "getWorklogs" servlet that provides XML of the worklogs. See here for details: https://tempoplugin.jira.com/wiki/display/TEMPO/Tempo+Servlet+Manual#TempoServletManual-GetWorklogs

Best regards,

-Bjarni (Tempo support)

josefsabl March 18, 2014

Thanks for the tip Bjarni. I take the fact this servlet exists to be the proof that there is no way to get this basic information out of vanilla Jira. It is frustrating that Jira can do little to nothing without the use of plugins (mostly paid) because we are not in position to install (or even pay for) extensions. We will probably resort to running "hours long" scripts, stressing our already painfully slow Jira, to get simple reports from it (fail :-)

Like Lenin Raj likes this
Thomas Kekeisen May 6, 2021

I also created an idea ticket to get an api that already returns the calculated sums. You may want to vote for it here: https://ideas.tempo.io/ideas/T-I-923

1 vote
Tiago Comasseto
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 17, 2012

Hi Yan,

As an alternative to REST API, you can retrieve this information with this SQL query.

I hope this helps.

Cheers

josefsabl March 17, 2014

We cannot access the database directly. That is why there is API in the first place.

Like # people like this
0 votes
Dario B
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 26, 2016
0 votes
josefsabl March 17, 2014

This is pretty common task which you typically want to do with the API as Jira GUI does not offer much to help with it. Yet, it is practically impossible.

More on that here: http://stackoverflow.com/questions/12776109/work-logs-for-a-period-from-jira-rest-api

I am looking into this problem for two days now and the only way to do this task seems to be to NOT USE API. The problem is that we can only use API or GUI.

Suggest an answer

Log in or Sign up to answer