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.

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

Solution

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 17, 2020

Was this helpful?

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