How to obtain user information for the worklog database table on Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When searching for entries directly at the worklog database table, users can only obtain the user key (e.g. JIRAUSER1000) from the accounts that are associated with worklog entries, which normally do not match the actual user name from the account.
Environment
Jira Data Center on any version from 8.0.0
Solution
The following SQL query can be used to obtain both the user name and display name for each worklog entry:
⚠️ This query was written for PostgreSQL and may have to be updated to work on other database types:
1
2
3
4
5
6
SELECT p.pkey,i.issuenum,u.user_name, u.display_name, w.*
FROM cwd_user u
JOIN app_user a ON a.lower_user_name = u.lower_user_name
JOIN worklog w ON a.user_key = w.author
JOIN jiraissue i ON i.id=w.issueid
JOIN project p on i.project=p.id;Sample output:
1
2
3
4
pkey issuenum user_name display_name id issueid author grouplevel rolelevel worklogbody created updateauthor updated startdate timeworked
---- -------- --------- ------------ ----- ------- ------------- ---------- --------- ----------- ------------------- ------------- ------------------- ------------------- ----------
SP 1 admin Local Admin 10100 10000 JIRAUSER10000 (null) (null) testing 2022-03-30 11:20:14 JIRAUSER10000 2022-03-30 11:20:14 2022-01-30 11:20:00 600
SP 1 admin Local Admin 10000 10000 JIRAUSER10000 (null) (null) 2022-03-29 10:06:26 JIRAUSER10000 2022-03-29 10:06:26 2022-03-22 10:06:00 6660 ℹ️ Consider adding a WHERE clause to the query to filter only the needed users, as the worklog table typically has many entries.
Was this helpful?