Problem

Jira stores attachments, such as files and images, in a file system. This page explains where attachments are located within this file system. 

Resolution

1. The following SQL query will display the details of all attachments within a project called PRG:

   select fa.id, fa.filename, ji.issuenum from fileattachment fa join jiraissue ji on ji.id = fa.issueid join project p on p.id = ji.project where p.pkey = 'PRG';

   You will get results similar to the following:

     id   |     filename     | issuenum
   -------+------------------+---------
    10100 | test.file.gliffy |       11

2.  Using the issuenum (11), you can locate the file in the file system, at the path shown below :

   <JIRA_HOME>/data/attachments/<PROJECT>/<BUCKET>/<ISSUE_KEY>/<ID>

   'BUCKET' refers to which group of 10,000 items the file falls into. Since the issuenum is 11, it will be in the "10000" bucket.  (An issuenum between 1 and 10000 would be bucket 10000, whereas an issuenum between 10001 and 20000 would be in bucket 20000.)
   See JIRA 7.0.x platform release. Old format:

   <JIRA_HOME>/data/attachments/<PROJECT>/<ISSUE_KEY>/<ID>

   For the given example, it will be:

   /opt/atlassian/jira_home/data/attachments/PRG/10000/PRG-11/10100
    
   tree view for the same:
   PRG
   └── 10000
        └── PRG-11
             └── 10100

   Additionally, you can use a query like the following to output the location of the file on the system based on certain criteria (in this case, creation date). Make sure to adjust the '/var/atlassian/application-data/jira' value to point to your Jira home directory or shared home directory in the case of Jira Data Center:

   PSQL Syntax

   select
     fa.id,
     fa.filename,
     p.pkey as project,
     ji.issuenum,
     concat('/var/atlassian/application-data/jira', '/data/attachments/', p.originalkey, '/', ceiling((ji.issuenum / 10000))* 10000, '/', p.originalkey, '-', ji.issuenum, '/', fa.id) as "path",
     case when fa.mimetype = 'image/png' OR fa.mimetype = 'image/gif' OR fa.mimetype = 'image/jpeg' then concat('/var/atlassian/application-data/jira','/data/attachments/', p.originalkey,'/', CEILING((ji.issuenum/10000))*10000,'/', p.originalkey, '-', ji.issuenum, '/thumbs/_thumb_', fa.id, '.png') end as "thumbnail"
   from fileattachment fa
   join jiraissue ji on
     ji.id = fa.issueid
   join project p on
     p.id = ji.project
   where
     fa.created > '2017-04-20 13:45';

   You will get results like this:

     id   | filename  | project | issuenum |                                     path                                      |                                            thumbnail
   -------+-----------+---------+----------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------
    10101 | notes.png | ATT3    |        1 | /var/atlassian/application-data/jira/data/attachments/ATT2/10000/ATT2-1/10101 | /var/atlassian/application-data/jira/data/attachments/ATT2/10000/ATT2-1/thumbs/_thumb_10101.png

   
   

   Notice the query's using the Project's Original Key because that's what Jira uses to build the filesystem path. Even for new Issues or after multiple Project Key changes, it's always the Original Key that determines the attachment path in the filesystem.

   
   

   The last column also builds the thumbnail URLs as explained in Configuring File Attachments.

   
   

   If Jira's configured as a cluster with a shared-home, the attachments are located in the <shared-home>/data/attachments, not in the nodes local filesystem.

For the pre-Jira 7.0 style attachment path, you can use this query:

select fa.id, fa.filename, p.pkey as project, ji.issuenum, concat('/var/atlassian/application-data/jira', '/data/attachments/', p.pkey, '/', p.pkey, '-', ji.issuenum, '/', fa.id) as Path
from fileattachment fa join jiraissue ji on ji.id = fa.issueid join project p on p.id = ji.project 
where fa.created > '2017-04-20 13:45'