How to find the number of pages, blogposts, and attachments

Still need help?

The Atlassian Community is here for you.

Ask the community

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.

This can help identify spaces and parent pages that are getting too large and should bit split up to improve performance in the instance.

Solution

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;

(warning)  Note: As these queries were written for Postgres, they may need to be updated for other kinds of databases.

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'
group by SPACES.SPACENAME
order by "number of pages" desc;

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'
group by SPACES.SPACENAME
order by "number of pages" desc;

Number of pages in the entire instance:

select count(*) from content
where contenttype='PAGE' 
and prevver is null 
and content_status='current';

Number of blog posts in the entire instance:

select count(*) from content
where contenttype='BLOGPOST'
and prevver is null
and content_status='current';

Number of attachments in a space:

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 Jul 18, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.