Table: jira_issue_worklog - Query Jira Issue Worklogs using SQL

Jira is a project management tool that enables teams to plan, track, and manage their projects and tasks. Issue Worklogs in Jira represent the work done and the time spent on each issue. This information is crucial for tracking project progress, understanding individual contributions, and managing overall project timelines.

Table Usage Guide

The jira_issue_worklog table provides insights into the work done and the time spent on each issue in Jira. As a project manager or team leader, explore issue-specific details through this table, including the time spent, the work done, and the associated metadata. Utilize it to track project progress, understand individual contributions, and manage project timelines effectively.

Examples

Basic info

Explore the work logs of different issues in Jira to understand who has made contributions and the nature of their input. This could be useful for tracking team progress, identifying bottlenecks, and understanding individual work patterns.

select
  id,
  self,
  issue_id,
  comment,
  author
from
  jira_issue_worklog;

select
  id,
  self,
  issue_id,
  comment,
  author
from
  jira_issue_worklog;

Get time logged for issues

Explore which issues have had the most time logged on them. This can help prioritize resources by identifying which issues are consuming more time.

select
  issue_id,
  sum(time_spent_seconds) as total_time_spent_seconds
from
  jira_issue_worklog
group by
  issue_id;

select
  issue_id,
  sum(time_spent_seconds) as total_time_spent_seconds
from
  jira_issue_worklog
group by
  issue_id;

Show the latest worklogs for issues from the past 5 days

Explore the recent workload by identifying issues that have been active in the past 5 days. This can help in assessing the work distribution and identifying potential bottlenecks in real-time.

select
  id,
  issue_id,
  time_spent,
  created
from
  jira_issue_worklog
where
  created >= now() - interval '5' day;

select
  id,
  issue_id,
  time_spent,
  created
from
  jira_issue_worklog
where
  created >= datetime('now', '-5 days');

Retrieve issues and their worklogs updated in the last 10 days

Analyze the settings to understand the recent workload changes and their impact on project priorities. This query is useful for tracking the progress of projects and tasks that have been updated or modified in the last 10 days.

select
  distinct w.issue_id,
  w.id,
  w.time_spent,
  w.updated as worklog_updated_at,
  i.duedate,
  i.priority,
  i.project_name,
  i.key
from
  jira_issue_worklog as w,
  jira_issue as i
where
  i.id like trim(w.issue_id)
  and w.updated >= now() - interval '10' day;

select
  distinct w.issue_id,
  w.id,
  w.time_spent,
  w.updated as worklog_updated_at,
  i.duedate,
  i.priority,
  i.project_name,
  i.key
from
  jira_issue_worklog as w,
  jira_issue as i
where
  i.id like trim(w.issue_id)
  and w.updated >= datetime('now', '-10 days');

Get author information of worklogs

Discover the segments that allow you to gain insights into the authors of worklogs, such as their account type, name, email address, and time zone. This is particularly useful for understanding who is contributing to specific issues and their geographical location.

select
  id,
  issue_id,
  author ->> 'accountId' as author_account_id,
  author ->> 'accountType' as author_account_type,
  author ->> 'displayName' as author_name,
  author ->> 'emailAddress' as author_email_address,
  author ->> 'timeZone' as author_time_zone
from
  jira_issue_worklog;

select
  id,
  issue_id,
  json_extract(author, '$.accountId') as author_account_id,
  json_extract(author, '$.accountType') as author_account_type,
  json_extract(author, '$.displayName') as author_name,
  json_extract(author, '$.emailAddress') as author_email_address,
  json_extract(author, '$.timeZone') as author_time_zone
from
  jira_issue_worklog;

Schema for jira_issue_worklog

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
author	jsonb		Information about the user who created the worklog entry, often including their username, display name, and user account details.
comment	text		Any comments or descriptions added to the worklog entry.
created	timestamp with time zone		The date and time when the worklog entry was created.
id	text	=	A unique identifier for the worklog entry.
issue_id	text	=	The ID of the issue.
properties	jsonb		The properties of each worklog.
self	text		The URL of the worklogs.
started	timestamp with time zone		The date and time when the worklog activity started.
time_spent	text		The duration of time logged for the task, often in hours or minutes.
time_spent_seconds	bigint		The duration of time logged in seconds.
title	text		Title of the resource.
update_author	jsonb		Details of the user who last updated the worklog entry, similar to the author information.
updated	timestamp with time zone		The date and time when the worklog entry was last updated.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- jira

You can pass the configuration to the command with the --config argument:

steampipe_export_jira --config '<your_config>' jira_issue_worklog