Question on scoped custom application, sys_journal_field & sys_audit tables

[Shillu]

I am working on a custom scoped application using which users can search on legacy IT Help Desk application data. One part of the work involves importing the legacy help desk ticket records into an extended Task table in custom scope. I have done that and mapped the fields. Next step is to import the notes & comments from the from the legacy table into servicenow tables so that they can be listed in the chronological order in the activity formatter. For this, I had extended the global sys_journal_field table in the custom scope.(made sure to flip back the "Extensible" property of sys_journal_field table after created the extended table).

Question 1: Is extending sys_journal_field table the best way to do this? 

 Now comes the sys_audit table that stores the transactional data. I extended the sys_audit table  as I did the sys_journal_field in the custom scope. (Made sure to flip the "extensible" back to no). Using the records in the custom sys_journal_field table, I imported the records into the custom scoped & extended version of the sys_audit table. What I found after the import is that the records did not import into the custom scoped extended sys_audit table. Instead I found data in the global scoped sys_audit table.

Question 2: Is extending and using sys_audit table in custom scope possible? 

Question 3: How can  I populate the internal_checkpoint field in sys_audit table? What I found in the community search is that, it's the hex of the datetime stamp concatenated with 0000001 string. Has anyone written a transform field script for this field?

Appreciate any assistance on these questions. Thanks!

[Jon Barnes]

I would definitely not extend journal and audit tables. There can be issues with that, especially because of table rotation / extension. Why would you need to extend them anyway? You should be able to copy entries and tie them to your new copied records. I have tried to rebuild the audit records with those checkpoints, but couldn’t find an accurate way to walk back and calculate what I needed. I was able to get the history to render in the activities on the form, but they all showed up in one section like they were done at the same time.

[Shillu]

Thank you, Jon! You have confirmed what I read after posting this question. The rotation/extension of sys_audit should just allow us to import those transaction/activity records. Also, this is just a one time import and "leave it alone" type of data.

Originally, I was thinking of cordoning off the custom scoped application and that's why decided to extend the journal and audit tables. You are correct that the "under the hood" tables like sys... may have other consequences when they are extended.

I have the import working, except for the datetime stamp on each activity. Chronological order of the activities would help end users, so I will continue to put some more time before going with what I have.

Thanks again!

[Shillu]

Just wanted to share with the community how we imported the legacy help desk records from a sql server database and was able to list the comments and notes in chronological order.

The plan for the import was developed from the article posted at https://community.servicenow.com/community?id=community_article&sys_id=9decea65dbd0dbc01dcaf3231f961... .

What we did from the article above is below:

1. This was a one time import and the data would be available to end users in read only mode.

2. Created a main custom table (extending task) in a scoped application.

3. Used sys_journal_field and sys_audit table for storing the notes and comments.

4. As detailed in the document, set autoSysfields & setWorkflow methods to false on the transform map.

5. Converted the DateTime values to varchar in the SQl query. Used GlideDateTime class to create a GlideDateTime object using the string DateTime.

6. The article I cited above had source sys_id populated using MD5Hash (of a combination) of field values. I did not use that. I used the OnBefore Event Transform Map to map the import set row's sys_id (source.sys_id) to the target.sys_id. In this case source is the import set table and target was the sys_journal_field table. 

7. Used the OnAfter Transform map Event to create sys_audit table record using the values from the sys_journal_field record.  OnAfter is not one of the best practices as it would take a hit in performance, but, on the other hand I had all the objects and their values available right there. Made sure to set autoSysfield and setWorkflow to false upon sys_audit record.initialize(). 

8. record_internal_checkpoint was calculated using the function below in OnAfter script. I did not use the UTC milliseconds, I used GMT milliseconds. Apparently, they both are same, except that GMT has Timezone.

function getIntCheckPoint(createdDate) {
var createDateTime = new GlideDateTime(createdDate.toString());
var utcValue = createdDateTime.getNumericValue();
var hexUtc = utcValue.toString(16);
return hexUtc+"0000001";

9. UpdateCount was calculated in the sql query itself. Used the "row_number over(partition by exp order by exp DESC) as fieldname" syntax.

In the end, it all worked very well and we have the comments and notes in the chronological order.