Database Script for Last Viewed per project

Rebekka Heilmann _viadee_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 7, 2024

We'd like to do a huge cleanup on a Data Center site (~1400 projects). We already have info on Leads, Admins, Created and Last updated. However, we'd like to add the info about the last time, any of the project's issue has been viewed.

I know that info on issues viewed is stored with each user. Does anyone have a script for finding the "Last Viewed" Date per project?

 

2 answers

0 votes
ramyaallena
Contributor
November 12, 2024

Hi @Rebekka Heilmann _viadee_ ,

 

Did the script worked? have you got the solution?

 

thanks,

Allena

0 votes
Hernan Montes
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 7, 2024

Hi @Rebekka Heilmann _viadee_ 

I don't have a test environment to test it, but try the following, I think it's a good start, it will go through users history, retrieve issue last seen and from which project, the return a list of issues with their last seen date (ideally), I would recommend closing the query to a couple of projects, as the results will be large:

 

SELECT 
p.pname AS project_name,
i.issuenum AS issue_number,
u.lower_user_name AS user_name,
MAX(uh.timestamp) AS last_viewed_date
FROM
project p
JOIN
jiraissue i ON p.id = i.project
JOIN
(
SELECT
issueid,
author AS user_id,
timestamp
FROM
changegroup
WHERE
id IN (
SELECT
changegroupid
FROM
changeitem
WHERE
field = 'status'
)
) uh ON i.id = uh.issueid
JOIN
app_user u ON uh.user_id = u.user_key
GROUP BY
p.pname, i.issuenum, u.lower_user_name
ORDER BY
p.pname, i.issuenum, u.lower_user_name;

 - Please share results!

Thank you,

Hernan 

Rebekka Heilmann _viadee_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 8, 2024

Thank you! I'll try that out asap - had some production issues this week so been busy, but will put it on my list and let you know.

Suggest an answer

Log in or Sign up to answer