Purpose

1. To know how much disk space each Confluence's space consumes.
    This KB calculates the size of attachments only for each space as it contributes to the majority of the disk space a Confluence's space consumes. This doesn't include the contents of the space.
   • This is useful if you have a limited amount of disk space, and you wish to identify and reduce the size of unnecessary spaces.
   • This is also useful when you're performing an XML backup, but it is too large and you wish to reduce it.
2. To monitor each user's usage in Confluence.
   • The disk space that's consumed by each user is calculated by the size of attachments that he's uploaded.
   • The contents (page and blogposts) that he's created in Confluence

There's a related KB that calculates the largest Confluence pages by storage size in the database.

• https://confluence.atlassian.com/confkb/how-to-find-the-largest-confluence-pages-by-storage-size-in-the-database-800862464.html

Solution

1. To get the total size of attachments (in byte) in all pages / blogs in each space.
   
   

   MS SQL

   SELECT c1.SPACEID,s.SPACEKEY,s.SPACENAME,SUM(cp.LONGVAL) as "size (byte)"
   FROM CONTENT c1 JOIN CONTENT c2 ON c1.CONTENTID=c2.PAGEID
   JOIN CONTENTPROPERTIES cp ON c2.CONTENTID=cp.CONTENTID
   JOIN SPACES s on c1.SPACEID=s.SPACEID
   WHERE c2.CONTENTTYPE='ATTACHMENT'
   AND cp.propertyname='FILESIZE' 
   GROUP BY c1.SPACEID,s.SPACEKEY,s.SPACENAME;

   Example

   If you get a result of "18701747" for the size (byte).

   1Kilobyte (KB) = 1024 byte
   1Megabyte (MB) = 1024 KB

   The total size of attachments is 18701747/1024/1024 = 17.83 MB

2. To find the number of contents (page and blogs) created by each user.

   select 
   u.lower_username,count(u.lower_username) 
   from content c join user_mapping u on c.CREATOR=u.user_key 
   where c.CONTENTTYPE in ('PAGE','BLOGPOST') AND c.PREVVER is NULL 
   group by u.lower_username 
   order by count(u.lower_username) DESC
   

3. To find the total attachment size uploaded by each user in all pages / blogs.

   select
   u.lower_username, sum(cp.longval) as "size (byte)"
   from content c1 join content c2 on c1.contentid = c2.pageid
   join user_mapping u on c1.creator=u.user_key
   join contentproperties cp on c2.contentid = cp.contentid
   where c2.contenttype='ATTACHMENT'
   group by u.lower_username
   order by sum(cp.longval) desc
   

4. To find the total attachment size uploaded by each user. This includes attachments in all pages, blogs, comments, profile pictures.

   select
   u.lower_username, sum(cp.longval) as "size (byte)"
   from content c1 join user_mapping u on c1.creator=u.user_key
   join contentproperties cp on c1.contentid = cp.contentid
   where c1.contenttype='ATTACHMENT'
   group by u.lower_username
   order by sum(cp.longval) desc
   

5. To find the total sum of the content of pages per space, not including attachments.

   SELECT s.SPACEKEY, SUM(DataLength(bc.BODY))
   FROM BODYCONTENT bc
   JOIN CONTENT c ON bc.CONTENTID = c.CONTENTID
   JOIN SPACES s ON c.SPACEID = s.SPACEID
   WHERE c.CONTENTTYPE IN ('BLOGPOST','PAGE')
   GROUP BY s.SPACEKEY;

   MS SQL

   SELECT s.SPACEKEY,s.SPACENAME,SUM(CAST(DATALENGTH(b.BODY) as bigint)) as "size (byte)"
   FROM CONTENT c
   JOIN SPACES s ON c.SPACEID=s.SPACEID
   JOIN BODYCONTENT b ON b.CONTENTID=c.CONTENTID
   WHERE c.CONTENTTYPE IN ('PAGE','BLOGPOST')
   GROUP BY s.SPACEKEY,s.SPACENAME;