How to read data from the history tables

[jmweli]

Hi ServiceNow Developers,

I am facing the following situation: a business rule ran accidentally in production and caused one field (closed_at in the rm_enhancement table) to be updated incorrectly. This happened about a week ago when we did a production implementation. We did an 'Update All' on the enhancement table which was one of the steps required for that implementation. Managers only noticed yesterday when their reports produced unexpected results. 900 records were affected which is too much to correct manually. I need to recover the correct values for the closed date field and the only way I can do is to read the history table get the old value from it and put that into the close_dt on the rm_enhancement table. The table I am going after is the one that the History UI action points to. The problem I am having is first of all there are two history tables sys_history_line and sys_history_set and I am not sure what the difference between them is and which one to go after. Either way it does not matter which one I go for I am unable to get my script to do I want it to do. I am reading the rm_enhancement table and isolating all the records that were affected by the update, then for each record I am trying to go to the history table and look for a record that was created by the incorrect update so I can take the old value for the closed_dt on the history table and update the closed_dt on the rm_enhancement table. The problem I am running into is I am unable to find a match between the two tables (rm_enhancement & sys_history_line) even though I can see that the data I am looking for is there in the history table. I can read all the records that were updated from the enhancement table and display them however I am unable match any of them to the sys_history_line table. Would you please take a look at the script below and advise.

Here is the script :

readHistory();

function readHistory() {

  gs.log ("Job started");

  var count = 0;

  var queryString =

  "closed_atON2014-09-15@javascript:gs.dateGenerate('2014-09-15','start')@javascript:gs.dateGenerate('2014-09-15','end')^sys_updated_by=jmweli";

  var enhc = new GlideRecord('rm_enhancement');

  enhc.addEncodedQuery(queryString);

  enhc.query();

  while (enhc.next()) {

  count+=1;

  gs.log("enhancement is " + enhc.number + "close date is " + enhc.closed_at);

  var num = enhc.number;

  var hist = new GlideRecord('sys_history_line');

  hist.addQuery('set', num);

  hist.addQuery('label', 'Closed');

  hist.addQuery('user_id', 'jmweli');

  hist.addQuery('update_time', '2014-09-15');

  // enhUpdate.addQuery('number', 'ENHC0012001');

  hist.query();

  if (hist.next()){

  gs.log ("history rec found " + hist.set.getDisplayValue());

  }

  else {

  gs.log ("no history found");

  }

  // if (count == 1) {

  // break;

  // }

  }

  gs.log ("Total records found " + count);

  }

Thanks,

Johannes

[Slava Savitsky · Accepted solution]

To accomplish what you are trying to do, you need to use sys_audit table instead.

Understanding the sys audit Table - ServiceNow Wiki

To understand the difference between sys_audit table and the ones you were trying to use, have a look at the following article:

Viewing History Sets - ServiceNow Wiki

[jmweli]

Hi Slava,

Thanks for your response, the solution you gave me works fine

[jmweli]

I am trying to see how to mark this questions as answered.

[dlennartsson]

How did you get this to work? I have to update one field in over 7,000 records from the previous value of the field.

I see this in the history, but don't know how to access this table.