Purpose

This page contains a few way to search and list out all attachments in Confluence that has a specific extension or file type.

In this example, we are searching for all attachment with the extension .png and other scenario involving .xls 

Solution

From the UI - Use Confluence's search syntax

• Go to Search > AdvancedSearch

• Choose Attachment in the Of Type section

• Use the following search syntax to search the desired attachment type

  /.*<attachment type>.*/          Example: /.*png.*/

  

From the Database:

Use the following SQL query:

select c.TITLE as Attachment_Name, 
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 SPACES s on c2.SPACEID = s.SPACEID
where c.CONTENTTYPE = 'ATTACHMENT' and c.title like '%.png%';

The result will be as follows

Another example SQL query to retrieve Confluence Pages containing a type of an attachments, e.g. XLS in this case : 

select c.contentid as pageID, c.title as PageName, s.spacename from content c, spaces s
where 
c.spaceid = s.spaceid AND
c.contentid in (select c.pageid from content c 
where c.contenttype='ATTACHMENT'
AND c.title LIKE '%.xls%');

Additionally, in relation to the aforementioned SQL query, to print attachment name as well use following SQL query

select s.spacename, c.pageid, c.contentid,c.contenttype ,c.title as attachment_name from content c ,spaces s
where 
c.spaceid = s.spaceid 
AND c.contenttype='ATTACHMENT'
AND c.title LIKE '%.xls%';

 The queries are validated with Postgres database so they may need slight modification for other database types. 

From the Attachment folder:

Use the following unix search syntax:

find /<confluence_home>/attachments -type f | xargs file | grep PNG

 Will only work on some specific platforms.