Find who updated an issue recently in a project via the database
プラットフォームについて: Data Center のみ。 - This article only applies to Atlassian apps on the Data Center プラットフォーム。
この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。 Server* 製品のサポートは 2024 年 2 月 15 日に終了しました。Server 製品を実行している場合は、 アトラシアン Server サポート終了 のお知らせにアクセスして、移行オプションを確認してください。
*Fisheye および Crucible は除く
Please be mindful that the information below pertains to a service that is outside of Atlassian Support Scope. Any effort provided to support issues related to out of scope issues will be on a best-effort, as-is basis by the support engineer.
While this information can be checked in the Issue's Activity or History tabs, you may want to view this via the database. The query below can find the details on what was the recently updated issue for a project and who updated it along with the project, issue and user details.
Postgres
WITH jis AS (
select p.id as project_id,
p.pname as Project_Name,
p.Projecttype as ProjectType,
p.pkey as project_key,
jira.id as last_issue_tobe_updated,
jira.issuenum as issuenum,
p.Projecttype as Project_Type,
p.pcounter as Number_of_Issues,
p.lead as Project_Lead,
cu.email_address as projectlead_email,
jira.creator as creator,
Issuecounttbl.Issuecount as Issuecount,
jira.updated as Last_issue_update,
jira.created as Jira_created_date
from (select ji.id as id,
ji.issuenum as issuenum,
ji.project as project,
ji.creator as creator,
ji.created as created,
ji.updated as updated,
row_number() over (partition by project order by updated desc) as rno
from jiraissue ji) jira
join (select project,
count(*) as Issuecount
from jiraissue
group by project) Issuecounttbl on jira.project = Issuecounttbl.project
join Project p on jira.project = p.id
join app_user au on au.user_key = p.lead
join cwd_user cu on cu.lower_user_name = au.lower_user_name
where jira.rno = 1)
select final.Issueid,
final.Project_Name,
final.ProjectType,
final.project_key,
final.Project_Lead,
final.projectlead_email_address,
concat(final.project_key,'-',final.issuenum) as last_issue_tobe_updated,
final.last_user_to_update,
final.last_updated_time,
final.Number_of_Issues,
final.Last_update_Action
from (select tbltwo.*
from (select tblone.*,
row_number() over (partition by last_issue_tobe_updated order by last_updated_time desc) as ranknum
from
--Find the latest issue update from jiraissue if the issue was just created
(select jis.last_issue_tobe_updated as Issueid,
jis.Project_Name as Project_Name,
jis.ProjectType as ProjectType,
jis.project_key as project_key,
jis.last_issue_tobe_updated as last_issue_tobe_updated,
jis.issuenum as issuenum,
jis.creator as last_user_to_update,
jis.Jira_created_date as last_updated_time,
jis.Issuecount as Number_of_Issues,
jis.Project_Lead as Project_Lead,
jis.projectlead_email as projectlead_email_address,
jis.Last_issue_update as Last_issue_update,
'Issue Creation' as Last_update_Action
from jis
union
--Find the latest issue update from jiraaction if the issue had comments
select jis.last_issue_tobe_updated as Issueid,
jis.Project_Name as Project_Name,
jis.ProjectType as ProjectType,
jis.project_key as project_key,
jis.last_issue_tobe_updated as last_issue_tobe_updated,
jis.issuenum as issuenum,
jaction.author as last_user_to_update,
jaction.comment_updated_time as last_updated_time,
jis.Issuecount as Number_of_Issues,
jis.Project_Lead as Project_Lead,
jis.projectlead_email as projectlead_email_address,
jis.Last_issue_update as Last_issue_update,
'Comment Addition' as Last_update_Action
from jis
join (select jact.author as author,
jact.issueid as issueid,
jact.updated as comment_updated_time
from (select ja.author as author,
ja.issueid as issueid,
ja.updated, row_number() over (partition by issueid order by updated desc) as rno
from jiraaction ja) jact
where jact.rno = 1) jaction on jaction.issueid = jis.last_issue_tobe_updated
union
--Find the latest issue update from worklog if the last action on the table was adding worklog
select jis.last_issue_tobe_updated as Issueid,
jis.Project_Name as Project_Name,
jis.ProjectType as ProjectType,
jis.project_key as project_key,
jis.last_issue_tobe_updated as last_issue_tobe_updated,
jis.issuenum as issuenum,
wrklog.author as last_user_to_update,
wrklog.comment_updated_time as last_updated_time,
jis.Issuecount as Number_of_Issues,
jis.Project_Lead as Project_Lead,
jis.projectlead_email as projectlead_email_address,
jis.Last_issue_update as Last_issue_update,
'Worklog Addition' as Last_update_Action
from jis
join (select wlog.author as author,
wlog.issueid as issueid,
wlog.updated as comment_updated_time
from (select wl.updateauthor as author,
wl.issueid as issueid,
wl.updated, row_number() over (partition by issueid order by updated desc) as rno
from worklog wl) wlog
where wlog.rno = 1) wrklog on wrklog.issueid = jis.last_issue_tobe_updated
union
--Find the latest change history from change group from either from comment delete or issue transition or any other such activity.
select jis.last_issue_tobe_updated as Issueid,
jis.Project_Name as Project_Name,
jis.ProjectType as ProjectType,
jis.project_key as project_key,
jis.last_issue_tobe_updated as last_issue_tobe_updated,
jis.issuenum as issuenum,
cgroups.author as last_user_to_update,
cgroups.created as last_updated_time,
jis.Issuecount as Number_of_Issues,
jis.Project_Lead as Project_Lead,
jis.projectlead_email as projectlead_email_address,
jis.Last_issue_update as Last_issue_update,
'Change History Action' as Last_update_Action
from jis
join (select cgroup.author as author,
cgroup.issueid as issueid,
cgroup.created as created
from (select cg.author as author,
cg.issueid as issueid,
cg.created as created,
row_number() over (partition by issueid order by created desc) as rno
from changegroup cg join changeitem ci on ci.id=cg.id and ci.field not in ('WorklogId','timeestimate','timespent')) cgroup
where cgroup.rno = 1) cgroups on cgroups.issueid = jis.last_issue_tobe_updated
order by last_issue_tobe_updated) tblone) tbltwo
where tbltwo.ranknum = 1) final
order by final.Number_of_Issues desc;更新日時: September 25, 2025
この内容はお役に立ちましたか?
さらにヘルプが必要ですか?
アトラシアン コミュニティをご利用ください。