Understanding Audit History and how it works

[scottangehr]

I've recently enabled the audit history on a table and have been pulling reports.   What I'm not understanding is that I have multiple records that are not showing in an appropriate time frame.   For example, an incident was closed on 4/15/15 but the audit for the state field did not show up until 5/2/15 which seems to have been provoked by querying the specific record.

ID	Label	Old	New	Type	Update number	Update time
POI21623	State	Open	Work in Progress	Audit	3	4/27/2015 9:41
POI21623	State	Work in Progress	Pending	Audit	4	4/27/2015 10:28

The state was changed to Closed on 2015-04-27 10:36:03 and I just pulled this data.

How long does the complete audit history of a record take to show in the audit history?

[scottangehr]

Side note: after I sent this - the state change to closed was updated yesterday which seems occurred after I viewed the record.   Is that when the final audit occurs, after the record is viewed?

[brian_quinn]

Scott,

Based on the columns you listed it looks like you are looking at the sys_history_line table.   This table and the sys_history_set table are populated on demand when you view a record.   The sys_history_line table is on a table rotation, so records are only left in that table for 8 weeks.   The sys_audit table is the real table that tracks all of the changes as they happen, but this can be a very large table!   You should avoid querying this table whenever possible, but if you must, you should use this URL:

https://instance.service-now.com/sys_audit_list.do?sysparm_filter_only=true

That URL will load a page where you can configure a filter before trying to query the table.   I recommend including a created on date filter in addition to other filters to narrow down your results.   The sys_audit table uses table extensions, which create multiple tables that each hold 1 month worth of data, the narrower your date filter the less tables that have to be queried.

Table Extension - ServiceNow Wiki

Thanks

Brian

[scottangehr]

Good day Brian

Thank you for the info.   Great stuff.   However, I think I may be pulling different data.   I checked the URL you included and that info looks to be pulling audit records which shows sys_id's in the fields rather than the actual label.   I have activated both the Audit Records and Audit History related list items on incidents. The Audit History shows the data that the end user can understand.

I believe they are all pulling from the same sys_audit table.   So then let me pose this question.   Do you believe there is a better way to capture all the changes of a record in a report for an end user?   I know a specific VP that wants to see specifically the history of changes to the State, but not limited to other fields.

Thank you again,

Scott

[brian_quinn · Accepted solution]

Scott,

Yes, the "Audit History" and "Audit Records" are both generated on demand by pulling data from sys_audit.  

If you want to report on the "Audit History" table (since it is more end user friendly), you could create a scheduled job to force the "Audit History" and "Audit Records" tables to update for the records you are interested in, and then run a scheduled report to report on the data in the "Audit History" table.  

Please make sure you have some limit for the number of records being updated in the Audit tables!   Generating the Audit data for all incidents could potentially cause performance issues.

A scheduled job similar to this would populate the Audit tables for all incidents updated this quarter:

var gr = new GlideRecord("incident");

gr.addQuery("sys_updated_on", ">", gs.beginningOfThisQuarter() );

gr.query();

while( gr.next() ){

  new GlideHistorySet("incident", gr.sys_id).generate();

}

Thanks

Brian