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

[SanjivMeher]

Hi Rick,

Why not upgrade your Fuji instance to Istanbul, instead of migrating data to a new instance?

[rickseiden_info]

The original Fuji instance was run by HR, and only has HR Case running in it.   The new instance is run by IT, and has all the typical ITSM stuff in it, like Incident, Change and Problem.   We're migrating the HR team to the IT instance to cut back on licensing costs, and a few other things.   Since the new instance is in production, and has live Incident, Change and Problem data (among other modules), we can't upgrade the HR instance to Istanbul and then clone it to the IT Instance, then build everything on top of that.

We're really in a situation where we have to manually move the data from one system to another.

[rickseiden_info]

One thing I forgot to mention.   I've tried migrating a case to the new instance, then using REST to get the sys_history_line records and attach them to the sys_history_set record in the new system for the HR Case.   That works in that I now see all the activity data in the HR Case.   But when I delete the sys_history_set and sys_history_line data (as the system will do after a while--I think I heard 8 weeks), and then go back into the case, the activities data isn't there.   It regenerates the initial values that come over with the HR Case, but not the rest of the values I used REST to pull over.   So that won't work for the long run, either.

[SanjivMeher]

So the sys_audit stores data only if audit is activated on that table. And you mayn't need this data at all.

Also the table rotation only happens fr sys_history_line table. So I dont think it deletes the data. But it moves the data to its sub table may be.

Also the other historic data you may need is sys_email. So make sure to migrate that as well.