Import Comments and Work Notes

[wipeout630]

I am migrating Incidents from a different ticketing system into Service now.   As part of this migration, I have an excel file full of comments and work notes that need to be added to the migrated Incidents.   Some of the questions that I can't find answers to:

• When setting up the transform map, which table do the work notes/comments map to?
• How do I associate the work note/comment in the excel file to the Incident?
• Is it possible to set who & when the work note/comment was created?
  • I have configured the Incident import so that the date created reflects correctly, I'd like the work notes/comments to also reflect the date/time they were created and who created it.

[jamesmcwhinney]

If you dont mind using c# and webservices, here is one way to do it:

How to import legacy incidents, comments and worknotes via c# & web services

Cheers,

- James

[jmoral2]

Hi all,

As explained here:  https://www.servicenow.com/community/developer-articles/how-to-migrate-import-comments-and-work-note...

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