Migrating Spiceworks to JIRA Service Desk

Let's keep this simple. I will consider a Spiceworks installation that defaults to sqlite3. It is amazing how much this can handle BTW. It gets slow but man, I saw recently over 1GB of sqlite data handled by a SpiceWorks installation. Well, I did know sqlite rocks and not just in mobile devices.

1. Install sqlite3 (command line) in the Spiceworks server
2. Copy the db (for example from C:\Program Files (x86)\Spiceworks\db) to the sqlite bin directory
3. Access the db from sqlite

   sqlite3 spiceworks-prod.db

4. From sqlite prompt export relevant fields:

   .headers on
   .mode csv
   .output spiceworks-prod.csv
   select tickets.id as ticket_id,
     tickets.created_at,
     tickets.summary,
     tickets.description,
     (select email from users where users.id = tickets.assigned_to) as assigned_to,
     tickets.category,
     tickets.closed_at,
     (select email from users where users.id = tickets.created_by) as created_by,
     tickets.due_at,
     tickets.status,
     tickets.updated_at,
     tickets.summary,
     group_concat(comments.created_at || " - " || (select email from users where users.id = comments.created_by) || " - " || comments.body, " [COMMENT_END] ") as comment
     from tickets, comments
     where comments.ticket_id=tickets.id
     group by ticket_id
     order by comments.ticket_id,comments.id;
   

5. Use JIRA CSV File Import, point to generated file spiceworks-prod.csv, select file encoding UTF-8, date format yyyy-MM-dd HH:mm:ss, leave imported users inactive, map status field (closed to Done; open to Open)
6. When done importing, save configuration and the import logs
7. Optional: If you are into lean thinking you might want to read a bit about classes of service and triage systems or just trust me that this is the simplest way to prioritize your work. To that end go to JIRA Service Desk settings / issues / priorities and use them as class of service. You will need to keep only three and change their name (Mark standard as default):
   • Expedite: There is no workaround. There is a tangible impact to the business bottom line.
   • Fixed Delivery Date: There is no workaround. It must be done before certain date. It impacts the business bottom line
   • Standard: First In First Out. There is a workaround. It impacts the business bottom line.