Confluence Query for Attachments - Size and Date

[Mike]

I am working to improve searching within Confluence and curious if its possible to run a query that will give a list of attachments with the size and date of upload.

Running Confluence 6.6.6 with Microsoft SQL

Ive been able to find total size of attachments, but nothing including date of upload 

Thank for the help

[Tim Oldendorf · Accepted solution]

I just went through a did something similar. I was able to use the queries listed on this page and tweak it a little to get what I was looking for. 

https://confluence.atlassian.com/confkb/how-do-i-list-all-attachments-in-confluence-with-their-location-and-file-sizes-346325893.html

In the content table there is a CREATIONDATE field that you can add to the query that should get you the date of upload you are looking for. Just make sure you change the <confluence base url> to your Confluence base URL

[Mike]

Thanks Tim - I was looking at that page, but was getting an error when running the query

Msg 207, Level 16, State 1, Line 6
Invalid column name

(which is pointing at my base URL that I did confirm in Site Configuration)

Tried in all caps as well

[Tim Oldendorf]

Not sure. I just took that line out since I didn't need it. Depending on your SQL version, the concat function may not be built in. The query will still return the Page title that the attachment is on if you needed to go to that page.

You could also export the results and do the concatenating within excel if you want to have the hyperlink directly to the page. 

This is what I ran and it ran successfully. 

select 

c.TITLE as "Attachment Name",
cp.LONGVAL as "File Size",
c2.TITLE as "Page Title",
s.SPACENAME as "Space Name",
c.CREATIONDATE as CreationDate
from CONTENT c
join CONTENT c2 on c.PAGEID = c2.CONTENTID
join CONTENTPROPERTIES cp on c.CONTENTID = cp.CONTENTID
join SPACES s on c2.SPACEID = s.SPACEID
where c.CONTENTTYPE = 'ATTACHMENT' and cp.PROPERTYNAME = 'FILESIZE'
order by cp.LONGVAL desc;

[Mike]

Thanks Tim!