Find who updated an issue recently in a project via the database
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
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
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;Updated on April 8, 2025
Was this helpful?
Still need help?
The Atlassian Community is here for you.