I have two departments that I need to run this for: Facilities and IT. This initiative is being executed concurrently with UAT. Already over 150k tickets have been migrated over; however, to keep it testable for our users, we need to make sure old ticket system updates that are currently happening are somewhat replicated in the new system.
First, I checked to see how many of the tickets I had in FC/IT were statused open. There were 208 open tickets for FC and 372 open tickets for IT. Then, out of the migrated tickets, I checked to see what the latest solarwinds ticket creation date was to get a grasp of when the CSV was pulled from Solarwinds. (Solarwinds does not allow for API integration and therefore requires us to manually pull a CSV file) The latest date was Nov 21, 2024 for FC and Dec 17, 2024 for IT (Non-sis) and Jan 6, 2025 for IT (SIS).
Facilities is simpler as they do not have various statuses, just open and closed, so the only additional information I would be adding to a ticket are:
- Comments
- Changing the status from open to closed
IT have a little more complexity as the information that would be added to a ticket are:
- Comments
- Changing status to valid mapped status
| Solarwinds status | Jira mapped status |
| Open | Waiting for support |
| Waiting for response | Waiting for customer |
| Resolved | Resolved |
| Appointment made | Appointment Made |
| Dispatch | Dispatch |
| Closed | Closed |
| Cancelled | Canceled |
| Pending | Pending |
| Follow up | Waiting for customer |
| RMA | In Progress |
| Waiting for Delivery | Waiting for Delivery |
| Converted to Project | Converted to Project |
First things first, we have to migrated over new tickets that were created in the span of the historical migration and update existing open tickets. To make things a bit easier, what I am going to do is just pull a csv for (1) opened tickets from Nov 21,2024 – Today for ALL tickets. And one more csv for (2) updated tickets from Nov 21, 2024 – Today.
(1) Opened tickets:
Import CSV to Fabric Lakehouse & request type mappings
I will then clean the opened_tickets with a dataflow gen2 and create some new columns by doing some data joins with the requesttype_mapping table. This will allow for me to deal with the complexity of having multiple projects, request types and sub request types.
Then, I created a notebook that will run every 5 minutes to create tickets that are not already in the jira projects. This GET endpoint: https://[company].atlassian.net/rest/api/3/search?jql=”Solarwinds Original Ticket Number[Short text]”~ “[ticket number]” will allow for creations of new tickets on a real-time basis.
Logic:
# JQL Query
jql_query = f'"Solarwinds Original Ticket Number[Short text]"~ "{no}"'
search_url = f"{jira_base_url}/rest/api/3/search"
# Step 1: Search for existing issues
get_response = requests.get(search_url, params={"jql": jql_query}, auth=auth, headers=headers)
if get_response.json().get('total') < 1:
# Attempt to create the Jira issue (with potential retry)
response = create_jira_issue(issue_payload)(2) Updated Tickets
Import CSV to Fabric lakehouse. Then I will clean the updated_tickets with a dataflow gen2.
I then created a generalized updater with data mapping from old system statuses to new system statuses.
Logic:
# JQL Query GET the ticket
jql_query = f'"Solarwinds Original Ticket Number[Short text]"~ "{no}"'
search_url = f"{jira_base_url}/rest/api/3/search"
# Step 1: Search for existing issues
get_response = requests.get(search_url, params={"jql": jql_query}, auth=auth, headers=headers)
if get_response.json().get('total') >= 1:
# Get the appropriate transition ID based on ticket_status
ticket_status_lower = ticket_status.lower()
if ticket_status_lower == 'resolved' or ticket_status_lower == 'cancelled':
ticket_status_lower = 'closed'
if rtkey.startswith("it/"):
transition_id = status_to_transition_id.get(ticket_status_lower)
elif rtkey.startswith("fc/"):
transition_id = fc_status_id.get(ticket_status_lower)
issue_key = get_response.json().get('issues', [])[0].get('key')
issue_status = get_response.json().get('issues', [])[0].get('fields',[])['status']['name']
search_comment_url = f"{jira_base_url}/rest/api/3/issue/{issue_key}/comment"
get_comment_response = requests.get(search_comment_url, auth=auth, headers=headers)
issue_comment = get_comment_response.json().get('comments', [])[0].get('body', []).get('content',[])[0].get('content',[])[0].get('text')
if transition_id and (ticket_status_lower != issue_status.lower()):
# Prepare the payload and make the transition request
transition_payload = {"transition": {"id": transition_id}}
transition_response = requests.post(
f"{jira_base_url}/rest/api/3/issue/{issue_key}/transitions",
auth=auth,
headers=headers,
data=json.dumps(transition_payload),
)
if transition_response.status_code == 400 or transition_response.status_code == 404:
print(f"Bad request while transitioning ticket {issue_key} to '{ticket_status}'. Skipping transition.")
continue
elif transition_response.status_code == 204:
print(f"Ticket {issue_key} transitioned to '{ticket_status}'.")
# Additional final transition if status is "Closed"
if ticket_status_lower in {"closed", "cancelled", "resolved"}:
final_transition_payload = {"transition": {"id": "941"}}
final_transition_response = requests.post(
f"{jira_base_url}/rest/api/3/issue/{issue_key}/transitions",
auth=auth,
headers=headers,
data=json.dumps(final_transition_payload),
)
# Add notes as a comment
formatted_notes = parse_notes(notes)
comment_payload = {
"body": {
"type": "doc",
"version": 1,
"content": [{"type": "paragraph", "content": [{"text": str(formatted_notes), "type": "text"}]}]
}
}
comment_response = requests.post(
f"{jira_base_url}/rest/api/3/issue/{issue_key}/comment",
auth=auth, headers=headers, data=json.dumps(comment_payload)
)
if comment_response.status_code == 400:
print(f"Bad request while adding comment to ticket {issue_key}. Skipping comment.")
elif comment_response.status_code == 201:
print(f"Ticket {issue_key} inserted comment.")
else:
print(f"Unexpected status code: {transition_response.status_code} for ticket {issue_key}.")
else:
print(f"Unknown status '{ticket_status}'. No transition performed. OR same ticket status")