History List audit details

[KeithD]

Hi all,
A while ago I posted a question about getting Web Service Scripts working from IBM z/OS cURL calls. Now they're all working 🙂 but I can't currently get all of the audit data I was trying to retrieve.
The scenario I have is ... a script runs to identify all incidents updated within a certain timeframe. It then uses the list of given sys_id values to query the sys_audit table for all audited activity in the same timeframe for the incident table.
This works fine when there is a sys_audit table entry but this appears not to be the case in numerous instances.
From the GUI, when you use the History ... List function, all activity seems to be presented correctly. I assume that the 'type' column shows 'audit' only when there is a corresponding entry on the sys_audit table. This issue I have is that the History ... List function also shows entries for things such as state changes with no 'type' value (which don't appear to be present on sys_audit). My question is .. what tables does the generate History List process use as well as sys_audit ? The data must be somewhere because the history list can display it.
Ultimately I'm trying to recreate the data that the History ... List function provides but in a SOAP/Script call and sys_audit seems to only represent part of the incident history. I just need to find out how the GUI builds the entire history and find out where the history data is for records without a 'type' ....
As an aside, I need to do this as part of an automated script and would prefer not to try to generate sys_history_set and sys_history_line entries though I would like to mimic what these processes do in terms of data access.

Hopefully this all makes sense...

Thanks,
Keith

[KeithD]

Hi all,
Sorry for replying, once again, to myself.
From what I can tell, our SN instance must still be using a false value for glide.sys.audit_inserts. I am not an admin of our set-up so I can't tell but there is a coincidence between the missing data and the original inserted values.
So .. I think I need to find where the initial values, that sys_history_set can reference, are held.

I suppose that in the following scenarios that the initial values may be derived as:
a) No updates have happened so the initial values are the current values.
b) As soon as an update happens, an audit record is written and the initial values are those represented in oldvalue on sys_audit.

I think our process will have to proceed with those assumptions until I find anything to indicate anything different.

Thanks,
Keith

P.S. Actually, I'm going to mark this as answered as I'm going to assume that the History->List details perform some logic to determine the old_value for the first audit_record to present that as the original, non audit type, value which makes sense. We could enable glide.sys.audit_inserts but I don't think that we really need to.

[CapaJC]

Sorry I didn't see your October post. You got it, History Sets use the oldvalue from the oldest sys_audit record for a field for a given record to determine its initial value. If there are no sys_audit records for the field, the current value is the original value.

One optimization on the sys_audit query would be to set a lower bound query on sys_audit.sys_created_on, since you don't need to look for sys_audit records older than the record itself.

It is good to have glide.sys.audit_inserts false if at all possible. In a typical instance, inserts accounted for over 80% of the size of the sys_audit table. Eliminating them gave a pretty significant performance improvement, not to mention significantly reducing the size of the database.

[KeithD]

Hi CapaJC,
Thanks for your comments.
From what I can tell we limit the sys_audit query sufficiently thus ...
Initial GR against incident:

var gr = new GlideRecord('incident');
gr.addQuery('sys_updated_on' , '>=' , start_date_to_use); - dates default to beginning and end of yesterday
gr.addQuery('sys_updated_on' , '<=' , end_date_to_use);

and then a new GR against sys_audit:

var gr2 = new GlideRecord('sys_audit');
var gr2aq= gr2.addQuery('documentkey' , '=' , gr.sys_id);
gr2.addQuery('sys_created_on' , '>=' , start_date_to_use);
gr2.addQuery('sys_created_on' , '<=' , end_date_to_use);

It occasionally times out with default SOAP timeout settings but an immediate resubmission usually fixes that.
We are thinking about increasing the value for glide.soap.request_processing_timeout but don't currently need to as we have coded in a resubmission in this event in our scheduling.

Thanks once again,
Keith