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

[dlennartsson]

Hi again.

I have the script now and I think I am OK to go.

But I haven't been able to start it through scheduled jobs.

I've tried execute now

And

Execute at a certain hour.

It never seems to start.

I owe you big time on this on☺

Also, how do I read the gs.log?

Thanks, I owe you big time on this on☺

readHistory();

function readHistory() {

gs.log ("Job started");

var count = 0;

var queryString =

" u_status=6^ORu_status=5company=a45c524b0a0aa018017f0b32ad1d6187^{sys_updated_by=L6017}sys_updated_onON2014-10-23@javascript:gs.dateGenerate('2014-10-23','start')@javascript:gs.dateGenerate('2014-10-23','end')";

var enhc = new GlideRecord('u_security');

enhc.addEncodedQuery(queryString);

// enhc.addQuery('number', 'ENHC0012232');

enhc.query();

while (enhc.next()) {

// exclude tickets that were closed that day but not as part of the 'Update All'

//

// if (enhc.number == 'ENHC0010949' || enhc.number == 'ENHC0010258'

// || enhc.number == 'ENHC0010906' || enhc.number == 'ENHC0010370'

// || enhc.number == 'ENHC0010111' || enhc.number == 'ENHC0010879') {

// continue;

// }

count+=1;

var hist = new GlideRecord('sys_audit');

hist.addQuery('tablename', 'u_security');

hist.addQuery('fieldname', 'u_status');

hist.addQuery('documentkey',enhc.sys_id);

hist.addQuery('user','L6017');

hist.addQuery('sys_created_on', 'CONTAINS', '2014-10-23');

hist.query();

while (hist.next()) {

enhc.u_status = hist.oldvalue;

enhc.update();

gs.log ("updated successfully " + enhc.number + " closed status is now " + hist.oldvalue);

}

//if (count == 5) {

// break;

// }

}

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

}

Debbie

[dlennartsson]

Hi again,

OK, it started, but I got

total records found 0

I will have to keep trying.

Debbie Lennartsson

IT

[dlennartsson]

Never mind.

All is well.

Thank you for everything!

Debbie Lennartsson

IT