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

[Pradeep Sharma]

Hello Hetal,

You may find the below link as helpful.

http://www.servicenowguru.com/system-definition/remove-activity-log-journal-entries/

[hetalvadanlal · Accepted solution]

Hi Pradeep

Thank you for response

I have already looked at this post but it was not helpful

Got a response from Servicenow though

The sys_audit table is set for rotation every 30 days

So i will end up querying individual sys_audit_#### tables to find the relevant records

Also owing to the fact that this tables are of massive size, it is recommended not to query them directly

They might pull down the system performance

So I will be cloning my environment and extracting relevant data and then pushing updates to live instance

Regards

Hetal

[Pradeep Sharma]

Agreed..That is the best approach.

Can you please mark the thread as answered if this answers your question.

[hetalvadanlal]

Hi Pradeep

I am still waiting for someone to respond with any implementation done on sys_history_line which alleviates the need of using sys_audit table all together so that i can avoid querying multiple tables

Else will mark is as answered in a coupla days

Regards

Hetal