How do I list all attachments in Confluence with their location and file sizes?

Still need help?

The Atlassian Community is here for you.

Ask the community

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

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.