How do I list all attachments in Confluence with their location and file sizes?
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Purpose
To get a list of all attachments in Confluence with a link to the page they are attached to and file sizes.
Solution
Run one of the below queries into your Confluence Database:
For Confluence 5.6 and below:
MySQL
select a.TITLE as "Attachment Name",
a.FILESIZE,
c.TITLE as "Page Title",
s.SPACENAME as "Space Name",
concat("http://<confluence_base_url>/pages/viewpageattachments.action?pageId=", a.PAGEID) as "Location"
from ATTACHMENTS a
join CONTENT c on a.PAGEID = c.CONTENTID
join SPACES s ON c.SPACEID = s.SPACEID
order by a.FILESIZE desc;
PostgreSQL
select a.TITLE as Attachment_Name,
a.FILESIZE as Attachment_Size,
s.SPACENAME as Space_Name,
c.TITLE as Page_Title,
'http://<confluence_base_url>/pages/viewpageattachments.action?pageId='||a.PAGEID as Location
from ATTACHMENTS a
join CONTENT c on a.PAGEID = c.CONTENTID
join SPACES s on c.SPACEID = s.SPACEID
order by a.FILESIZE desc; For Confluence 5.7 and above:
MySQL
select
c.TITLE as "Attachment Name",
cp.LONGVAL as "File Size",
c2.TITLE as "Page Title",
s.SPACENAME as "Space Name",
concat("http://<confluence_base_url>/pages/viewpageattachments.action?pageId=", c.PAGEID) as "Location"
from CONTENT c
join CONTENT c2 on c.PAGEID = c2.CONTENTID
join CONTENTPROPERTIES cp on c.CONTENTID = cp.CONTENTID
join SPACES s on c2.SPACEID = s.SPACEID
where c.CONTENTTYPE = 'ATTACHMENT' and cp.PROPERTYNAME = 'FILESIZE'
order by cp.LONGVAL desc;
PosgreSQL
select c.TITLE as Attachment_Name,
cp.LONGVAL as Attachment_Size,
s.spacename,
c2.TITLE as Page_Title,
'http://<confluence_base_url>/pages/viewpageattachments.action?pageId='||c.PAGEID as Location
from CONTENT c
join CONTENT c2 ON c.PAGEID = c2.CONTENTID
join CONTENTPROPERTIES cp on c.CONTENTID = cp.CONTENTID
join SPACES s on c2.SPACEID = s.SPACEID
where c.CONTENTTYPE = 'ATTACHMENT' and cp.PROPERTYNAME = 'FILESIZE'
order by cp.LONGVAL desc;Replace <confluence_base_url> with your Confluence Base URL . This was tested against MySQL and PostgreSQL.
Last modified on Jan 9, 2023
Powered by Confluence and Scroll Viewport.