How to find the space that is not used for last 2 years.

Question

I want to clean up the spaces in Confluence so I need a space list those are not used for last 2~3 years.

Is there any way including DB query to find unused space for a long time?

Regards, JJ

Jongho Jung · Answer

I found the answer from the Confluence Knowledge Base.

https://confluence.atlassian.com/confkb/how-can-i-find-which-spaces-have-been-recently-viewed-or-their-content-modified-1236937337.html
Summary
As an admin, you may want to get a list of all spaces, with their creation date, the last modified date (of any content) and the last viewed date of a content in a space.

Solution
Recently viewed pages and blogposts are stored in the AORECENTLY_VIEWED table in Confluence's database:

WITH Recently_Viewed AS (SELECT
"SPACE_KEY", MAX("LAST_VIEW_DATE") as "last_view_date"
FROM "AO_92296B_AORECENTLY_VIEWED"
GROUP BY 1)
SELECT s.spacename, s.spacekey, s.creationdate as "creation_date", MAX(c.lastmoddate) as "last_updated_date",r."last_view_date"
FROM content c
JOIN spaces s ON s.spaceid=c.spaceid
LEFT JOIN Recently_Viewed r ON r."SPACE_KEY" = s.spacekey
GROUP BY 1,2,3,5;