I have been tasked with automating the migration of over 100k SolarWinds Helpdesk tickets over to Jira Service Management as we are preparing for the helpdesk transition.
Plan:
My current plan is to create an ETL on Microsoft Fabric that ingests CSV ticket data from Solarwinds and manipulate field values to fit our specific Jira layout needs. I will then leverage Fabric’s automation tools and the Jira API to automatically run through the list of tickets and post each ticket to Jira with the appropriate information making sure to consider rate limits.
Test:
I have to test the API posting for 1 ticket first.
POST endpoint: {{BaseURL}}/rest/api/3/issue
{
"fields": {
"assignee": {
"id": "712020:075b73cb-47a8-4b1d-8129-23da726dc604" //use this endpoint to get id: {{BaseURL}}/rest/api/3/user/search?query=[EMAIL]
},
"customfield_10152": { //Location
"value": "Unassigned"
},
"summary": "API Test Creation",
"description": {
"type": "doc",
"version": 1,
"content": [
{
"type": "paragraph",
"content": [
{
"text": "sample",
"type": "text"
}
]
}
]
},
"project": {
"id": "10022"
},
"customfield_10010": "fc/0364ae89-cf85-4348-ba1c-96c113b4bbdc", // To get this we need to acquire the "portalkey" & "key" from this endpoint: {{BaseURL}}/rest/servicedesk/1/servicedesk/request/18129/request-types
"issuetype": {
"id": "10085" // Find the appropriate issue type {{BaseURL}}/rest/api/3/issuetype/
}
}
}
The table below is an example of how the data looks like in the ticket CSV exported from the old SolarWinds platform.
No. | Date | Updated | Status | Priority | Alert Level | Tech | Location | Request Type | Subject | Request Detail | Client | Notes |
176001 | 21-11-24 2:59 pm | 21-11-24 3:09 pm | Open | Medium | Polsfut ,Devin [DPolsfut] | St. Maria Goretti Community School | 3 – Facilities : Carpentry | Stall door missing lock parts girls washroom by gym. | Barker ,Chris [CBarker] | This was submitted as event 44801 |
Unfortunately, we cannot automatically add the status in Jira on creation, we have to have call an additional API endpoint that transitions ticket status:
First, we need to get the transition type in question by calling: {{BaseURL}}/rest/api/3/issue/FC-68/transitions
Once we know the transition status id, we can call this POST endpoint:{{BaseURL}}/rest/api/3/issue/FC-68/transitions
Body:
{
"transition": {
"id": 31
}
}
This will properly status the ticket over from Open to Closed.
Resulting Plan for Automation:
I will leverage Fabric to import all the data from a csv into a lakehouse table. I will manipulate the data so that I get all of the necessary field values I need for proper mapping. Once that is done, I will execute the Create & Transition API call for each record and this will hopefully automate the migration of tickets over to Jira.
We know there are some limitations like not being able to change creation date; however, it’s probably useful to have the OG creation date somewhere on the ticket, so having a custom field fulfill this need would probably make sense.
In addition to this, the notes section is formatted in a weird way, where maybe we need to parse it and extract information to create comments for the new ticket.
Transforming of Lakehouse Data Using DataFlow Gen2:
- TechEmail Column needs to be created using PowerQuery: Text.Lower(Text.BetweenDelimiters([Tech], “[“, “]”) & “[EMAIL_DOMAIN]”)
- JiraLocationDefaultsIncluded Column basically uses a left merge on the location_mapping lakehouse map table to properly map record Locations to their appropriate standardized Jira location.
- SimpleRequestType Column needs to be created using PowerQuery:
let
// Find the position of "Facilities :"
facilitiesPos = Text.PositionOf([RequestType], "Facilities :") + Text.Length("Facilities :"),
// Extract the substring after "Facilities :"
afterFacilities = Text.Middle([RequestType], facilitiesPos),
// Find the position of the first colon after "Facilities :"
firstColonPos = Text.PositionOf(afterFacilities, ":"),
// If a colon is found, extract the part before it; otherwise, take the whole remaining string
result = if firstColonPos > -1 then Text.Trim(Text.Start(afterFacilities, firstColonPos)) else Text.Trim(afterFacilities)
in
result
I have to make sure for this request type, there is a mapping table in the lakehouse so I can reference it when calling the api later. RequestType or customfield_10010 must have a value like: “fc/0364ae89-cf85-4348-ba1c-96c113b4bbdc”.
Mapping of Data for Ticket Creation:
Solarwinds Transformed Ticket Data | Jira Service Desk Data |
Date | Solarwinds Original Creation Date (10205) |
TechEmail | Assignee – Email |
JiraLocationDefaultsIncluded | Location (10152) |
SimpleRequestType | RequestType(10010) – mapped with requesttype_mapping table |
Subject | Summary |
RequestDetail | Description |
Project>id = default to 10022 (Facilities) | |
Issuetype = default to (Facilities issue type) | |
Reporter | ClientEmail |
Here is a visualization of my dataflow: