How to search specific attachment type in Confluence
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
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
1/.*<attachment type>.*/ Example: /.*png.*/
From the Database:
Use the following SQL query:
1
2
3
4
5
6
7
8
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 :
1
2
3
4
5
6
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
1
2
3
4
5
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:
1
find /<confluence_home>/attachments -type f | xargs file | grep PNG⚠️ Will only work on some specific platforms.
Was this helpful?