Purpose

If your instance is taking too much disk space or you'd like to check your attachment sizes to enforce some restrictions, you might want to check which users or pages have the biggest attachments. For versions of Confluence 5.4 or earlier you might have attachments stored in the database, otherwise they will be stored in your <confluence_home>/attachments/ver003 folder unless you upgraded from a previous version and did not migrate the attachments over to the filesystem.

It's easy to check by going to  -> General configuration -> Attachment Storage.

Solution 

The examples below are for a PostgreSQL database, but your DBA can translate them to another DB engine if needed.

• If your attachments are stored in the database, you can run the following query which will return the size of the attachment in bytes, the attachmentid, title, user that uploaded, and the page to which it was attached:

  SELECT octet_length(a.data) AS SIZE,
         a.attachmentid,
         c.title AS attachmentTitle,
         u.username AS guilty,
         co.title AS pageTitle
  FROM ATTACHMENTDATA AS a
  JOIN CONTENT AS c ON c.contentid = a.attachmentid
  JOIN USER_MAPPING AS u ON u.user_key = c.creator
  JOIN CONTENT AS co ON c.pageid = co.contentid
  ORDER BY SIZE DESC

• If you are storing the attachments in the file system, you can run this slightly modified query:

  
  

  SELECT DISTINCT c.contentid,
  				c.spaceid,
                  c.title AS attachmentTitle,
                  u.username AS uploadedBy,
                  co.title AS pageTitle,
                  cn.longval AS bytes,
                  s.spacekey as spacekey,
                  s.spacename as spacename
  FROM CONTENT AS c
  JOIN USER_MAPPING AS u ON u.user_key = c.creator
  JOIN CONTENT AS co ON c.pageid = co.contentid
  JOIN spaces AS s ON c.spaceid=s.spaceid
  JOIN CONTENTPROPERTIES AS cn ON cn.contentid = c.contentid
  WHERE c.contenttype = 'ATTACHMENT'
    AND cn.longval IS NOT NULL
    AND  cn.propertyname = 'FILESIZE'
  ORDER BY cn.longval DESC;