How to migrate/import Comments and Work Notes from External Systems

lordy · ‎02-04-2018

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 Table An onComplete script should duplicate the journal record into sys_audit. This is done during on complete so that update count is set correctly Sys_id: Try to derive sys_audit.sys_id from the journal.sys_id The 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_id

Index2: element_id

sys_audit

Index1: sys_id

Index2 sys_created_on

Index3: 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 incident Note 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 Logic

Pseudo Code - this is not valid javascript.

Error Handling removed for clarity

onStart() {

// load script include

ji = 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 Audit

documentkey = 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";

},

Jay Janssen · ‎02-13-2019

Hi, I'm wondering if anyone has any updates on this for London. I feel like I'm getting very close, but my comments/work notes won't show up on my incident after the import. If I add a comment/work note manually, the journals show up, but with the wrong timestamps and users.

Jay Janssen · ‎02-13-2019

Nevermind, I got my implementation of this working. Thanks for the guide!

mfestger · ‎07-17-2019

Would you happen to be able to share your process? Currently working in Madrid and not getting work notes to populate. Thank you!

Troy Riblett · ‎08-29-2019

This is a helpful post, but code itself is much more helpful in my opinion.

Also, one thing that this post doesn't mention is that the activity formatter will not look for any sys_audit records that have a created date before the ticket itself was created (which means if you are transferring a worknote from an old incident to an incident you just created that it will not show in the activity formatter).

All that aside, here is the code that I was able to get this working with (Note that this has not yet been thoroughly tested or deployed in a production system).

var JWTransferJournalFieldsUtil = Class.create();
JWTransferJournalFieldsUtil.prototype = {
    initialize: function() {
    },
	
	// Transfer all journal fields from one glide record to another
	transferJournalFields: function(origGR, targetGR){
		// Get all journal field updates
		var journGR = new GlideRecord("sys_journal_field");
		journGR.addQuery("element_id", origGR.getUniqueValue());
		journGR.orderBy("sys_created_on");
		journGR.query();

		// Set the update count to the current mod count
		var count = parseInt(targetGR.getValue("sys_mod_count"));
		
		while(journGR.next()){
			// Create a new sys audit record for the update
			var auditGR = new GlideRecord("sys_audit");
			auditGR.documentkey = targetGR.getUniqueValue();
			auditGR.fieldname = journGR.element;
			auditGR.newvalue = journGR.value;
			auditGR.oldvalue = "JOURNAL FIELD ADDITION";
			auditGR.reason = "Transfer from " + origGR.getDisplayValue();
			auditGR.record_checkpoint = count;
			auditGR.internal_checkpoint = this._gdtToCheckpoint(new GlideDateTime(journGR.sys_created_on));
			auditGR.tablename = targetGR.sys_class_name;

			// Set the audit user information information
			auditGR.sys_created_by = journGR.sys_created_by;
			auditGR.user = journGR.sys_created_by;
			
			// Set the time to current because audit history doesn't look for any audits created before the record
			// which means that if you are transferring an older ticket to a new one the worknotes and comments
			// will not show up
			auditGR.sys_created_on = new GlideDateTime();
			auditGR.newvalue = "Original note time " + journGR.sys_created_on + "\n" + auditGR.newvalue;
			
			// Create the record with no automatic system fields so that sys_created_on and sys_created_by are 
			// not overwritten
			auditGR.autoSysFields(false);
			auditGR.insert();

			// Create the journal record for the new record
			journGR.name = targetGR.sys_class_name;
			journGR.element_id = targetGR.getUniqueValue();
			var id = journGR.insert();
			
			// Update the "sys_created_by" to be the original commenting user
			var newJournGR = new GlideRecord("sys_journal_field");
			newJournGR.get(id);
			newJournGR.sys_created_by = auditGR.sys_created_by;
			newJournGR.update();
			
			count = count + 1;
		}

		// Make the target count each of the notes or comments as an update
		targetGR.sys_mod_count = count;
		targetGR.autoSysFields(false);
		targetGR.update();
	},
	
	
	_gdtToCheckpoint: function(gdt){
		var n = gdt.getNumericValue();
		return n.toString(16)+"0000001";
	},

    type: 'JWTransferJournalFieldsUtil'
};

mcconnellsj · ‎10-21-2020

Hi - I am having difficulty getting to work ... but first need to ask a silly question: what are the reasons against importing the journal entries via an import set in the same way - rather than generating them on-the-fly after the audit import?

