Purpose

• To find the total pages & blog post in each space
• To find which space is public-facing (anonymous access) and the total pages/blog posts in that space.

We can get these details after executing the below SQL in the Confluence-connected Database. 

Solution

• Total pages & blogpost in each space. Please run the below SQL in the Confluence-connected Database to get the desired results. 
   This doesn't include drafts or previous versions.

  select s.SPACENAME,s.SPACEKEY,c.CONTENTTYPE, count(*) as Total
  from content c join spaces s on c.SPACEID=s.SPACEID 
  where c.CONTENTTYPE in ("PAGE","BLOGPOST") AND c.PREVVER is NULL 
  group by s.SPACENAME,c.CONTENTTYPE order by Total desc

• Total pages & blogpost in each public-facing space (anonymous access), Please run the below SQL in the Confluence-connected Database to get the desired results. 
   This doesn't include pages/blogs that the permissions (viewing) are specifically restricted 

  select s.SPACENAME,s.SPACEKEY,c.CONTENTTYPE, count(*) as Total 
  from content c join spaces s on c.SPACEID=s.SPACEID 
  where c.CONTENTTYPE in ("PAGE","BLOGPOST") AND c.PREVVER is NULL AND s.SPACEKEY in 
  (select s.SPACEKEY from spaces s join spacepermissions sp on s.SPACEID=sp.SPACEID where PERMTYPE="VIEWSPACE" AND PERMGROUPNAME is NULL AND PERMUSERNAME is NULL) 
  group by s.SPACENAME,c.CONTENTTYPE 
  ORDER by Total desc