How to find the number of pages, blogposts, and attachments
Purpose
It can be useful to know how many pages exist in each of your spaces or to know the number of child pages, attachments and comments are associated with a parent page.
The following sql queries can help identify spaces and parent pages that are getting too large and should bit split up to improve performance in the instance. Do note that you may need to modify these queries for specific databases.
Solution
The number of child pages, attachments, and comments for a given parent page:
drop table if exists tempdata;
create table tempdata as
(select contentid from content where
contentid in (select contentid from content where lowertitle = '<the-page-title-lowercased>') or
parentid in (select contentid from content where lowertitle = '<the-page-title-lowercased>'));
select count(*), contenttype from content where
pageid in (select contentid from tempdata)or
contentid in (select contentid from tempdata) group by contenttype;
Note: As these queries were written for Postgres, they may need to be updated for other kinds of databases.
The number of pages in a space:
select count(CONTENTID) as "number of pages", SPACES.SPACENAME from CONTENT
join SPACES on CONTENT.SPACEID = SPACES.SPACEID
where CONTENT.SPACEID is not null
and CONTENT.PREVVER is null
and CONTENT.CONTENTTYPE = 'PAGE'
and CONTENT.CONTENT_STATUS='current'
group by SPACES.SPACENAME
order by "number of pages" desc;The number of blogposts in a space:
select count(CONTENTID) as "number of pages", SPACES.SPACENAME from CONTENT
join SPACES on CONTENT.SPACEID = SPACES.SPACEID
where CONTENT.SPACEID is not null
and CONTENT.PREVVER is null
and CONTENT.CONTENTTYPE = 'BLOGPOST'
and CONTENT.CONTENT_STATUS='current'
group by SPACES.SPACENAME
order by "number of pages" desc;The number of pages in the entire instance:
select count(*) from content
where contenttype='PAGE'
and prevver is null
and content_status='current';The number of blog posts in the entire instance:
select count(*) from content
where contenttype='BLOGPOST'
and prevver is null
and content_status='current';The number of comments in the entire instance:
select count(*) from content
where contenttype='COMMENT'
and prevver is null
and content_status='current';The number of attachments in the entire instance:
select count(*) from content
where contenttype='ATTACHMENT'
and prevver is null
and content_status='current';Total number of spaces in Confluence:
select count(spacename) from spaces;The number of attachments in a space: This count will include deleted attachments if not purged from space tools
Pre 5.7:
select count(ATTACHMENTID) as "number of attachments", SPACES.SPACENAME from CONTENT
join SPACES on CONTENT.SPACEID = SPACES.SPACEID
join ATTACHMENTS on ATTACHMENTS.PAGEID = CONTENT.CONTENTID
where CONTENT.SPACEID is not null
group by SPACES.SPACENAME
order by "number of attachments" desc;Post 5.7:
select count(CONTENTID) as "number of attachments", SPACES.SPACENAME from CONTENT
join SPACES on CONTENT.SPACEID = SPACES.SPACEID
where CONTENT.SPACEID is not null
and CONTENT.PREVVER is null
and CONTENT.CONTENTTYPE = 'ATTACHMENT'
group by SPACES.SPACENAME
order by "number of attachments" desc;
Last modified on Jan 30, 2023
Powered by Confluence and Scroll Viewport.