要約

ディスク領域の減少、新しいサーバーへの移行準備、単純な確認目的などのさまざまな理由で、Confluence にアップロードされた添付ファイルの合計サイズを確認したい場合があります。

環境

Confluence Server および Data Center

ソリューション

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;

  特定のスペースの場合:

  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;