How to get list of worklogs through JIRA REST API

[Yan Yan]

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?

[Denis Blanchette · Accepted solution]

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]

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

[Paul van den Dool]

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.

[Marius Storm-Olsen]

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]

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.

[josefsabl]

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?

[Oleg]

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]

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

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

[Adam Labus]

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

[Bjarni Thorbjornsson]

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]

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 :-)

[Thomas Kekeisen]

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

[Tiago Comasseto]

Hi Yan,

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

I hope this helps.

Cheers

[josefsabl]

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

[Dario B]

This is being fixed:

https://jira.atlassian.com/browse/JRA-34746

[josefsabl]

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.