Summary

For any number of reasons (low on disk space, getting ready to migrate to a new server, simple curiosity) you might want to get the total size of attachments uploaded to Confluence.

Environment

Confluence Server and Data Center

Solution

Confluence 5.7.x and above (tested on PostgreSQL and MSSQL)

• Size of all attachments:

  SELECT sum(LONGVAL) AS size_bytes
  FROM CONTENTPROPERTIES
  WHERE CONTENTID IN (SELECT CONTENTID
                        FROM CONTENT
                        WHERE CONTENTTYPE = 'ATTACHMENT')
  AND PROPERTYNAME = 'FILESIZE'
  ORDER BY sum(LONGVAL) DESC;

  Group by spaces:

  SELECT s.SPACEID,
         s.SPACENAME,
         sum(LONGVAL) AS size_bytes
  FROM CONTENTPROPERTIES c
  JOIN CONTENT co ON c.CONTENTID = co.CONTENTID
  JOIN SPACES s ON co.SPACEID = s.SPACEID
  WHERE c.CONTENTID IN (SELECT co.CONTENTID
                        FROM CONTENT
                        WHERE co.CONTENTTYPE = 'ATTACHMENT')
  AND c.PROPERTYNAME = 'FILESIZE'
  GROUP BY s.SPACENAME, s.SPACEID
  ORDER BY sum(LONGVAL) DESC;

  From a specific space:

  SELECT sum(LONGVAL) AS size_bytes
  FROM CONTENTPROPERTIES c
  JOIN CONTENT co ON c.CONTENTID = co.CONTENTID
  JOIN SPACES s ON co.SPACEID = s.SPACEID
  WHERE c.CONTENTID IN (SELECT co.CONTENTID
                        FROM CONTENT
                        WHERE co.CONTENTTYPE = 'ATTACHMENT')
  AND c.PROPERTYNAME = 'FILESIZE'
  AND s.SPACEKEY = '<SPACEKEY>'
  ORDER BY sum(LONGVAL) DESC;

  Ordered size of each one of the attachments:

  SELECT s.SPACENAME AS SpaceName,
         co2.TITLE AS PageTitle,
         co.TITLE AS AttachmentName,
         cp.LONGVAL AS Size_Bytes
  FROM CONTENTPROPERTIES cp
  JOIN CONTENT co ON cp.CONTENTID = co.CONTENTID
  JOIN SPACES s ON co.SPACEID = s.SPACEID
  JOIN CONTENT co2 ON co.PAGEID = co2.CONTENTID
  WHERE cp.CONTENTID IN (SELECT co.CONTENTID
                         FROM CONTENT
                         WHERE co.CONTENTTYPE = 'ATTACHMENT')
  AND cp.PROPERTYNAME = 'FILESIZE'
  ORDER BY s.SPACENAME, co2.TITLE, cp.LONGVAL DESC;

  Do note, Confluence stores file size in bytes. You will need to do some division to get the total size to KB (1,000 bytes) or MB (1,000,000 bytes).

Confluence 5.6.x and below

• Size of all attachments:

  SELECT sum(FILESIZE) FROM ATTACHMENTS;