JIRA project migration with Google Spreadsheets

JIRA to JIRA project migration — most of the time this has been a headache when it comes to customer negotiations and compromises.

In my experience, usually it starts with a discussion round to identify what customer exactly needs. Some customers expect everything, including issue change history, comments, votes, etc. In this case we have to use the XML project import method. With status, resolution, priorities and custom field mapping this is a headache. In the near-perfect JIRA project migrations I’ve performed, I remember I used Groovy scripts to merge field values, convert additional issue types to labels, and etcetera. If you choose XML import, it needs a lot of technical work and beforehand testing when you have a demanding customer and a JIRA instance that isn’t expected to change much.

The other type of customers are simple-living minimalist people. 😉 They keep things simple, needs are simple and they just need the contents to be brought over. They are okay with basic things such as issue summary, description, type and status.

The method I’m going to discuss today is for the customers who lie in-between the two types of customers above. In my particular case I met a customer who needs comments and one of their custom fields to be brought over. File attachments and issue change history were not necessary for them. In this case downloading JIRA issues from Issue Navigator into Excel and using it as a CSV source won’t do the perfect import.

This led me to develop a custom Google Spreadsheets script. It uses JIRA REST API and fetches project issues into the spreadsheet. Then, it can be downloaded as CSV and imported into JIRA. Here’s a reduced version of the code I wrote – just to demonstrate:

var jql = encodeURIComponent("project = ABC ORDER BY key ASC");
var rows = [];
var s = 0;
while (true) {
  var response = fetchJIRA (baseurl, "/rest/api/2/search?jql=" + jql + "&maxResults=200&fields=*all%2Ccomment&startAt=" + s);
  // fetchJIRA () is a custom function I wrote 
  // to call JIRA REST API and return JSON.
  // It is not explained in this article.
  var max = response.maxResults;
  var c = response.issues.length;
  var t = response.total;
  if (c == 0) break;

  response.issues.forEach (function (i) {
    var row = [];
    row.push (i.key);
    row.push (i.fields.summary);
    // .. Include other fields
    rows.push (row);
    s = response.startAt + c;
  });
}
rows.unshift (["Key", "Summary", ...]);

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.insertSheet("Issues");
var iR = sheet.getRange(1, 1, rows.length, rows[0].length);
iR.setValues(rows);

Actually it does more!

  • Status/ issue type/ resolution/ user account/ select-list custom field value mapping:
    Using a function like this maps statuses on-the-fly fetching, so we don’t have to worry about creating a workflow.

    function statusMapper (param) {
    var mapping = {
      "Open":"Backlog",
      "Done":"Closed",
      "Resolved":"Closed",
      "Validate":"In Verification",
      "Planned":"Defined"
    };
    var ret = mapping[param];
    if (ret == undefined) ret = param;
    return ret;
    }
    row.push (statusMapper (i.fields.status.name));
    row.push (typeMapper (i.fields.issuetype.name));
    row.push (i.assignee ? userMapper (i.assignee.name) : "shaakunthala");
    // ^^ Handy if target project doesn't allow unassigned issues.
    // It maps user accounts on-the-fly, 
    // while assigning all unassigned issue to a default user account.
  • Linking parent and sub-task on-the-fly. The script will do what’s asked in the Atlassian’s official documentation. After fetching issues, ParentIDs will be added using VLOOKUP spreadsheet formula applied by the script.
    var issueid_col = sheet.getLastColumn();
    var newCol = issueid_col + 1;
    var parentkey_cell1 = sheet.getRange(2, issueid_col - 1, 1, 1).getA1Notation();
    var formula ="=IFERROR(VLOOKUP(" + parentkey_cell1 + "," + issuesRange.getA1Notation().replace (/[0-9]/g, "") + "," + issueid_col + ",FALSE), \"\")";
    sheet.getRange(1, newCol, 1, 1).setValue("Parent ID");
    sheet.getRange(2, newCol, 1, 1).setFormula(formula)
         .copyTo(sheet.getRange(3, newCol, sheet.getLastRow() - 2, 1));
  • Mapping additional custom fields to labels. This is useful when you want to avoid creating any custom fields.
    if (i.fields.customfield_30000 != null)
      labels_array.push (i.fields.customfield_30000.value);
  • Comments, Affects Versions, Fix Versions, Labels, etc. can be fetched into different sheets of the same spreadsheet to be imported separately. The reason for this is to avoid large and much complicated CSV files that aren’t much human-readable.
    • In this case, still, target project doesn’t have to be empty. If target project is different, relationship between multiple CSV imports can be handled with issue key inserted to a text custom field as a reference ID.

So, here’s the pros and cons.

Pros:

  1. What’s being transferred over are in human-readable format in the spreadsheet. It may be useful reference. This gives you a better way to verify any mapping and additional labeling you have done.
  2. Google Spreadsheets will take care of the CSV syntax and escaping. You don’t have to worry a thing about CSV errors. Just code what needs to sit in each column.
  3. Less time consuming compared to XML import, but brings over more data compared to minimal CSV import.

Cons:

  1. Limitations of Google Spreadsheets: Google will terminate the script if it runs for more than 6 minutes. This can happen with larger projects. In this case you can use a Python script instead. But you’ll need to write additional code to map parents and sub-tasks.

Reference: 

Official documentation by Atlassian: https://confluence.atlassian.com/adminjiraserver071/importing-data-from-csv-802592885.html

Notes:

