Purpose

In several situations, you may need to import several attachments to several JIRA issues. One example is if you've imported issues from another JIRA using CSV, but was unable to add the attachments to the CSV. If the issue keys were not modified (you included them on the import), you can add the attachments this way.

You can attach a single file in a single JIRA issue using REST API. However, as the number of attachments grow, this will become harder and more error prone.

You'll find here an automated way to import many attachments to many JIRA issues with a simple click of a button (or almost).

It's important to point out that these steps were only tested in JIRA 6.4 and may not work in other versions. Please, test it first.

Pre-requisites

To use the following steps, make sure the following prerequisites are met (otherwise, it will simply not work).

1. You have access to a user who can add the attachments to all issues
2. You have a computer with access to your JIRA and it's running Linux or Mac. The OS running the JIRA Server doesn't matter, but you'll have to run a bash script that won't work on Windows
3. You have all the attachments in the following directory structure (the one used by JIRA):

• ABC
  • ABC-1
    • attachment1.png
    • attachment2.jpg
  • ABC-2
    • attachment3.pdf

Where ABC is the project key and ABC-1 and ABC-2 are issue keys in this project.

Each attachment must be within a directory with the issue key the attachment will be added to, which in its turn must be within a directory with the project key.\

Solution

Fixing File Names (only if exporting from another JIRA)

This directory structure was not chosen randomly. It's exactly the one used by JIRA to hold the attachments, so you can use this easily to migrate to another JIRA, simply using the attachments in JIRA's 'attachments' directory.

However, if this is the case, JIRA doesn't keep the files with their names, it keeps the files with filenames as their IDs in the database. JIRA keeps the original file name in the database.

If you go directly to the 'Procedure' section, the attachments will be imported with the file ID as their name. Also, they will have other ID as their filename in the destination directories: a mess.

Follow these instructions first to change the file names to their correct ones.

Pre-requisites

• Access to the source JIRA's DB
• Access to the source JIRA's attachments

Be aware that this was only tested in Postgres database.

Instructions

1. Find the project directory (ABC) under the 'attachments' folder
2. Copy the directory elsewhere (we will only change the copy, not to affect the source JIRA Server)

   1. If you're using a Windows JIRA Server, you'll need to copy the attachments to a Linux or Mac machine.

      If you make any of the following changes to the actual JIRA attachments directory instead of a copy, this will make your JIRA Server no longer recognize the attachments.

