Migrating Activities data between instances

[rickseiden_info]

I have a situation where we are migrating HR Case data from a Fuji system to our Istanbul instance, so cloning is not an option.

I want to get all the historical data that you see in the Activities section of the form, such as changes in State, Assigned to, and so on.

The standard answer I've seen, over and over again, is that the data is stored in the sys_audit table, and moved to sys_history_set and sys_history_line on demand.

The problem is that our sys_audit table only has 473 rows.

So I pick a random HR Case, and copy its sys_id, but don't go into it.   Then I go into sys_history_set and sys_history_line and look for entries there for that ticket.     Most of the time, because the tickets I'm picking are old, I don't see anything in these tables.   I check sys_audit, and there's nothing there, either.

Now I go into the ticket to look at it, and all the data in the Activities section is there.   If I got back to sys_history_set, there's now an entry for that record.   And if I go to sys_history_line, there are entries for that record.   But if I go to sys_audit, there still isn't anything in that table for the record in question.

So the system has to be pulling the data from someplace other than sys_audit (because it's not there to start with, and it's not there once it's regenerated).

I've used this thread to run some code that will generate the Activities section without going into the ticket.

I find a random HR Case, and make sure there is nothing in sys_history_set or sys_history_line. Then I run the following code:

new GlideHistorySet("hr_case", <hr_case.sys_id>).generate();

Then I check sys_history_set, and it has a record there for the case, and I check sys_history_line and it has records for the case.   But again, I check sys_audit, and there is still nothing there for the record.   If I go into the record, I see the Activities.

I've also run a background script that gets a record count for all the tables in sys_db_object.   When I look at that list, sorted most to least, and start at hr_case and work my way up, there is no table that I can see that would fit the bill.

I've turned on verbose SQL debugging and gone into an old HR Case.   I can see in the debug information that an "INSERT" SQL statement is being run to put data into sys_history_line, but I don't see a "SELECT" statement before that on any table that I'm unfamiliar with.

I have a HI ticket open for this, but it's getting nowhere right now, and they say to ask the community first.

Thanks,

Rick

[rickseiden_info]

I checked the dictionary record for the table, and Auditing is turned on for this table, so sys_audit should be used.

When you look at sys_history_line, you're looking at all the sub tables, too. Kind of like when you look at task, you're looking at all the records in incident, change and so on, only the mechanism is different.

So even if a sys_history_line record is moved to a sub table, it's still going to show when you look in sys_history_line.

Also, the system does delete the records from both sys_history_set and sys_history_line.   I know this because the records don't exist before I view a case, but do exist after I view the case.   So the data is rotated out of those tables, but stored someplace else.

[SanjivMeher]

This is what I got from below thread. Looks like it is going to be difficult. We had similar situation. We ended up leaving all the data in the old instance and keep the instance with us.

History Sets/Lines are a cached version of the history of the ticket that gets refreshed based off someone actually accessing the record in the UI.   After a period of time this cache is removed.   The data in the history set is a combination of records from the sys_audit, sys_journal_field, sys_email tables at least (I'm not sure how it generates relationship added/remove entries, they might be in sys_audit also.

Re: Getting activity log of specific incident through the REST API

[rickseiden_info]

This solution still uses sys_history_line, which won't work as the data isn't there.   If the data is there, putting it in sys_history_line in the new instance won't work as once it's deleted by the system, it doesn't come back.

[Sumanth16]

How did you move the attachments in HR case from Fuji to Istanbul version?

[sachin_namjoshi]

You can think of implementing datapump for your instance to migrate data to remote Database.

After you run datapump job, data in different tables ( sys_audit, sys_history_line) as per your datapump jobset configuration will be migrated to remote DB.

You can then export record from remote DB and then import in another instance.

Please look into below for more details about datapump.

Configuration

Regards,

Sachin