Track down big attachments

[GÉANT IT]

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.

[fsim · Accepted solution]

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

[Peter Doyle]

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;

[Dick Visser]

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"