How to get more statistical data (disk space, contents created) from Confluence's usage.

Summary

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.

• How to find the largest Confluence pages by storage size in the database

Solution

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

   MS SQL

   1 2 3 4 5 6 7 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.

   1 2 3 4 5 6 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.

   1 2 3 4 5 6 7 8 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.

   1 2 3 4 5 6 7 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.

   1 2 3 4 5 6 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

   1 2 3 4 5 6 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;