How to find the largest attachment files in your Confluence instance

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

目的

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.


ソリューション 

(info) The examples below are for a PostgreSQL database, but can be translated by your DBA to another DBMS type 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;

Last modified on Mar 21, 2024

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.