How can I find which Spaces have been recently viewed or their content modified?
Platform Notice: Data Center and Cloud By Request - This article was written for the Atlassian data center platform but may also be useful for Atlassian Cloud customers. If completing instructions in this article would help you, please contact Atlassian Support and mention it.
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
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.
There currently isn't a way to export this information directly from the Confluence via the UI.
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 AO_92296B_AORECENTLY_VIEWED r ON r.SPACE_KEY = s.spacekey
GROUP BY 1,2,3,5;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;WITH Recently_Viewed AS (
SELECT
"SPACE_KEY",
MAX("LAST_VIEW_DATE") AS "Space_Last_View_Date"
FROM
AO_92296B_AORECENTLY_VIEWED
GROUP BY
"SPACE_KEY"
)
SELECT
s.spacename,
s.spacekey,
s.creationdate AS "Space_Creation_Date",
MAX(c.lastmoddate) AS "Space_Last_Updated_Date(Page_Created Or Page_Modified)",
r."Space_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
s.spacename,
s.spacekey,
s.creationdate,
r."Space_Last_View_Date";
Note that this table by nature tracks recency and does not contain all historical data for page/space views. If you are looking to track overall page/space views, you will need to consult the Atlassian Marketplace for plugins that may extend this functionality in Confluence.