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.pkey, '/', ceiling((ji.issuenum / 10000))* 10000, '/', p.pkey, '-', ji.issuenum, '/', fa.id) as path,
   	concat('/var/atlassian/application-data/jira', '/data/attachments/', p.originalkey, '/', ceiling((ji.issuenum / 10000))* 10000, '/', p.originalkey, '-', ji.issuenum, '/', fa.id) as "path if project key was changed"
   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:

   Please note that in some cases the project key may have changed (moving from PRG1 to PRG2, for example). In this case the second "concat" in this query will be useful to find the correct path of those attachments.

3.  id    |     filename     | project  | issuenum |path
   -------+------------------+----------+----------+-----------------------------------------------------------------------------
    10100 | test.file.gliffy |PRG       |       11 | /var/atlassian/application-data/jira/data/attachments/PRG/10000/PRG-11/10100

   Jira will store the attachments under the original project key, even if the project key is changed. For scenarios like this, you can use the query below which will use the original project key for your projects. This query also provides the thumbnail path for supported attachment types, as clarified in Configuring File Attachments.

   select fa.id,fa.filename, pkk.pkey as project, ji.issuenum, 
   concat('/var/atlassian/application-data/jira','/data/attachments/',pkk.pkey,'/', CEILING((ji.issuenum/10000))*10000,'/',  pkk.pkey, '-', 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/',pkk.pkey,'/', CEILING((ji.issuenum/10000))*10000,'/',  pkk.pkey, '-', ji.issuenum, '/thumbs/_thumb_', fa.id, '.png') end as thumbnail
   from fileattachment fa 
   join jiraissue ji on ji.id= fa.issueid 
   join (
   	SELECT DISTINCT ON (project_id) project_key as pkey, project_id
   	FROM project_key
   	ORDER BY project_id,project_key desc) pkk
   on ji.project=pkk.project_id
   where fa.created > '2021-04-20 13:45';

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

   PSQL Syntax

   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'