Attachment size

[Rahul Aich [Nagra]]

HI

I observe that in my jira instance more and more users are attaching huge log files.

Is there a way i can find out how many issues contain attachments in a certain period over 5 MB or say 10MB.

Rahul

[Matheus Fernandes · Accepted solution]

Hi Rahul,

If you are running JIRA 6.1 or a newer version, this query should help:

SELECT p.pkey||'-'||ji.issuenum, filename, filesize FROM fileattachment fa 
	JOIN jiraissue ji ON ji.id = fa.issueid 
	JOIN project p ON ji.project = p.id
WHERE fa.created > '2014-01-01' AND filesize > '10485760';

If you are using older version, please try:

SELECT ji.pkey, filename, filesize FROM fileattachment fa 
	JOIN jiraissue ji ON ji.id = fa.issueid 
	JOIN project p ON ji.project = p.id
WHERE fa.created > '2014-01-01' AND filesize > '10485760';

Just replace "2014-01-01" with the date you want and '10485760' with the filesize (this is 10MB). Also, please note this was created using PostgreSQL, so you may need to adapt to other DBMSs - probably only on the first query to concatenate. :)

I hope this helps!

[Rahul Aich [Nagra]]

Thanks Matheus, it works.