I am pulling tasks, audits and journals and coalescing on sys_id on all tables.

neilpetertuffs · ‎06-28-2021

Great article, works with all journal and sys_audit records (using REST calls to merge two Paris ServiceNow instances together - allowed for generating new sys_ids and matching users). One caveat is NOT to be tempted to look at the newly created records BEFORE the journal and audit records are transferred (in that order) as the sys_history_set/sys_history_line tables will be created at that point in time (and these cannot simply be deleted).

Kenney1 · ‎07-29-2022

I am trying to integrate two instances that are both scoped using an API. How would I turn off system fields? Any insight?

kklosterman · ‎10-27-2022

Took me a while to figure this out but hope this code helps, or even provide comments of a different way of doing it.

I created a datasource/transform map into the sys_journal_field table. That is mostly 1 to 1 mappings besides an on after script to update the sys_created_by and sys_created_on.

then a onComplete transform script runs to insert into the sys_audit table.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    var iSetRow = new GlideRecord("u_spm_journal_field_migration");
    iSetRow.addQuery("sys_import_set", import_set.getUniqueValue());
	iSetRow.orderBy("u_created");
    iSetRow.query();

    while (iSetRow.next()) {
		
		var target_sys_id = iSetRow.getValue("u_element_id");
		var target_table_name = iSetRow.getValue("u_ï__name");
		var target_field_name = iSetRow.getValue("u_element");
		var target_value = iSetRow.getValue("u_value");
		var target_created = iSetRow.getValue("u_created");
		var target_created_by;
		
		var uGr = new GlideRecord("sys_user");
		if (uGr.get("email", iSetRow.getValue("u_created_by"))) {
			target_created_by = uGr.getValue("user_name");
		} else {
			target_created_by = iSetRow.getValue("u_created_by");
		}

        var sAudit = new GlideRecord("sys_audit");
        sAudit.addQuery("documentkey", target_sys_id);
        sAudit.query();

        var count = sAudit.getRowCount();

        var nAudit = new GlideRecord("sys_audit");
        nAudit.documentkey = target_sys_id;
        nAudit.fieldname = target_field_name;
        nAudit.newvalue = target_value;
        nAudit.oldvalue = "JOURNAL FIELD ADDITION";
        nAudit.reason = "Transfer from legacy Instance";
        nAudit.record_checkpoint = count + 1;
        nAudit.internal_checkpoint = _gdtToCheckpoint(new GlideDateTime(target_created));
        nAudit.tablename = target_table_name;

        // Set the audit user information information
        nAudit.sys_created_by = target_created_by;
		nAudit.sys_created_on = target_created;
        nAudit.user = target_created_by;
        nAudit.autoSysFields(false);
        nAudit.insert();

        var gr = new GlideRecord(target_table_name);
		if ( gr.get(target_sys_id) ) {
			gr.autoSysFields(false);
			gr.setWorkflow(false);
			gr.setValue("sys_mod_count", count + 1);
			gr.update();
		}

    }

    function _gdtToCheckpoint(gdt) {
        var n = gdt.getNumericValue();
        return n.toString(16) + "0000001";
    }

})(source, map, log, target);

Just like everyone else said, don't look at any records until this has been completed or delete out the sys_history_sets first for the particular records

jmoral2 · ‎02-23-2024

Hi all,

Thanks for your code! It help me a lot, so I'll try to give something back to you.

I need to import work notes and comments from an Excel so I use a TM and mapped sys_created and sys_updated, an used this onBefore Transform Script to prevent overwriting them. It's important that sys_updated and sys_created times are before the journal times, otherwise they might not be created correctly. Also created Journals in chronological order, which is the reverse order

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
	target.autoSysFields(false);
})(source, map, log, target);

An onAfter Transform Script

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
	var oJournalUtils = new JournalUtils();
    oJournalUtils.createAllJournals(source.u_comments_and_work_notes, target);
})(source, map, log, target);

And the class