There could be third-party add-ons that can perform hassle-free project migrations. If you can purchase such an add-on, this kind of approaches may not be necessary.

There is a method to import JSON instead of CSV. This syntax appears to be different from the issue object returned by the REST API. Therefore you’ll still need a custom script to perform JSON import.

[JIRA] Delete projects from XML backup using Python

[Experimental]

If you are a JIRA system administrator, have you ever come across the requirement of partially exporting JIRA into XML – probably for importing individual projects into another JIRA instance?

If yes, this might work for you. However, my work is incomplete and experimental. This worked with a small JIRA instance (100 issues), and failed with a huge JIRA instance (1 million issues).

Here’s the Python script I wrote for deleting projects from JIRA XML backup:

#!/usr/bin/python

from lxml import etree

#xp = etree.XMLParser(encoding='utf-8', recover=True)
#doc = etree.parse('entities.xml', xp)
doc = etree.parse ('entities.xml')

# Array of project keys
plist = ['ABC', 'DEF', 'GHI']

for x in plist:
    for p in doc.xpath ("//Project[@key!=\'" + x + "\']"):
        #p.get entities and delete
        pi = p.get("id")
        for i in doc.xpath ("//Issue[@project=\'" + pi + "\']"):
            ii = i.get("id")
            for cfv in doc.xpath ("//CustomFieldValue[@issue=\'" + ii + "\']"):
                cfv.getparent().remove(cfv)
            for cg in doc.xpath ("//ChangeGroup[@issue=\'" + ii + "\']"):
                cgi = cg.get("id")
                for ci in doc.xpath ("//ChangeItem[@group=\'" + cgi + "\']"):
                    ci.getparent().remove(ci)
                cg.getparent().remove(cg)
            for fa in doc.xpath ("//FileAttachment[@issue=\'" + ii + "\']"):
                fa.getparent().remove(fa)
            for il in doc.xpath ("//IssueLink[@source=\'" + ii + "\']"):
                il.getparent().remove(il)
            for il in doc.xpath ("//IssueLink[@destination=\'" + ii + "\']"):
                il.getparent().remove(il)
            for na in doc.xpath ("//NodeAssociation[@sourceNodeEntity=\'Issue\' and @sourceNodeId=\'" + ii + "\']"):
                na.getparent().remove(na)
            for wl in doc.xpath ("//Worklog[@issue=\'" + ii + "\']"):
                wl.getparent().remove(wl)
            for c in doc.xpath ("//Action[@issue=\'" + ii + "\']"):
                c.getparent().remove()
            i.getparent().remove(i)
        for na in doc.xpath ("//NodeAssociation[@sourceNodeEntity=\'Project\' and @sourceNodeId=\'" + pi + "\']"):
            na.getparent().remove(na)
        for c in doc.xpath ("//Component[@project=\'" + pi + "\']"):
            c.getparent().remove(c)
        for v in doc.xpath ("//Version[@project=\'" + pi + "\']"):
            v.getparent().remove(v)
        for a in doc.xpath ("//ProjectRoleActor[@pid=\'" + pi + "\']"):
            a.getparent().remove(a)
        for pk in doc.xpath ("ProjectKey[@projectId=\'" + pi + "\']"):
            pk.getparent().remove(pk)
        p.getparent().remove(p)

f = open ('entities.new.xml', 'w')
f.write (etree.tostring(doc, pretty_print=True, xml_declaration=True))
f.close()

Before you run this, you need to unzip the JIRA XML backup which is a zip archive containing two or three XML files. Put the script file in the same directory where you have the unzipped XML files and run it. It will produce output into file ‘entities.new.xml’. Once completed, replace ‘entities.xml’ by ‘entities.new.xml’, remove the Python script and re-pack the folder into a new zip file. Now it is ready to be imported into another JIRA instance.

Please note that this is incomplete and I cannot guarantee 100% success. You can easily follow the structure of this code and improve it by yourself.

SQL Injection පුදුමය!

මේ දවස් වල කැම්පස් එකේ Information Security and Audit විෂයය යටතේ අපි SQL Injection ගැන හදාරනවා. කැම්පස් එකේ spoonfeeding කරන්නෙ නැහැ‍නේ… මාර්ගය පෙන්වා දීම පමණයි (Teacher is a facilitator). ඒ නිසා වැඩි දුර උනන්දුවක් තියෙනවනම් තමන් තනියම ඉගෙනගන්න ඕන.

ඉතිං මට මේ ගැන හදාරන්න ඇති වුණ විශාල උනන්දුවත් එක්ක මම localhost එකේ එව්වා මෙව්වා අටවගෙන වැඩ පටන් ගත්තා. ටිකක් වෙලා යද්දි මටම මේ ගැන පුදුම හිතුණා. පුදුම විතරක් නෙමෙයි… බය! කොයි තරම් ලොකු ආරක්ෂක අවදානමක්ද? 😯 නිර්මාණාත්මකව හිතන්න පුළුවන් නම් ලොකු ගේම් දෙන්න පුළුවන්. හැම දේටම වඩා සතුටුයි සරසවි දේශන වලින් ලැබුණු දේට වඩා දුරක් එතන ඉඳලා තනියම යන්න පුළුවන් වෙච්ච එක ගැන. 🙂

හැබැයි මේ සම්බන්ධයෙන් මගේ අත්දැකීම් ගැන වැඩි විස්තර forbidden! බුහහහහා!! 😀