Freshdesk to Jira Migration - Importing comments

[Jack Brickey]

Greetings migration experts!

TLDR - how can I import comments from fresh desk into the appropriate JSM tickets while maintaining Author date and public/private properties?

Details...

I have been struggling with migrating one of my support teams out of fresh desk and onto our Jira service management instance. Freshdesk (FD) makes it very challenging to migrate data. However I've made it through most hurtles at this point with really one last Challenge that I'm hoping someone may have some ideas on. Specifically what I need to do is import all of the notes/comments from FD. Below is where I currently stand.

Option 1 - CSV Import

I have been playing with importing the comments subsequent to importing the issues themselves. This is because I have been unable to find a means of exporting the comments along with the issues in a clean manner. So the import looks something like this:

1. Create an export for all fresh desk issues starting with the archive first
2. Massage the CSV file to align with my needs of import.
3. Import the issues using the CSV importer into my JSM project. Note my import file includes the FD ticket number which is recorded into a custom field.
4. Create an export of all issues imported with just the issue key, summary, and FD ticket #
5. Create an export for all FD comments associated with the previous issues exported.
6. Using excel VLOOKUP I can take the two files (steps #4 & #5) and map the Jira issue key to the appropriate comments.
7. Using the results from step 6, import the comments

Findings

According to the documentation, the following format should be used to import comments.

"01/01/2012 10:10;Admin; This comment works"

1. I was able to successfully do this if I use the GDPR compliant Account ID. However I have as yet been able to use the users email address. Well it likely will be feasible to export all users and then use that data to map the email to the Account ID it will be extremely painful. I currently have a support ticket open inquiring about the end ability to use the users email address. Update - the inability to use the users email is a known bug (MIG-842) and I do not expect this bug to get addressed TBH.
2. The really horrible limitation of a CSV import for JSM projects is captured in the following documentation snippet.

If your CSV file consists of Jira Service Management projects with comments and is mapped to the Comments body in the Jira fields column, all the comments from your import file will become public after the CSV import. 

YIKES! This simply will not do. Obviously this would allow customers to see all internal comments. So I decided to turn to the APIs...

Option 1 - APIs

I decided to take a look at the APIs to determine if I could use them to create comments and was initially please that there was the option to do so. Moreover it allows you to select what is the comment was public or not. YAY!

My excitement eventually diminished. What I found was that I was unable to designate the author of the comment. The comment would always be made by the user associated with the API. I found the following open suggestion in JAC which is been open since 2013 so I don't suspect we're gonna see resolution on that.

JRACLOUD-35124 - Add a comment via REST API on behalf of another user 

Any input or assistance would be most appreciated.

[Deleted user]

Hi Jack, 

I've just gotta say...You've done a lot of work! And you've got some top-notch documenting skills. 

Have you checked the Atlassian marketplace? There's a Freshdesk to Jira Service Management app that could have saved you all the hassles. The app would export your Freshdesk data to Jira Service Management. Yes, via the API. However, the app keeps the original author of the comment. 

"My excitement eventually diminished. What I found was that I was unable to designate the author of the comment. The comment would always be made by the user associated with the API. I found the following open suggestion in JAC which is been open since 2013 so I don't suspect we're gonna see resolution on that."

Now, I don't know all the details of your migration. But I do know, that when migrating to Jira Service Management via the API, you need to add users to the target project, grant permission, and make their email visible. Here's a guide on that. Maybe that was your problem.

You need to have a budget to use the app. But considering that you've already spent at least 4 days figuring all of this out, I think it's worth a try. 

P.S. I work for this company. Let me know if my comment was of any help. 

[Jack Brickey]

Hi @[deleted] ,

Thanks for chiming in here. Indeed I am familiar with your company and have previously been working with your team. Unfortunately for a number of reasons I had to forgo this option. I would have loved for this to have worked for me.

[Deleted user]

I see. Thanks for the feedback. 

[Andrew Gallagher]

@Jack BrickeyWhat were the issues with the migration app? This may be useful to other people making similar decisions...

[Jack Brickey]

@Andrew Gallagher , as I recall...

• due to my specific implementation it was going to require a number of customizations. Not a big deal but for sure some work that prevented me from performing a clean trial
• data residency - due to my customer's requirements I could not have the migration data stored overseas. It was possible to setup local residency for a fee but again I would really have to continue to invest just to get to where I had a clean test.
• in my case I knew I was going to have to perform the migration over many iterations over many weeks. This solution has an understandable time limit on how long your migration data is retained. So I would need to start over each time I needed to perform a migration. 

In the end it became easier in my case just to take it on internally. I think that for most folks this tool will work quite well and the company is very responsive. It is when you get into customizations that it becomes  questionable. Again I had some weird field conversions I chose to do, e.g. merging multiple FD fields into a single text field. I am sure that if I chose to proceed we would have gotten it to work.

[Jack Brickey]

Update on what I have found and my current planned approach. I will turn this into an article once complete assuming it all works.

export FD Archive:

1. export the Archived issues from FD w/o comments by using the built in export feature

prep import file:

1. massage the export file from #1 to align with my import needs. important: include the FD ticket ID into a custom field in Jira (EXT#)
2. use CSV import and inspect results

prep for comments import:

1. Once I have successfully imported the issues then I export them with the following fields: issue key, summary, EXT#
2. Export the comments using the FD APIs and be sure to include the Public note indicator.
3. Investigate the results from the comments export and record all unique commenters, both agents and customers.
4. Next we need to capture the unique Jira account ID for each customer and agent involved in the comments from #3. This can be achieved by exporting the JSM agents and portal only customers from Jira. The unique Jira account ID is required due to MIG-842. 
5. Using Excel (or similar app) and the outputs from #1, 2, & 4 we need update the exported comment file to include the commenter's Jira unique user ID. If your import is small (few commenters) then you can manually add in the ID to each comment row otherwise VLOOKUP can be your friend here.
6. Similar to the procedure in #5, add in the Jira issue ID cross referencing the EXT# from the Jira export with the FD ticket # in the FD exported comment file.
7. Create a new column in the comments file and use CONCATENATE to create the Jira specified comment import format (example below) using the comment date-time, unique Jira user ID, and comment

   "01/01/2012 10:10;756048:cd791313-4715e-45h6-a888-3a6b057t071; This comment works"

8. Split the updated comment file from #6 into two files: private and public. This is required to resolve the Jira CSV import limitation where all comments are treated as public.

import comments:

1. First we will import, using CSV importer, the internal comments using the internal comment file created above. The file should consist of the following columns/fields: Jira issue ID, Summary, formatted Comment field
2. Next we will use the Edit comment JSM API to convert the imported comments to be identified as internal.
3. Finally we will import the public comments in the same manner as #1.

Yes this is most certainly a very painful process but the best I have been able to come up with.

[Mahad Uddin]

Hey. Can you link me to the guide from where I can export the comments using FD API?

Thanks!