Difference between revisions of "Supportworks Request Import"

From Hornbill
Jump to navigation Jump to search
Line 26: Line 26:
 
===Windows Installation===
 
===Windows Installation===
  
−
* Download the [https://github.com/hornbill/goSWRequestImport/releases/download/1.5.0/goSWRequestImport_v1_5_0.zip ZIP archive]
+
* Download the [https://github.com/hornbill/goSWRequestImport/releases/download/1.5.1/goSWRequestImport_v1_5_1.zip ZIP archive]
 
* Extract zip into a folder you would like the application to run from e.g. '''C:\Call_Import\'''
 
* Extract zip into a folder you would like the application to run from e.g. '''C:\Call_Import\'''
 
* Open '''conf.json''' and add in the necessary configuration
 
* Open '''conf.json''' and add in the necessary configuration

Revision as of 14:55, 23 October 2018

About the Supportworks to Hornbill Service Manager Request Import Utility

The utility provides a simple, safe and secure way to migrate call data from Supportworks v7.x & v8.x in to Hornbill Service Manager. The tool is designed to run behind your corporate firewall, and requires access to your Supportworks database host(s) and file storage.

The tool connects to your Hornbill instance in the cloud over HTTPS/SSL, so as long as you have standard internet access then you should be able to use the tool without the need to make any firewall configuration changes.

The following tasks are carried out when the tool is executed:

  • Supportworks call data is extracted as per your specification, as outlined in the Configuration section of this document;
  • New requests are raised on Service Manager using the extracted call data and associated mapping specifications;
  • Supportworks call diary entries are imported as Historic Updates against the new Service Manager Requests;
  • Attachments to Supportworks Call Diary Entries are imported against their appropriate Historic Updates within Service Manager;
  • Call attachments that are not related to Call Diary Entries are attached to the relevant Service Manager request;
  • Call attachments of type SWM (Supportworks Mail) are decoded and stored as plain text attachments against the Service Manager request or Historic Update as appropriate.

Important Notice

Importing Supportworks call data and associated file attachments in to your Hornbill instance will consume your subscribed storage. Please check your Administration console and your Supportworks data prior to running this import to ensure that you have enough subscribed storage available.

When running the import tool, after the call records are imported, you will receive a warning before importing the associated call file attachments. Please take note of the information presented, as this will inform you the amount of Hornbill storage space you have available to your instance, and the approximate amount that will be consumed should you continue with the file attachment import.

Open Source

The Supportworks to Hornbill Service Manager Request Import Utility is provided open source under the Hornbill Community Licence and can be found Here on GitHub

Installation Overview

Windows Installation

  • Download the ZIP archive
  • Extract zip into a folder you would like the application to run from e.g. C:\Call_Import\
  • Open conf.json and add in the necessary configuration
  • Open a Command Line Prompt as Administrator
  • Change Directory to the folder containing the utility C:\Call_Import\
  • Run the command relevant to the architecture of the machine you are running this on:

For 32bit Windows machines: goSWRequestImport_x32.exe -dryrun=true

For 64bit Windows machines: goSWRequestImport_x64.exe -dryrun=true

Configuration Overview

A demonstration configuration file is provided within the package, which includes configuration for importing call data from Supportworks 7.6.x running ITSM Enterprise 3.6.x. If a configuration file is not specified as a command line argument when executing the tool, then a default configuration file named conf.json, containing the correct JSON, must exist. The following configuration file contains the configuration elements required when importing Supportworks call data:

Example Configuration file

Click "Read More" to expand this section and show an example conf.json file. -------->>>> 

 "HBConf": {
   "InstanceID": "",
   "UserName": "",
   "Password": ""
 },
 "SWServerAddress":"127.0.0.1",
 "AttachmentRoot":"E:/Program Files/Hornbill/Supportworks Server/data/cfa_store",
 "SWSystemDBConf": {
   "Driver":"swsql",
   "UserName": "",
   "Password": ""
 },
 "SWAppDBConf": {
   "Driver": "swsql",
   "Server": "127.0.0.1",
   "Database": "swdata",
   "UserName": "",
   "Password": "",
   "Port": 5002,
   "Encrypt": false
 },
 "CustomerType": "0",
 "SMProfileCodeSeperator": "-",
 "RelatedRequestQuery":"(SELECT fk_callref_m AS parentRequest, fk_callref_s AS childRequest from cmn_rel_opencall_oc) UNION (SELECT bpm_parentcallref AS parentRequest, callref AS childRequest FROM opencall WHERE callclass = 'B.P Task') ",
 "RequestTypesToImport": 
 [
   {
     "Description":"This object configures the importing of Incidents",
     "Import":true,
     "CallClass": "Incident",
     "SupportworksCallClass":"Incident",
     "DefaultTeam":"Service Desk",
     "DefaultPriority":"Low",
     "DefaultService":"Communications",
     "SQLStatement":"SELECT opencall.callref,  logdatex, closedatex, cust_id, cust_name, itsm_title, owner, suppgroup, status, updatedb.updatetxt, priority, itsm_impact_level, itsm_urgency_level, withinfix, withinresp, bpm_workflow_id, probcode, fixcode, site FROM opencall, updatedb WHERE updatedb.callref = opencall.callref AND updatedb.udindex = 0 AND callclass = 'Incident' AND appcode = 'ITSM'",
     "CoreFieldMapping": {
       "h_datelogged":"[logdatex]",
       "h_dateclosed":"[closedatex]",
       "h_summary":"[itsm_title]",
       "h_description":"Supportworks Incident Reference: [oldCallRef]\n\n[updatetxt]",
       "h_external_ref_number":"[oldCallRef]",
       "h_fk_user_id":"[cust_id]",
       "h_status":"[status]",
       "h_request_language":"en-GB",
       "h_impact":"[itsm_impact_level]",
       "h_urgency":"[itsm_urgency_level]",
       "h_customer_type":"0",
       "h_container_id":"",
       "h_fk_serviceid":"",
       "h_resolution":"",
       "h_category_id":"[probcode]",
       "h_closure_category_id":"[fixcode]",
       "h_ownerid":"[owner]",
       "h_fk_team_id":"[suppgroup]",
       "h_fk_priorityid":"",
       "h_site_id":"[site]",
       "h_company_id":"",
       "h_company_name":"",
       "h_withinfix":"[withinfix]",
       "h_withinresponse":"[withinresp]",
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":""
     },
     "AdditionalFieldMapping":{
       "h_firsttimefix":"",
       "h_custom_a":"Custom Data",
       "h_custom_b":"[itsm_title]",
       "h_custom_c":"[updatetxt]",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":"",
       "h_flgproblemfix":"",
       "h_fk_problemfixid":"",
       "h_flgfixisworkaround":"",
       "h_flg_fixisresolution":""
     }
   },
   {
     "Description":"This object configures the importing of Business Process Tasks",
     "Import":true,
     "CallClass": "Incident",
     "SupportworksCallClass":"B.P Task",
     "DefaultTeam":"Service Desk",
     "DefaultPriority":"Low",
     "DefaultService":"Communications",
     "SQLStatement":"SELECT opencall.callref,  logdatex, closedatex, cust_id, cust_name, itsm_title, owner, suppgroup, status, updatedb.updatetxt, priority, itsm_impact_level, itsm_urgency_level, withinfix, withinresp, bpm_workflow_id, probcode, fixcode, site FROM opencall, updatedb WHERE updatedb.callref = opencall.callref AND updatedb.udindex = 0 AND callclass = 'B.P Task' AND appcode = 'ITSM'",
     "CoreFieldMapping": {
       "h_datelogged":"[logdatex]",
       "h_dateclosed":"[closedatex]",
       "h_summary":"[itsm_title]",
       "h_description":"Supportworks Task Reference: [oldCallRef]\n\n[updatetxt]",
       "h_external_ref_number":"[oldCallRef]",
       "h_fk_user_id":"[cust_id]",
       "h_status":"[status]",
       "h_request_language":"en-GB",
       "h_impact":"[itsm_impact_level]",
       "h_urgency":"[itsm_urgency_level]",
       "h_customer_type":"0",
       "h_container_id":"",
       "h_fk_serviceid":"",
       "h_resolution":"",
       "h_category_id":"[probcode]",
       "h_closure_category_id":"[fixcode]",
       "h_ownerid":"[owner]",
       "h_fk_team_id":"[suppgroup]",
       "h_fk_priorityid":"",
       "h_site_id":"[site]",
       "h_company_id":"",
       "h_company_name":"",
       "h_withinfix":"[withinfix]",
       "h_withinresponse":"[withinresp]",
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":""
     },
     "AdditionalFieldMapping":{
       "h_firsttimefix":"",
       "h_custom_a":"Custom Data",
       "h_custom_b":"[itsm_title]",
       "h_custom_c":"[updatetxt]",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":"",
       "h_flgproblemfix":"",
       "h_fk_problemfixid":"",
       "h_flgfixisworkaround":"",
       "h_flg_fixisresolution":""
     }
   },
   {
     "Description":"This object configures the importing of Service Requests",
     "Import":true,
     "CallClass": "Service Request",
     "SupportworksCallClass":"Service Request",
     "DefaultTeam":"Service Desk",
     "DefaultPriority":"Low",
     "DefaultService":"Desktop Support",
     "SQLStatement":"SELECT opencall.callref,  cust_id, logdatex, closedatex, itsm_title, owner, suppgroup, status, updatedb.updatetxt, priority, itsm_impact_level, itsm_urgency_level, withinfix, withinresp, bpm_workflow_id, probcode, fixcode, site FROM opencall, updatedb WHERE updatedb.callref = opencall.callref AND updatedb.udindex = 0 AND callclass = 'Service Request'  AND appcode = 'ITSM'",
     "CoreFieldMapping": {
       "h_datelogged":"[logdatex]",
       "h_dateclosed":"[closedatex]",
       "h_summary":"[itsm_title]",
       "h_description":"Supportworks Incident Reference: [oldCallRef]\n\n[updatetxt]",
       "h_external_ref_number":"[oldCallRef]",
       "h_fk_user_id":"[cust_id]",
       "h_status":"[status]",
       "h_request_language":"en-GB",
       "h_impact":"[itsm_impact_level]",
       "h_urgency":"[itsm_urgency_level]",
       "h_customer_type":"0",
       "h_container_id":"",
       "h_fk_serviceid":"",
       "h_resolution":"",
       "h_category_id":"[probcode]",
       "h_closure_category_id":"[fixcode]",
       "h_ownerid":"[owner]",
       "h_fk_team_id":"[suppgroup]",
       "h_fk_priorityid":"",
       "h_site_id":"[site]",
       "h_company_id":"",
       "h_company_name":"",
       "h_withinfix":"[withinfix]",
       "h_withinresponse":"[withinresp]",
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":""
     },
     "AdditionalFieldMapping":{
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":""
     }
   },
   {
     "Description":"This object configures the importing of Change Requests",
     "Import":false,
     "CallClass": "Change Request",
     "SupportworksCallClass":"Change Request",
     "DefaultTeam":"Service Desk",
     "DefaultPriority":"Low",
     "DefaultService":"Finances",
     "SQLStatement":"SELECT opencall.callref, cust_id, logdatex, closedatex, itsm_title, owner, suppgroup, status, updatedb.updatetxt, priority, itsm_impact_level, itsm_urgency_level, withinfix, withinresp, bpm_workflow_id, probcode, fixcode, site FROM opencall, updatedb WHERE updatedb.callref = opencall.callref AND updatedb.udindex = 0 AND callclass = 'Change Request'  AND appcode = 'ITSM' ",
     "CoreFieldMapping": {
       "h_datelogged":"[logdatex]",
       "h_dateclosed":"[closedatex]",
       "h_summary":"[itsm_title]",
       "h_description":"Supportworks Change Request Reference: [oldCallRef]\n\n[updatetxt]",
       "h_external_ref_number":"[oldCallRef]",
       "h_fk_user_id":"[cust_id]",
       "h_fk_user_name":"[cust_name]",
       "h_status":"[status]",
       "h_request_language":"en-GB",
       "h_impact":"[itsm_impact_level]",
       "h_urgency":"[itsm_urgency_level]",
       "h_customer_type":"0",
       "h_container_id":"",
       "h_fk_serviceid":"[service_name]",
       "h_resolution":"",
       "h_category_id":"[probcode]",
       "h_closure_category_id":"[fixcode]",
       "h_ownerid":"[owner]",
       "h_fk_team_id":"[suppgroup]",
       "h_fk_priorityid":"",
       "h_site_id":"[site]",
       "h_company_id":"",
       "h_company_name":"",
       "h_withinfix":"[withinfix]",
       "h_withinresponse":"[withinresp]",
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":""
     },
     "AdditionalFieldMapping":{
       "h_start_time":"",
       "h_end_time":"",
       "h_change_type":"",
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":"",
       "h_scheduled":""
     }
   },
   {
     "Description":"This object configures the importing of Problems",
     "Import":false,
     "CallClass": "Problem",
     "SupportworksCallClass":"Problem",
     "DefaultTeam":"Service Desk",
     "DefaultPriority":"Low",
     "DefaultService":"Home Working",
     "SQLStatement":"SELECT opencall.callref, cust_id, logdatex, closedatex, itsm_title, owner, suppgroup, status, updatedb.updatetxt, priority, itsm_impact_level, itsm_urgency_level, withinfix, withinresp, bpm_workflow_id, probcode, fixcode, site FROM opencall, updatedb WHERE updatedb.callref = opencall.callref AND updatedb.udindex = 0 AND callclass = 'Problem'  AND appcode = 'ITSM' ",
     "CoreFieldMapping": {
       "h_datelogged":"[logdatex]",
       "h_dateclosed":"[closedatex]",
       "h_summary":"[itsm_title]",
       "h_description":"Supportworks Problem Reference: [oldCallRef]\n\n[updatetxt]",
       "h_external_ref_number":"[oldCallRef]",
       "h_fk_user_id":"[cust_id]",
       "h_fk_user_name":"[cust_name]",
       "h_status":"[status]",
       "h_request_language":"en-GB",
       "h_impact":"[itsm_impact_level]",
       "h_urgency":"[itsm_urgency_level]",
       "h_customer_type":"0",
       "h_container_id":"",
       "h_fk_serviceid":"[service_name]",
       "h_resolution":"",
       "h_category_id":"[probcode]",
       "h_closure_category_id":"[fixcode]",
       "h_ownerid":"[owner]",
       "h_fk_team_id":"[suppgroup]",
       "h_fk_priorityid":"",
       "h_site_id":"[site]",
       "h_company_id":"",
       "h_company_name":"",
       "h_withinfix":"[withinfix]",
       "h_withinresponse":"[withinresp]",
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":""
     },
     "AdditionalFieldMapping":{
       "h_workaround":"",
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":""
     }
   },
   {
     "Description":"This object configures the importing of Known Errors",
     "Import":false,
     "CallClass": "Known Error",
     "SupportworksCallClass":"Known Error",
     "DefaultTeam":"Service Desk",
     "DefaultPriority":"Low",
     "DefaultService":"Infrastructure Support",
     "SQLStatement":"SELECT opencall.callref, cust_id, logdatex, closedatex, itsm_title, owner, suppgroup, status, updatedb.updatetxt, priority, itsm_impact_level, itsm_urgency_level, withinfix, withinresp, bpm_workflow_id, probcode, fixcode, site FROM opencall, updatedb WHERE updatedb.callref = opencall.callref AND updatedb.udindex = 0 AND callclass = 'Known Error'  AND appcode = 'ITSM' ",
     "CoreFieldMapping": {
       "h_datelogged":"[logdatex]",
       "h_dateclosed":"[closedatex]",
       "h_summary":"[itsm_title]",
       "h_description":"Supportworks Known Error Reference: [oldCallRef]\n\n[updatetxt]",
       "h_external_ref_number":"[oldCallRef]",
       "h_fk_user_id":"[cust_id]",
       "h_fk_user_name":"[cust_name]",
       "h_status":"[status]",
       "h_request_language":"en-GB",
       "h_impact":"[itsm_impact_level]",
       "h_urgency":"[itsm_urgency_level]",
       "h_customer_type":"0",
       "h_container_id":"",
       "h_fk_serviceid":"[service_name]",
       "h_resolution":"",
       "h_category_id":"[probcode]",
       "h_closure_category_id":"[fixcode]",
       "h_ownerid":"[owner]",
       "h_fk_team_id":"[suppgroup]",
       "h_fk_priorityid":"",
       "h_site_id":"[site]",
       "h_company_id":"",
       "h_company_name":"",
       "h_withinfix":"[withinfix]",
       "h_withinresponse":"[withinresp]",
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":""
     },
     "AdditionalFieldMapping":{
       "h_solution":"",
       "h_root_cause":"",
       "h_steps_to_resolve":"",
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":""
     }
   },
   {
     "Description":"This object configures the importing of Releases",
     "Import":false,
     "CallClass": "Release",
     "SupportworksCallClass":"Release Request",
     "DefaultTeam":"Service Desk",
     "DefaultPriority":"Low",
     "DefaultService":"Finances",
     "SQLStatement":"SELECT opencall.callref, cust_id, logdatex, closedatex, itsm_title, owner, suppgroup, status, updatedb.updatetxt, priority, itsm_impact_level, itsm_urgency_level, withinfix, withinresp, bpm_workflow_id, probcode, fixcode, site FROM opencall, updatedb WHERE updatedb.callref = opencall.callref AND updatedb.udindex = 0 AND callclass = 'Release Request'  AND appcode = 'ITSM' ",
     "CoreFieldMapping": {
       "h_datelogged":"[logdatex]",
       "h_dateclosed":"[closedatex]",
       "h_summary":"[itsm_title]",
       "h_description":"Supportworks Release Reference: [oldCallRef]\n\n[updatetxt]",
       "h_external_ref_number":"[oldCallRef]",
       "h_fk_user_id":"[cust_id]",
       "h_fk_user_name":"[cust_name]",
       "h_status":"[status]",
       "h_request_language":"en-GB",
       "h_impact":"[itsm_impact_level]",
       "h_urgency":"[itsm_urgency_level]",
       "h_customer_type":"0",
       "h_container_id":"",
       "h_fk_serviceid":"[service_name]",
       "h_resolution":"",
       "h_category_id":"[probcode]",
       "h_closure_category_id":"[fixcode]",
       "h_ownerid":"[owner]",
       "h_fk_team_id":"[suppgroup]",
       "h_fk_priorityid":"",
       "h_site_id":"[site]",
       "h_company_id":"",
       "h_company_name":"",
       "h_withinfix":"[withinfix]",
       "h_withinresponse":"[withinresp]",
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":""
     },
     "AdditionalFieldMapping":{
       "h_start_time":"",
       "h_end_time":"",
       "h_release_type":"",
       "h_custom_a":"",
       "h_custom_b":"",
       "h_custom_c":"",
       "h_custom_d":"",
       "h_custom_e":"",
       "h_custom_f":"",
       "h_custom_g":"",
       "h_custom_h":"",
       "h_custom_i":"",
       "h_custom_j":"",
       "h_custom_k":"",
       "h_custom_l":"",
       "h_custom_m":"",
       "h_custom_n":"",
       "h_custom_o":"",
       "h_custom_p":"",
       "h_custom_q":"",
       "h_scheduled":""
     }
   }
 ],
 "PriorityMapping": {
   "Supportworks Priority":"Service Manager Priority"
 },
 "TeamMapping": {
   "Supportworks Group ID":"Service Manager Team Name"
 },
 "CategoryMapping": {
   "Supportworks Profile Code":"Service Manager Profile Code"
 },
 "ResolutionCategoryMapping": {
   "Supportworks Resolution Profile Code":"Service Manager Resolution Profile Code"
 },
 "ServiceMapping": {
   "Supportworks Service Name":"Service Manager Service Name"
 },
 "StatusMapping":{
   "1" : "status.open",
   "2" : "status.open",
   "3" : "status.open",
   "4" : "status.onHold",
   "5" : "status.open",
   "6" : "status.resolved",
   "8" : "status.new",
   "9" : "status.open",
   "10" : "status.open",
   "11" : "status.open",
   "16" : "status.closed",
   "17" : "status.cancelled",
   "18" : "status.closed"
 }

What do I put in the Configuration file?

HBConfig

Connection information for the Hornbill instance:

  • "InstanceID" - This is the name of your Hornbill instance and can be found within the URL you use to navigate to it: live.hornbill.com/[instance name]/. This value is case sensitive.
  • "UserName" - Instance User Name with which the tool will log the new requests
  • "Password" - Instance Password for the above User

SWServerAddress

The address of the Supportworks Server. If this tool is to be run on the Supportworks Server, then this should be set to localhost.

AttachmentRoot

This is the location of the Supportworks Call File Attachment Store.

SWSystemDBConf

Contains the connection information for the Supportworks cache database (sw_systemdb).

  • "Driver" the driver to use to connect to the sw_systemdb database:
  • swsql = Supportworks 7.x SQL (MySQL v4.0.16). Also supports MySQL v3.2.0 to <v5.0
  • mysql = MySQL Server v5.0 or above, or MariaDB (Supportworks v8+)
  • "UserName" Username for a user that has read access to the SQL database from the location of the tool
  • "Password" Password for above User Name

SWAppDBConf

Contains the connection information for the Supportworks application database (swdata).

  • "Driver" the driver to use to connect to the database that holds the Supportworks application information:
  • swsql = Supportworks 7.x SQL (MySQL v4.0.16). Also supports MySQL v3.2.0 to <v5.0
  • mysql = MySQL Server v5.0 or above, or MariaDB (Supportworks v8+)
  • mssql = Microsoft SQL Server (2005 or above)
  • "Server" The address of the SQL server
  • "UserName" The username for the SQL database
  • "Password" Password for above User Name
  • "Port" SQL port (5002 if the data is hosted on the Supportworks server)
  • "Encrypt" Boolean value to specify whether the connection between the script and the database should be encrypted. NOTE: There is a bug in SQL Server 2008 and below that causes the connection to fail if the connection is encrypted. Only set this to true if your SQL Server has been patched accordingly.

CustomerType

Should contain an integer value of 0 or 1, to determine the customer type for the records being imported:

  • 0 - Hornbill Users
  • 1 - Hornbill Contacts

SMProfileCodeSeperator

A string, to specify the Profile Code separator character in use on your Service Manager instance. By default this is a :

RelatedRequestQuery

The SQL query to run to return request associations from Supportworks, to allow associated imported requests to be linked in Service Manager. The output of the SQL query needs to consist of the following columns:

  • parentRequest : holds the Supportworks request reference of the parent request
  • childRequest : holds the Supportworks request reference of the child request

RequestTypesToImport

A JSON array of objects that contain request-type specific configuration.

  • Description - a string that allows you to describe the current request type object within the array. This is not used by the tool, so can contain any text string.
  • Import - boolean true/false. Specifies whether the current class section should be included in the import.
  • CallClass - specifies the Service Manager request class that the current Conf section relates to.
  • SupportworksCallClass - specifies the call class of the Supportworks requests being imported. Only used in the logging and display output of the tool, the actual Supportworks request class for each import should be defined in the query contained within the SQLStatement parameter.
  • DefaultTeam - If a request is being imported, and the tool cannot verify its Support Group, then the Support Group from this variable is used to assign the request.
  • DefaultPriority - If a request is being imported, and the tool cannot verify its Priority, then the Priority from this variable is used to escalate the request.
  • DefaultService - If a request is being imported, and the tool cannot verify its Service from the mapping, then the Service from this variable is used to log the request.
  • SQLStatement - The SQL query used to get call (and extended) information from the Supportworks application data.
  • CoreFieldMapping - The core fields used by the API calls to raise requests within Service Manager, and how the Supportworks data should be mapped in to these fields.
    • Any value wrapped with [] will be populated with the corresponding response from the SQL Query
    • Any Other Value is treated literally as written example:
      • "h_summary":"[itsm_title]", - the value of itsm_title is taken from the SQL output and populated within this field
      • "h_description":"Supportworks Incident Reference: [oldCallRef]\n\n[updatetxt]", - the request description would be populated with "Supportworks Incident Reference: ", followed by the Supportworks call reference, 2 new lines then the call description text from the Supportworks call.
    • Any Hornbill Date Field being populated should have an EPOCH value passed to it. This includes h_datelogged, h_dateresolved and h_dateclosed.
    • Note: "h_dateclosed":"[closedatex]", - opencall.closedatex is used in Supportworks to hold the date a request will come off hold. This must be populated if you are importing requests in an On-Hold or Closed status.
    • Core Fields that can resolve associated record from passed-through value:
      • "h_site_id":"[site]", - When a string is passed to the site field, the script attempts to resolve the given site name against the Site entity, and populates the request with the correct site information. If the site cannot be resolved, the site details are not populated for the request being imported.
      • "h_fk_user_id":"[cust_id]", - As site, above, but resolves the original request customer against the users or contacts within Hornbill.
      • "h_ownerid":"[owner]", - As site, above, but resolves the original request owner against the analysts within Hornbill.
      • "h_category_id":"[probcode]", - As site, above, but uses additional CategoryMapping from the configuration, as detailed below.
      • "h_closure_category_id":"[fixcode]", - As site, above, but uses additional ResolutionCategoryMapping from the configuration, as detailed below.
      • "h_ownerid":"[owner]", - As site, above, but resolves the original request owner against the analysts within Hornbill.
      • "h_fk_team_id":"[suppgroup]", - As site, above, but uses additional TeamMapping from the configuration, as detailed below.
      • "h_fk_priorityid":"[priority]", - As site, above, but uses additional PriorityMapping from the configuration, as detailed below.
  • AdditionalFieldMapping - Contains additional columns that can be stored against the new request record. Mapping rules are as above.

PriorityMapping

Allows for the mapping of Priorities between Supportworks and Hornbill Service Manager, where the left-side properties list the Priorities from Supportworks, and the right-side values are the corresponding Priorities from Hornbill that should be used when escalating the new requests.

TeamMapping

Allows for the mapping of Support Groups/Team between Supportworks and Hornbill Service Manager, where the left-side properties list the Support Group ID's (not the Group Name!) from Supportworks, and the right-side values are the corresponding Team names from Hornbill that should be used when assigning the new requests.

CategoryMapping

Allows for the mapping of Problem Profiles/Request Categories between Supportworks and Hornbill Service Manager, where the left-side properties list the Profile Codes (not the descriptions!) from Supportworks, and the right-side values are the corresponding Profile Codes (again, not the descriptions!) from Hornbill that should be used when categorising the new requests.

ResolutionCategoryMapping

Allows for the mapping of Resolution Profiles/Resolution Categories between Supportworks and Hornbill Service Manager, where the left-side properties list the Resolution Codes (not the descriptions!) from Supportworks, and the right-side values are the corresponding Resolution Codes (again, not the descriptions!) from Hornbill that should be used when applying Resolution Categories to the newly logged requests.

ServiceMapping

Allows for the mapping of Services between Supportworks and Hornbill Service Manager, where the left-side properties list the Service names from Supportworks, and the right-side values are the corresponding Services from Hornbill that should be used when raising the new requests.

StatusMapping

Allows for the mapping of Request Statuses between Supportworks and Hornbill Service Manager, where the left-side properties list the Status IDs from Supportworks, and the right-side values are the corresponding Status IDs from Hornbill that should be used when importing the requests.

Command Line Parameters

  • file - Defaults to `conf.json` - Name of the Configuration file to load
  • dryrun - Defaults to `false` - Set to True and the XMLMC for the raising of new requests will not be called, and instead the generated XML for each request will be dumped to the log file. This is to aid in debugging the initial connection information.
  • debug - Defaults to `false` - set to true to output additional debug logging
  • concurrent - defaults to `1`. This is to specify the number of requests that should be imported concurrently, and can be an integer between 1 and 10 (inclusive). 1 is the slowest level of import, but does not affect performance of your Hornbill instance, and 10 will process the import much more quickly but could affect performance.
  • attachments - defaults to 'false'. If this is false then the tool will prompt to import file attachments, giving you detailed informtion regarding instance free-space and the approximate amount of space the imported files will take up. Set to true and the tool will import the files without prompting.

HTTP Proxies

If you use a proxy for all of your internet traffic, the HTTP_PROXY Environment variable needs to be set. The https_proxy environment variable holds the hostname or IP address of your proxy server. It is a standard environment variable and like any such variable, the specific steps you use to set it depends on your operating system.

For windows machines, it can be set from the command line using the following:
set HTTP_PROXY=HOST:PORT
Where "HOST" is the IP address or host name of your Proxy Server and "PORT" is the specific port number.

Testing Overview

If you run the application with the argument -dryrun=true then no requests will be raised within Service Manager - the XML used to raise requests will be saved in the log file so you can ensure the database mappings are correct before running the import.

goSWRequestImport_x64.exe -dryrun=true -file=conf.json

Logging Overview

All logging output is saved in the log directory, in the same directory as the executable. The file name contains the date and time the import was run SW_Call_Import_2015-11-06T14-26-13Z.log

Trouble Shooting

Common Error Messages

Below are some common errors that you may encounter in the log file and what they mean:

  • [ERROR] Error Decoding Configuration File:..... - this will be typically due to a missing quote (") or comma (,) somewhere in the configuration file. This is where an online JSON viewer/validator can come in handy rather than trawling the conf file looking for that proverbial needle in a haystack.
  • [ERROR] Database Query Error: read tcp 127.0.0.1:xxxx->127.0.0.1:xxxx: wsarecv: An established connection was aborted by the software in your host machine. - This is most likely due to an incorrect Username and/or password specified in the SWSystemDBConf or SWAppDBConf section of the conf file. Check and confirm the username and password used to access your Supportworks databases.
  • [ERROR] Database Query Error: driver: bad connection. - Like the error above, this can be associated with an incorrect Username and/or password specified in the SWSystemDBConf or SWAppDBConf section of the conf file. Check and confirm the username and password used to access your Supportworks databases.

Error Codes

  • 100 - Unable to create log File
  • 101 - Unable to create log folder
  • 102 - Unable to Load Configuration File

Change Log

Click "Read More" to view the Change Log.

v1.5.0 (14/10/2018)

Features

  • Performance improvements
  • Memory usage improvements
  • Reduction in the number of HTTP sessions used
  • No longer required to provide instance zone when running tool
  • Logging improvements
  • Historic Updates and File Attachments are now processed for each request rather than at the end, improving log readability, performance and session use.

Defect fix

  • Fixed memory leak

v1.4.7 (07/02/2018)

Defect Fix

  • Fixed issue with call diary entries sometimes assigning to incorrect request

v1.4.6 (06/02/2018)

Feature

  • More memory usage improvements.

v1.4.5 (02/02/2018)

Feature

  • Improved multithreading peformance and memory usage.


v1.4.4 (12/12/2017)

Defect fix

  • Fixed issue where team mapping could return other groups of the same name.

v1.4.3 (08/12/2017)

Features

  • Now removes any incompatible (non UTF-8) characters from the decoded Supportworks Mail file response
  • Improved output of SWM file text representation, included RFC headers and made line feeds Windows compatible.

Defect Fixes

  • Fixed "null" value issue for date/time of file attachments
  • Fixed warnings thrown when date/time is string instead of EPOCH

v1.4.2 (07/12/2017)

Defect Fix

  • Corrected issue with historic update attachments being associated to the incorrect historic update within the same request

Feature

  • Improved output when unable to connect to the Hornbill instance

v1.4.1 (30/11/2017)

Defect Fix

  • Now sending UTC time to Hornbill APIs to ensure correct request log date being applied

v1.4.0 (28/11/2017)

Defect Fix

  • Fixed mismatch of historic attachment UpdateID to Historic Update Index

Features

  • Refactored code, broken the code up in the original release in to modules for easier reading and maintenance;
  • Status Mapping, which was hard-coded, is now configurable;
  • Output DB connection errors to CLI as well as Log
  • Optimise hornbillitsmhistoric index once historic updates have been added
  • Made the Request Type in the configuration an array of objects, so can add configuration for as many request type imports as necessary.
  • Added support for import of Releases;
  • Added support for import of BP Task;
  • Added support for admin-defined query to return request associations, and configuration to allow imported BP Tasks to be associaed to other imported request types;
  • Process file attachments in-line when importing requests, rather than at the end of the import process;
  • Supportworks Mail file extraction, and attach files contained in SWM emails as files against new request or historic updates
  • Improved logging

v1.3.0 03/01/2017

Feature

  • Added logic to write AdditionalFieldMapping mapped data to new RequestsExtended entity

v1.2.10 25/11/2016

Defect Fix

  • Issue importing requests in an On Hold status in to Service Manager v2.30+

v1.2.9 14/11/2016

Defect Fix

  • Issue importing file attachments in to historical attachments entity

v1.2.8 07/11/2016

Feature

  • Historical diary entries are now ordered by descending update time

v1.2.7 07/07/2016

Defect Fix

  • File attachments with names containing API-constrained characters [<>|\/:*?"] were not imported. These characters are now replaced by an underscore character _

v1.2.6 17/06/2016

Features

  • Added -attachments flag to allow for the importing of attachments without prompt

Defect Fixes

  • Certain imported requests did not have an active activity stream, meaning they could not be updated.

v1.2.5 01/05/2016

Defect Fixes

  • Writing of duplicate Request profile codes failing
  • Historic Update File Attachments with a .SWM extension importing but not accessible through Service Manager Request UI

v1.2.4 09/35/2016

Features Features:

  • Release binary now includes 32bit and 64bit Windows executables
  • Added -concurrent flag, allowing you to specify the maximum number of requests to be imported concurrently
  • Improved performance when importing file attachments
  • Added ability to specify Service Manager Profile Code separation character
  • Improved client-side import logging.

v1.2.3 09/05/2016

Defect Fixes

  • Fixed data mapping issues
  • Fixed issue where imported calls in a status of On Hold could not be taken off hold

v1.2.2 29/04/2016

Features

  • Allow the back-dating of imported requests,to the date/time the original Supportworks request was logged
  • Allow the import of Resolved Date & Closed Date to match those of the original Supportworks request
  • New requests logged from requests that are On Hold in Supportworks are now placed On Hold in Service Manager, to the original requests date & time

Defect Fixes

  • Fixed issue when importing historical diary entries that have a Time Spent value of NULL

v1.2.1 28/04/2016

Features

  • Takes request prefix from Application Settings rather than import tool

v1.2.0 27/04/2016

Features

  • Improved import performance:
    • Streamlined API's and client side record caching
    • Multi-threaded the Request Association and File Attachment Import code
  • Extended field mapping, allowing more request fields to be written to, including class-specific extended table fields

Defect Fixes

  • Fixed race conditions in Goroutines
  • Fixed issue with MSSQL driver returning INT64 values, causing data conversion problems

v1.1.2 07/04/2016

Defect Fix

  • Corrected output of oldCallRef mapping variable

v1.1.1 07/04/2016

Features

  • Added additional stage & warning in file attachment import user confirmation;
  • Enhanced notification display using colour in CLI output

v1.1.0 06/04/2016

Features

  • Improved import performance using Goroutines & parallel processing of Hornbill request logging

v1.0.2 24/02/2016

Defect Fixes

  • No Default Service being assigned to imported requests when Service Name from Supportworks data contained a NULL value
  • Updated Request Status Matrix.

v1.0.1 01/02/2016

Defect Fixes

  • Added missing brace to ConfKnownError section of configuration file.

v1.0.0 - 22/01/2015

Initial Release

Retrieved from "https://wiki.hornbill.com/index.php?title=Supportworks_Request_Import&oldid=19445"