regenerating sys_history_line and sys_history_set with updated sys_audit records

[david631]

I'm trying to import back-dated comments and work notes from some external systems. I had success using this guide to load the comments and work notes: How to migrate/import Comments and Work Notes from External Systems; however, the datetime values are all wrong. 

Chuck Tomasi noted that the display date/time value for activity history items is derived from sys_history_line records ("Copy Journal Entries & Keep in Date Order"):

Modifying the sys_journal_field's sys_created_on value has no impact on the displayed date/time value in the activity history - that comes from sys_history_line record. Unfortunately, sys_history_line is a read-only table.

ServiceNow docs state the system generates sys_history_line records from the Audit table on request ("Differences Between Audit and History Sets"); however, sys_history_line records are not generated for Audit records which I update with new sys_created_on and internal_checkpoint values.

My procedure is to...

1. delete all history set and history line items for a given case
2. update the date and internal checkpoint on an audit record
3. re-generate the history for the case

The new history consistently omits the updated audit record. Why is this?

[NagyDan1 · Accepted solution]

You need to modify the relevant Audit records too. But when you "hack" Audit records you need to do it in batches. One batch meaning Audit records with the same checkpoint. You need to update their heckpoint otherwise the system will revert them back.