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

[jmweli]

Hi Debbie,

Its pretty easy to do, you must not go after the history file but you must go after the audit file (sys_audit). I had records that I updated using the 'Update all' functionality and that caused the business rule to run and it changed the closed date on all the records that were updated. I corrected this issue by going after all the records that were affected by this update in the enhancement table (you must know when the records were updated and by who). I then went after the audit table and matched the records that were created as a result of this update and took the old value and put that on the closed date in the enhancement table.

Here is the correct batch script I used in production to accomplish the task. P.S. note that the field I am interested in is the closed_at which is the Closed Date in my instance. I hope this helps, it certainly worked from me

readHistory();

function readHistory() {
gs.log ("Job started");
var count = 0;
var queryString =
"closed_atON2014-09-24@javascript:gs.dateGenerate('2014-09-24','start')@javascript:gs.dateGenerate('2...";
var enhc = new GlideRecord('rm_enhancement');
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', 'rm_enhancement');
  hist.addQuery('fieldname', 'closed_at');
  hist.addQuery('documentkey', enhc.sys_id);
  hist.addQuery('sys_created_by', 'jmweli');
  hist.addQuery('sys_created_on', 'CONTAINS', '2014-09-24');
  hist.query();
  while (hist.next()) {
    enhc.closed_at = hist.oldvalue;
    enhc.update();
   
    gs.log ("updated successfully " + enhc.number + " closed date is now " + hist.oldvalue);
   
   
  }
 
  // if (count == 10) {
    //   break;
    // }
  }
 
  gs.log ("Total records found " + count);
}

[dlennartsson]

Hi

Thank you so much for your quick answer. I am going crazy and SN is not able to help me.

I didn't do a proper select in an update from a record producer and all the records, approx. 8000 got updated.

I have to get the previous value for u_status from u_security table where company = 'FCD'.

I'm an old Oracle programmer, so this is difficult for me.

I have also been told that querying the sys_audit table is a no-no.

But I will try your query.

Thank you again.

From an American living in Sweden☺

Debbie Lennartsson

IT

[jmweli]

Hi Debbie,

ServiceNow is very easy once you get used to it. Do not query the audit table directly using the left appl nav, its too big it will drag your system down, in my instance in production audit table has 38 million records. However you can query it using a scheduled batch script as I did, you need to be more efficient though by using more fields in your query to narrow down your search to the exact records you want to go after.

Good luck!

[dlennartsson]

I don't understand this part. Do I need it? I want all records updated by a certain user.

var queryString =

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

var enhc = new GlideRecord('rm_enhancement');

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;

[jmweli]

you can build that part anyway you want. What it means is I want records on the enhancement table that were updated on 2014-09-24 and were updated by 'jmweli' (my user-id'). So basically I was doing production implementation on Sept 24 and I did an 'update all' that caused a business rule to run and update records incorrectly. So I want to go after all the records I updated that day. Do not worry about the other part of the script that goes after specific records, those are the ones I updated that day but which I know for a fact that I updated manually, they were not part of the 'update all' so I had to isolate those and make sure I don't update them in my script.

Let me know if you need more help. I have gotten a lot of help from the Community and therefore I really don't mind helping whenever I can.