var JournalUtils = Class.create();
JournalUtils.prototype = {
    initialize: function() {},

    gdtToCheckpoint: function(gdt) {
        try {
            var n = gdt.getNumericValue();
            return n.toString(16) + "0000001";
        } catch (oErrMsg) {
            new GSLog().logError("Script Include: JournalUtils; Function: gdtToCheckpoint. Error Message received from Try/Catch: " + oErrMsg);
        }
    },

    createJournal: function(sTable, sRecordID, sJournalType, sText, sUserName, oGDTdate) {
        try {
            var oGRUser = new GlideRecord("sys_user");
            oGRUser.get("name", sUserName);

            if (oGRUser && oGRUser.getUniqueValue()) {
                sUserName = oGRUser.user_name;
            }

            var oGRTarget = new GlideRecord(sTable);
            oGRTarget.get(sRecordID);

            var count;

            if (oGRTarget.getValue("sys_mod_count"))
                count = parseInt(oGRTarget.getValue("sys_mod_count"));
            else
                count = 0;

            // Create a new sys audit record for the update
            var auditGR = new GlideRecord("sys_audit");
            var journGR = new GlideRecord("sys_journal_field");
            auditGR.initialize();

            auditGR.documentkey = oGRTarget.getUniqueValue();
            auditGR.fieldname = sJournalType;
            auditGR.newvalue = sText;
            auditGR.oldvalue = "JOURNAL FIELD ADDITION";
            auditGR.reason = "";
            auditGR.record_checkpoint = count;
            auditGR.tablename = sTable;
            // Set the audit user information information
            auditGR.sys_created_by = sUserName;


            auditGR.user = sUserName;

            auditGR.sys_created_on = oGDTdate;
            auditGR.internal_checkpoint = this.gdtToCheckpoint(oGDTdate);
            auditGR.autoSysFields(false);
            auditGR.insert();

            journGR.initialize();
            journGR.name = sTable;
            journGR.element_id = oGRTarget.getUniqueValue();
            var id = journGR.insert();

            var newJournGR = new GlideRecord("sys_journal_field");
            newJournGR.get(id);
            newJournGR.sys_created_by = auditGR.sys_created_by;
            newJournGR.element = sJournalType;
            newJournGR.value = sText;
            newJournGR.update();

            count = count + 1;

            oGRTarget.sys_mod_count = count;
            oGRTarget.autoSysFields(false);
            oGRTarget.update();
        } catch (oErrMsg) {
            new GSLog().logError("Script Include: JournalUtils; Function: createJournal. Error Message received from Try/Catch: " + oErrMsg);
        }
    },

    createAllJournals: function(sWorkNotes, oTarget) {
        try {
            var re = /[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}.{1,100}(\(Work notes\)|\(Customer Communication\))/g;
            gs.log("createAllJournals sWorkNotes " + sWorkNotes);
            var aMatchDate = sWorkNotes.match(re);
            var aMatchText = [];
            var sDate, sName, sType;
            var i;
            for (i = 0; i < aMatchDate.length; i++) {
                if (i < aMatchDate.length - 1)
                    aMatchText[i] = sWorkNotes.substring(sWorkNotes.indexOf(aMatchDate[i]) + aMatchDate[i].length, sWorkNotes.indexOf(aMatchDate[i + 1]));
                else
                    aMatchText[i] = sWorkNotes.substring(sWorkNotes.indexOf(aMatchDate[i]) + aMatchDate[i].length, sWorkNotes.length);
            }

            for (i = aMatchDate.length; i >= 0; i--) {

                sType = aMatchDate[i].indexOf("(Work notes)") > 0 ? "work_notes" : "comments";
                sDate = aMatchDate[i].substring(0, 19);

                if (sType == "work_notes")
                    sName = aMatchDate[i].substring(22, aMatchDate[i].indexOf("(Work notes)") - 1);
                else
                    sName = aMatchDate[i].substring(22, aMatchDate[i].indexOf("(Customer Communication)") - 1);

                sName = sName.substring(0, sName.indexOf(" ")) + "," + sName.substring(sName.indexOf(" "), sName.length);
				gs.log("createAllJournals createJournal " + oTarget.sys_class_name +" , "+ oTarget.getUniqueValue() +" , "+  sType +" , "+ aMatchText[i] +" , "+  sName +" , "+  new GlideDateTime(sDate));
                this.createJournal(oTarget.sys_class_name, oTarget.getUniqueValue(), sType, aMatchText[i], sName, new GlideDateTime(sDate));
            }
        } catch (oErrMsg) {
            new GSLog().logError("Script Include: JournalUtils; Function: createAllJournals. Error Message received from Try/Catch: " + oErrMsg);
        }
    },
    type: 'JournalUtils'
};

Happy Coding!

Javier Moral