Getting a list of Pages with most Historic Versions in Confluence Data Center
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server 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 described in the Page History and Page Comparison Documentation, Confluence tracks the history of changes to each page by creating a new version of the page each time it's modified, allowing users to view the changes between different versions, and roll back to a previous version upon need. The historic entries are actually pages that remain stored in the database and sometimes, Confluence instances may have several historic pages saved in the backend, which may not be in use and that are not automatically purged.
As an administrator, you would like to get a list of how many versions exist for a specific page in a your different spaces.
If you have Confluence Data Center, remaining versions will not be renumbered when a version is deleted. Older versions may also be deleted automatically, if your administrator has defined retention rules for the site or space.
Environment
Confluence 6.2.1 and above
Purpose
Run the following SQL statement to list the current number of versions of a specific page, and the latest/current version on the page/space:
WITH VERSIONED_PAGES AS(
SELECT c1.contentid as current_contentid ,c1.title as current_title ,c1.version current_version,c1.spaceid current_spaceid,c2.contentid previous_contentid,c2.title previous_title
FROM CONTENT c1
JOIN CONTENT c2 ON c1.contentID = c2.prevver
WHERE c1.CONTENTTYPE = 'PAGE'
)
SELECT MAX(current_version) AS "Latest Page Version", COUNT(current_version) AS "Total Page Versions", current_title AS "Current Page Title", s.spacekey AS "Space Key"
FROM VERSIONED_PAGES vp
JOIN SPACES s ON s.spaceid=vp.current_spaceid
GROUP BY current_title, s.spacekey
ORDER BY 2 DESC
Run the following SQL statement to list the total number of versions in your spaces:
WITH VERSIONED_PAGES AS(
SELECT c1.contentid as current_contentid ,c1.title as current_title ,c1.version current_version,c1.spaceid current_spaceid,c2.contentid previous_contentid,c2.title previous_title
FROM CONTENT c1
JOIN CONTENT c2 ON c1.contentID = c2.prevver
WHERE c1.CONTENTTYPE = 'PAGE'
)
SELECT COUNT(current_contentid) AS "Total Versions in Space", s.spacekey
FROM VERSIONED_PAGES vp
JOIN SPACES s ON s.spaceid=vp.current_spaceid
GROUP BY s.spacekey
ORDER BY 1 DESC
If you want all the pages and their historical version count per space, run the following query:
SELECT content.title, content.version
FROM content
INNER JOIN spaces
on content.spaceid = spaces.spaceid
WHERE content.contenttype = 'PAGE'
and content.prevver is null
and content.content_status = 'current'
and spaces.spacename ='<SPACENAME>'
ORDER BY 2 DESC; If you are looking to list the pages that has historic versions older than X days, run the following query:
select distinct parentid
from "content"
where content_status !='current'
and contenttype ='PAGE'
and (now()::date - creationdate::date) > X ;