How to find when and by whom an Issue was browsed or viewed in Jira
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
Summary
Admins may want to list which Issues a specific user has been browsing (viewing) or by whom and when a specific Issue was browsed for the last time.
This may be useful for auditing purposes or troubleshooting errors or performance issues.
Solution
Every time an Issue's viewed through the Browser, the respective record for the logged in user and Issue is updated in the userhistoryitem table.
This only happens for Browser activity — fetching the Issue through the REST API doesn't update the userhistoryitem records.
The table contains the last viewed time (in Epoch milliseconds) for each Issue and User. It doesn't hold historical data — only the most recent activity for that Issue and User.
Layout of userhistoryitem
1
2
3
4
id | entitytype | entityid | username | lastviewed | data
-------+------------+----------+----------+---------------+------
10319 | Issue | 10000 | admin | 1679062466321 |
10313 | Issue | 10033 | admin | 1679062371273 | Example "universal" query:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
h.entityid as "Issue Id",
concat(p.pkey, concat('-', i.issuenum)) as "Issue Key",
h.username,
h.lastviewed as "lastviewed (Epoch Time)",
h.data
from
userhistoryitem h
left join
jiraissue i on h.entitytype = 'Issue'
and i.id = cast(h.entityid as integer)
left join
project p on p.id = i.project
where
h.entitytype = 'Issue'
/* and h.lastviewed > 1679011200000 */
/* and h.username in ('user1', 'user2', 'user3') */
/* and h.entityid in ('10000', '10344', '13768') */
order by
h.lastviewed desc,
h.entityid asc
;Additional — and actually recommended — clauses:
Lastviewed (line 16): You may filter records only after (or before) a specific date. Epochconverter.com is a nice free online tool to convert dates to Epoch time. (1679011200000, for example, corresponds to March 17, 2023 00:00:00 UTC)
Username (line 17): If you're looking for a specific user's accesses, you may narrow the results down by this clause
Issue (line 18): If you're interested in only a single or a few Issues, you may filter them by this
Sample output:
1
2
3
4
5
6
Issue Id | Issue Key | username | lastviewed (Epoch Time) | data
----------+-----------+----------+-------------------------+------
10000 | SCRUM-1 | admin | 1679062466321 |
10022 | SCRUM-23 | admin | 1679062395929 |
10021 | SCRUM-22 | admin | 1679062395333 |
10020 | SCRUM-21 | admin | 1679062394835 | Converting Epoch time to user-friendly formats
Each DB type has it's own syntax for converting Epoch time to user-friendly date formats. You may replace line 5 from the "universal query" above by the one corresponding to your DB:
Line 5 that should be replaced
1
h.lastviewed as "lastviewed (Epoch Time)", For Postgres
1
to_timestamp(h.lastviewed/1000) as "lastviewed (formatted)",For MySQL
1
from_unixtime(floor(h.lastviewed/1000)) as "lastviewed (formatted)",For Oracle
1
TO_CHAR(CAST(DATE '1970-01-01' + (1/24/60/60/1000) * h.lastviewed AS TIMESTAMP), 'MM/DD/YYYY HH24:MI:SS') as "lastviewed (formatted)",For SQLServer
1
DATEADD(second, h.lastviewed/1000,'1970/1/1') as "lastviewed (formatted)",Sample output
This is a sample output from Postgres:
1
2
3
4
Issue Id | Issue Key | username | lastviewed (formatted) | data
----------+-----------+----------+----------------------------+------
10000 | SCRUM-1 | admin | 2023-03-17 14:14:26.321+00 |
10022 | SCRUM-23 | admin | 2023-03-17 14:13:15.929+00 | Appendix: updated Issues
On this same subject, this is a sample query we can use to check when an Issue has been updated or which Issues a specific user has updated:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
cg.issueid as "Issue Id",
concat(p.pkey, concat('-', i.issuenum)) as "Issue Key",
cg.author,
cg.created
from
changegroup cg
left join
jiraissue i on i.id = cg.issueid
left join
project p on p.id = i.project
where
cg.issueid in (10010, 10011, 10012)
and cg.author in ('admin')
order by
cg.created desc
;Change lines 13 and 14 to restrict the query to certain Issue Ids or usernames.
Sample output:
1
2
3
4
5
Issue Id | Issue Key | author | created
----------+-----------+--------+----------------------------
10011 | SCRUM-12 | admin | 2021-03-28 12:18:05.529+00
10010 | SCRUM-11 | admin | 2021-03-27 14:53:05.528+00
10012 | SCRUM-13 | admin | 2021-03-24 02:51:05.529+00Was this helpful?