3. Run the following select in the source JIRA Server database to generate a few commands

   If you're importing from a Jira Cloud instance then contact Atlassian Support to run the query below.

   /* Example for PostgreSQL */
   SELECT ( 'mv ' || project.originalkey || '-' || jiraissue.issuenum || '/' || fileattachment.id || ' "' || project.originalkey || '-' || jiraissue.issuenum || '/' || filename || '"' ) AS command
     FROM fileattachment
     JOIN jiraissue ON fileattachment.issueid = jiraissue.id
     JOIN project ON jiraissue.project = project.id
    WHERE project.pkey = 'ABC'; 
   
   
   /* Example for SQL Server and/or MySQL */
   SELECT ( CAST('mv ' AS nvarchar(18)) + CAST(<jiraschema>.project.originalkey AS nvarchar(18)) + '' + CAST(<jiraschema>.jiraissue.issuenum AS nvarchar(18)) + '/' + CAST(<jiraschema>.fileattachment.ID asASnvarchar(18)) + ' "' + <jiraschema>.project.originalkey + '' + CAST(<jiraschema>.jiraissue.issuenum AS nvarchar(18)) + '/' + filename + '"' ) AS command
     FROM <jiraschema>.fileattachment
     JOIN <jiraschema>.jiraissue ON <jiraschema>.fileattachment.issueid = <jiraschema>.jiraissue.id
     JOIN <jiraschema>.project ON <jiraschema>.jiraissue.project = <jiraschema>.project.id
    WHERE <jiraschema>.project.pkey = 'ABC'
   
   /* Example for MySQL 8.x */
    SELECT CONCAT(CAST('mv ' AS CHAR(18)),
     CAST(<jiraschema>.project.ORIGINALKEY AS CHAR(18)),
     '-',
     CAST(<jiraschema>.jiraissue.issuenum AS CHAR(18)),
     '/',
     CAST(<jiraschema>.fileattachment.ID as CHAR(18)),
     ' "',
     <jiraschema>.project.originalkey,
     '-',
     CAST(<jiraschema>.jiraissue.issuenum AS CHAR(18)),
     '/',
     filename,
     '"') AS command
   FROM <jiraschema>.fileattachment
     JOIN <jiraschema>.jiraissue ON <jiraschema>.fileattachment.issueid = <jiraschema>.jiraissue.id
     JOIN <jiraschema>.project ON <jiraschema>.jiraissue.project = <jiraschema>.project.id
   WHERE <jiraschema>.project.pkey = 'ABC'
   
   
   /* Example for Oracle */
   SELECT ( CAST('mv ' AS VARCHAR2(18)) || CAST(project.originalkey AS VARCHAR2(18)) || '-' || CAST(jiraissue.issuenum AS VARCHAR2(18)) || '/' || CAST(fileattachment.ID as VARCHAR2(18)) || ' "' || project.originalkey || '-' || CAST(jiraissue.issuenum AS VARCHAR2(18)) || '/' || filename || '"' ) AS command
      FROM fileattachment
     JOIN jiraissue ON fileattachment.issueid =jiraissue.id
     JOIN project ON jiraissue.project = project.id
    WHERE project.pkey = 'ABC'; 

   1. Replace 'ABC' by the project key

   2. This query contains two backslashes used to space the double quotation marks. Depending on the database and version, it may need to be removed, replacing <\"> by <">
   3. In the example for SQL Server and/or MySQL, replace <jiraschema> by the proper Jira Schema of your system

   4. Here are the sample results for the above query (PostgreSQL)

      mv ABC-1/10000 "ABC-1/image1.png"
      mv ABC-1/10001 "ABC-1/script.sh"

4. Navigate to the copy project directory in the Linux/Mac server
5. Run the 'mv' commands generated by the select. This steps will rename all the existing attachments (ID) to their respective name
6. Remove the 'thumbs' directory from the copy directory as we don't want to also add the thumbnails.

cd ABC
## If you have the directory structure with the 10000 directory after ABC such as ABC/10000 you'll need to run this instead and repeat for others like 20000:
## cd ABC/10000
rm -rf ./ABC-*/thumbs

Procedure

Follow these instructions to bulk add all attachments to all issues at once. This can only be done to a single project, but you can run it more than once if you need to add to more.

If you followed the instructions in 'Fixing Files Names', please use the directory with the fixed attachments.

1. Atlassian Cloud only: Create an API token here; 

2. Navigate to the project directory:

   cd ABC
   ## If you have the directory structure with the 10000 directory after ABC such as ABC/10000 you'll need to run this instead and repeat the process for others like 20000:
   ## cd ABC/10000

3. Download jira_attachments_import.sh to the project directory (ABC, for example)

4. Give the file executable permissions

   chmod a+x jira_attachments_import.sh

5. Run it 

   ./jira_attachments_import.sh <user account> <password> ABC https://charliesjira.atlassian.net | tee Jira-IMPORT.txt

   1. Atlassian Cloud:
      1. Enter your email address in the <user account>
      2. Enter the API token in the <password>
   2. Server/DC: 
      1. Enter your username in the <user account> (tests worked using Jira Internal user account with administration + project rights) 
      2. Enter the usernames's password in the <password>
   3. project key (it doesn't retrieve it from the directory yet) and JIRA URL (https://charliesjira.atlassian.net);
   4. tee command after the pipe will export the results to both terminal and a text file. It can be used later for troubleshooting errors and/or provided to Atlassian Support
       
6. The attachments will be imported one by one. You can watch for errors.

The expected return for each attachment if it's successful:

HTTP/1.1 200 OK