How can I retrieve the timesheet data for non-project tasks from the Reports and Timesheets for Jira app on Jira Data Center?

You can encounter scenarios where retrieving timesheet data for non-project tasks is necessary for external use beyond timesheet reports. This article guides you on how to access this data.

Instructions

To retrieve the data, you can pull the contents of these tables in the Jira database:

app_user
AO_6B9F04_AIO_NON_PROJECT_HOUR
AO_6B9F04_AIO_NON_PROJECT_TASK
AO_6B9F04_AIO_USER

The following sample SQL query provides a list of non-project tasks.

The query returns the username, task summary, start date, and the time logged on each task.

SELECT a.lower_user_name, c."SUMMARY", d."STARTED", d."TIME_SPENT_SECONDS"
FROM "app_user" a, "AO_6B9F04_AIO_USER" b, "AO_6B9F04_AIO_NON_PROJECT_TASK" c, "AO_6B9F04_AIO_NON_PROJECT_HOUR" d
WHERE a.user_key = b."ACCOUNT_ID" AND b."ID" = d."AUTHOR_ID" AND c."ID" = d."NON_PROJECT_TASK_ID";

Avoid using any query other than simple SELECT statements in the database. Modifying the database's contents without the advice of the Appfire support team can lead to errors.
We recommend executing the query in a staging environment first and then in the prod environment outside business hours.