find recently viewed or modified spaces and their content in Confluence
Platform Notice: Data Center Only - This article only applies to Atlassian apps 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
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 blog posts are stored in the AORECENTLY_VIEWED table in Confluence's database:
MySQL
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;PostgreSQL
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;Oracle
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.
Was this helpful?