Jira Work log and Linked issues data available in Atlassian Analytics

[#1]

Hi, community!

We’re excited to announce that Jira work log and linked issues data are now available in Atlassian Analytics. You’ll find two new tables under “Jira family of products”:

• Issue worklog

• Issue link

The “Issue worklog” table lets you drill deeper into development velocity by breaking resource allocation down by individual and their exact time spent on an issue. This table stores a record for each time that work is logged by someone.

The “Issue link” table lets you see connections across projects or teams and quantify dependencies. This table contains all issues that have a linked issue and details about the link relationships.

We’ll gradually roll out the data in the coming week.

Read our schema documentation for more details about the new tables.

🎥 Check out our video example

In this video, I walk through the following examples:

• How to calculate total time spent on an issue

• How to see time logged by person

• How to combine cross-project blockers into one view

Examples of using the “Issue worklog” table

Total time spent on an issue

To get the total time logged for an issue, you can use the “Time spent (seconds)” column. Use the “Total sum” aggregation on this column as there may be multiple records for each issue. “Started at” is the starting point for each logged time.

Work log contributors

To get the names of those who added to or updated an issue’s work log, you can use the “Authored by” and “Updated by” columns, respectively, to join this table with the “Account” table. To get the account name associated with the “Authored by” or “Updated by” columns, you can join To get “Account name”, you can join the table with the “Account” table using the “Account ID” column.

Converting the time format

To convert the logged time into the same format that you see in the “Time tracking” field or the work log in Jira, you can use a series of Visual SQL steps. Check out our knowledge base article for all the details.

Joining with other issue-related tables

To get attributes associated with each issue, you can join the table with other issue-related tables using “Issue ID”. For example, you can join it with the “Issue” table to get the issue key.

Example of using the “Issue link” table

To combine the necessary attributes of both issues and linked issues into a single view, such as projects that are blocked or being blocked, you'll need to use multiple queries in Visual SQL.

The following example shows how using two queries can show which projects are blocking our “JT” project and vice versa. The first query gets a list of linked issues in our “JT” project that use the link name “Blocks”. This would return linked issues that have either “blocks” or “is blocked by” relationships. The second query gets the details about those linked issues, including which projects they’re in.

Things to keep in mind

• In the schema browser, toggle on Show full schema to see all the columns in the new tables

• In rare cases, a work log record or a linked issue may not show in the Data Lake. This happens when certain field values exceed the size limit. See this bug ticket for more details.

How to get the new tables

For new Data Lake connections that include Jira data, these new tables will automatically be available.

For existing Data Lake connections, an organization admin needs to edit the connections. If the connection already includes Jira data, the organization admin can edit the connection and save it without making any changes. More about editing Data Lake connections.

Tell us what you think

Comment below or contact support if you have any questions or concerns. Thanks!

[Dwight Thompson]

Is the new Issue link table available for data shares? If so, is the new table automatically included in an existing data share? Or does the data share need to be edited to include the new table? I don't see the new Issue link table from within Power BI using my existing data share to the Atlassian Data Lake.

[Tina Ling]

Hi @Dwight Thompson ,  the issue link table is not available on Data Share yet. 

cc:  @Claire Gayrard 

[Bala Muthusamy]

Any ETA on when issue link data will be available on Data Share @Dwight Thompson 

[Dwight Thompson]

I really appreciate all the work being done related to Atlassian Analytics. Thank you!

[Claire Gayrard]

Hi @Dwight Thompson and @Bala Muthusamy

We are working on bringing those two tables into data share soon.

Stay tuned.

Claire

[Richard White _TechTime_]

@Claire Gayrard we have customers that are interested in Tempo data in analytics as well.

The main reason Tempo worklogs are required is that the Jira worklog data does not have correct author information (for all worklogs the author is the Tempo app user), so its impossible to do any useful analysis of time spent by user / team etc. etc.

There are also other details that make it awkward such as Tempo's aggregated worklog feature, which means that all worklogs from a day (from many users) are aggregated into a single Jira work log (even if there are 100's or 1000's of real work logs).

Other small details such as Tempo's worklog attributes are also useful, but can be worked around, they typically are used to group worklogs by some key such as customer, cost code etc.

[Tina Ling]

Hi @Richard White _TechTime_ ,  thank you for sharing the use cases.   It would be great if you can vote for this feature request ticket.  We do give update through these feature requests. 
https://jira.atlassian.com/browse/ANALYTICS-70