find recently viewed or modified spaces and their content in Confluence
Platform Notice: Cloud and Data Center - This article applies equally to both cloud and data center platforms.
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
1
2
3
4
5
6
7
8
9
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
1
2
3
4
5
6
7
8
9
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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?