Database Script for Last Viewed per project

Rebekka Heilmann (viadee)

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?

 

Hernan Montes_ay1pm6

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