Hi,
I'm faced with a rather large attachments directory (2Gb), and I'm looking for a way to display a list of attachments with their size, space, and location.
Hey Dick,
You can do that by running this query in the Confluence Database:
SELECT a.TITLE AS "Attachment Name", a.FILESIZE, c.TITLE AS "Page Title", s.SPACENAME AS "Space Name", CONCAT("http://<YourConfluenceURL>/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;
Replace <yourConfluenceURL> with your Confluence URL. That will give you the correct hyperlink to each attachments' location. Added this as a KB here: https://confluence.atlassian.com/pages/viewpage.action?pageId=346325893
This was tested with MySQL (some DB might not have a CONCAT function. Check the DB manual for the correct method to concatenate 2 strings).
Foogie
Here is the same query for SQL Server
SELECT a.TITLE AS "Attachment Name", a.FILESIZE, c.TITLE AS "Page Title", s.SPACENAME AS "Space Name", 'http://<yourConfluenceURL>/pages/viewpageattachments.action?pageId='+convert(varchar(10),a.PAGEID) AS "Location" FROM [ConfluenceDB].[dbo].[ATTACHMENTS] a JOIN [ConfluenceDB].[dbo].[CONTENT] c ON a.PAGEID = c.CONTENTID JOIN [ConfluenceDB].[dbo].[SPACES] s ON c.SPACEID = s.SPACEID ORDER BY a.FILESIZE DESC;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks, this is what I was looking for.
I adjusted it a bit becuase we run PG, so it was possible to do this straight from the command line, did some formatting stuff, and sorted by Space name (so I can blame specific space admins):
postgres@confluence:~$ psql confluencedb -Atc "SELECT s.SPACENAME, pg_size_pretty(a.FILESIZE), a.TITLE AS AttachmentName, c.TITLE AS PageTitle, s.SPACENAME AS SpaceName, 'https://confluence.terena.org/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 WHERE a.FILESIZE > 1048576 ORDER BY s.SPACENAME, a.FILESIZE DESC"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.