sys_audit table not returning results

[hetalvadanlal]

Hi

We have custom fields 'u_resolved_by' and 'u_resolved_date'.
These are populated when the status of Request is set to 'Resolved'

Before Eureka, these fields were not getting populated for records which were updated over integration layer

Now we need to fix historical records for which the 'u_resolved_by' and 'u_resolved_date' is not populated (for closed and resolved requests)

My requirement is to get the details when the record was set to 'Resolved' status

I tried to get this by querying sys_audit table with query
{documentid= ''request sys id'^fieldname='state'^newvalue='-15'}

Below is the script

getResolvedDetails: function(sys_id){

  try{

    var auditGR = new GlideRecord("sys_audit");

    gs.log("sys id = " + sys_id);

    auditGR.addQuery("documentkey", sys_id);  

    auditGR.addQuery("fieldname", "state");

    auditGR.addQuery("newvalue", "-15");

    auditGR.query();

    if(auditGR.next()){

      return auditGR;

    }

    }  

  catch(e){

    gs.log(e);

    return null;

  }

  return null;

}

This is not returning any results in Production

When i checked tables, i was able to see sys_audit, sys_audit0000 to sys_audit0031

I am not sure how to get these details

[Kalaiarasan Pus]

history line table is not a small table either ... you are possibly looking at tables which are more or less of same size and complexity ... and per me , it is even more complex .. there are 2 tables involved here. first sys_history_set and second sys_history_line...

[jagmjavier]

sys_history_line table is not populated automatically. It means that you can see register once you have visited the History of the register. If you havent visited the history register yet, this table will not contain the related information to that register.

I think that you should continue doing that though sys_audit table since this one has the data saves till the end of the time. However history tables data are removed after 30 days.

You can check this info on the following link: Viewing History Sets - ServiceNow Wiki

Regards

[hetalvadanlal]

Thank you Javier and Kalaiarasan