A frequent requirement is to migrate journaled data (Comments and Work Notes) from an existing ticketing system to Servicenow,

with the original Author and data visibile in the correct place in the Activity Log.

This information may also help for bi-directional integrations where there may be a requirement for worknotes to appear with the original author and date set.

[ Edit some of the approach below is covered in this Word Document from ServiceNow , which outlines a similar solution for CSM. I think the key differences are:

• our customer needed an incremental - repeatable process so I've constructed the coalesce key into sys_audit/sys_journal_field to be consistent - not using GlideCounter
• I updated sys_mod_count on the main ticket to correspond to the record_checkpoint otherwise I found when I manually add a work note after it disappeared
• We brought legacy numbers directly into the 'Number' field which is indexed - the Correlation id/Name fields aren't
• We are populating both sys_journal_field and sys_audit

]

The following approach should work with Jakarta/Kingston

Short Version

• Import work_notes and comments to sys_journal_field -
• for performance coalese to target.sys_id only. (use a source coalesce script if necessary)
• Copy sys_journal_field data to sys_audit also setting the following:
  
  • internal_checkpoint(Record Internal Checkpoint) = hex(update time in UTC Millis) + "0000001"
    
  • record_internal_checkpoint(Update Count) = update sequence for parent record ordered by date (1,2,3…)
    
  • oldvalue="JOURNAL FIELD ADDITION",
• Update/Increment sys_mod_count of the parent record to match the highest Update count in sys_audit.
• Do not bother with sys_history_set and sys_history_line. - these are regenerated on demand.
• Set sysAutoFields(false) to preserve sys fields for all updates / inserts above.

Longer version

The tables involved are:

Table	Comment
incident	The main ticket table. This may be whichever table contains the journaled field.
sys_journal_field	Table that contains individual updates to journaled fields.The main transform map should import into this table.Correlate on sys_id only. If there is no source sys_id then use a source script to generate a sys_id using md5 hash of (element,element_id, name, time , author)Set sysAutoUpdate(false)
sys_audit	The Audit TableAn onComplete script should duplicate the journal record into sys_audit. This is done during on complete so that update count is set correctlySys_id: Try to derive sys_audit.sys_id from the journal.sys_idThe record_checkpoint(Update Count) should be set according to date order. Also update incident.sys_mod_count The internal_checkpoint(Record Internal Checkpoint) must be set correctly to get the correct date in the activity log. It is the UTC millis in hex followed by "0000001". Note GlideCounter.next('internal_checkpoint')); returns the checkpoint for the current time, not the original update time.
sys_history_set / sys_history_line	These tables contain the Activity View.They are generated on-demand (eg when viewing the incident) from sys_audit. There is no need to import into these tables.They should be ignored.

A note on Indexes

The sys_audit and sys_journal fields are very large. To avoid performance issues ensure that all coalesce and queries are completely covered by existing table indexes.

If you need to further refine a query, restrict query to the indexed fields and then loop on the returned data and filter within the loop.

Table	Indexes
sys_journal_field	Index1: sys_idIndex2: element_id
sys_audit	Index1: sys_idIndex2 sys_created_onIndex3: document_key,record_checkpoint

The following process assumes you have already imported users and incidents.

To import journal records - prepare an import file containing one row for each journal update.

The row should contain:

Source Field	Comment
sys_id	The transform map should coalesce on sysid only.As there are only indexes on sys_id and element_id.Try to ensure sysid is generated in the import file and is static and unique for the instance. If there is no stable sys_id then use a source script in the sys_id map, and compute a sysid based on MD5hash of name(table), element_id, element and user
User	The user that made the update.This could be user_name or user.sys_id
element_id	The sysid of the record. Eg sysid of the incident.
name	The table that contains the journaled field - eg incidentNote this has changed in recent versions of ServiceNow.It used to be 'task' but for Jakarta you set the actual table name - eg 'incident'
element	The journaled field name
create/update date	Time of the update.

Create a script include to be called by the transform script with the following logic:

Transform Map Step	Journal Importer Script Include LogicPseudo Code - this is not valid javascript.Error Handling removed for clarity
onStart() {// load script includeji = new JournalImporter()	Initialise empty hash H.H will be a nested hash of H{table_name}{element_id}{date} = array of journal sysids
Coalesce on sysid only.If there is no source sysid then use a source script that calls ji.genSysIdFromSource(source);	genSysidFromSource(source) {var str = source.u_table_name + source.u_element + source.u_element_id +source.u_create_date;str = 'sjf:'+md5hash(str);str=str.slice(0,32);}md5hash:(text) {Return new GlideChecksum(text).getMD5();}
onBefore() {ji.onBefore(source,target); }	onBefore(source,target) { Copy fields that are not mapped. target.sysAutoFields(false); If (action == 'insert') { target.sys_id = this.genSysidFromSource(source); }}
onAfter () {ji.onAfter(source,target);}	journalHash : {},onAfter(source,target) { // Make a note of the journal sys id for later processing.. // This is psuedo-code you will have to create missing intermediate hashes Var list = journalHash{table_name}{element_id}{date}; list.push(target.getValue("sys_id"));}
onComplete	Foreach table_name in journalHash { Foreach element_id in journalHash{table_name} { Var ticket = new GlideRecord(table_name); ticket.get(element_id); Update_count = +ticket.getValue('sys_mod_count'); date_list = Object.keys(journalHash{table_name}{element_id}); date_list = date_list.sort(); Foreach date from date_list { journalId = journalHash{table_name}{element_id}{date_list[i]}; this.createAuditFromJournal(journalId, update_count ++update_count } Ticket.sys_mod_count = update_count; ticket.autoSysRules(false); ticket.update(); }}createAuditFromJournal(journalId,updateCount) { journal = new GlideRecord('sys_journal_field'); journal.get(journalId); Audit_id = "A"+journal.sys_id; Get Audit Record(Audit_id) or initialise new one. Copy fields from Journal to Auditdocumentkey = journal.element_id,tablename = journal.name,fieldname = journal.element,newvalue = journal.value,oldvalue="JOURNAL FIELD ADDITION",user = journal.sys_created_by,sys_created_by = journal.sys_created_by,sys_created_on = journal.sys_created_on,internal_checkpoint = this.gdtToCheckpoint('sys_created_on')},record_checkpoint = updateCount} // Same format as GlideCounter.next('internal_checkpoint'));gdtToCheckpoint: (gdt) { var n = gdt.getNumericValue(); return n.toString(16)+"0000001";},

Labels:

• Integrations
• Scripting and Coding