How to search specific attachment type in Confluence
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
目的
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
ソリューション
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.