Is there a proper way to query for historical field change data (audit) via scripts?

bcronrath · ‎08-29-2016

Greetings SN Community,

I am trying to backfill some data for a set of incidents based on the history of their "state" field changes. I am wondering how I can go about this via scripting? I tried the sys_audit table but it appears that it can't really be queried as when I try to query it, it just seems to load endlessly until I kill the script. I figure there must be some way to query it or some similar table because I can go into any individual Incident through the UI, right click the header bar and go to History -> List and get exactly the type of information I'm looking for pretty instantaneously (and I only care about 1 field, this list gives me the history for every single field). Is there a different table, or specific way I need to structure my glideRecord queries in order to take a look at Incident field value history?

Best regards,

Brian

bcronrath · ‎08-30-2016

In case anyone is curious, here is an excerpt of how I handled one of the metrics, time to first acknowledgment (sanitized a bit to simplify it for general use):

var glInc = new GlideRecord('incident');

glInc.addNullQuery('u_time_to_first_acknowledged');

glInc.addQuery('opened_at','>','2016-01-01');

glInc.query();

while(glInc.next()){

   var lowerBound = new GlideDateTime(glInc.opened_at);

   var upperBound = new GlideDateTime(glInc.sys_updated_on);

   lowerBound.addSeconds(-1000); //To account for any odd timestamp issues

   upperBound.addSeconds(1000);

   var glA = new GlideRecord('sys_audit');

   glA.addQuery('documentkey',glInc.sys_id);

   glA.addQuery('sys_created_on' , '>=' , lowerBound);     //grab the length of time an incident has been open

   glA.addQuery('sys_created_on' , '<=' , upperBound);

   glA.addQuery('fieldname','state');     //only care about state changes

   glA.addQuery('newvalue','8');   //find any changes TO acknowledged

   glA.query();

   while(glA.next()){

   var glFirstResponse = new GlideRecord('u_incident_tracking');   //a table that I store historical data for state changes we care about, so that it can be reported on

   glFirstResponse.initialize();

   glFirstResponse.u_incident = glInc.sys_id;

   glFirstResponse.u_notification_state = 1;   //just a state signifying this was a first acknowledgement

   //since audit tables just stores users as strings, we need to find the proper user record we care about to signify which user acknowledged the ticket

   var glU = new GlideRecord('sys_user');

   glU.addQuery('user_name',glA.user);

   glU.query();

   while(glU.next()){

   var ourUser = gs.getUser();

   ourUser = ourUser.getUserByID(glU.sys_id);

   if(ourUser.isMemberOf("Application Support Team")){

   glFirstResponse.u_user = glU.sys_id;

   }

   }

   glFirstResponse.u_stop = glA.sys_created_on;

   glFirstResponse.u_start = glInc.opened_at;

   glFirstResponse.insert();

   var dc = new DurationCalculator();

   glInc.u_time_to_first_acknowledged = gs.dateDiff(glInc.opened_at.getDisplayValue(),glA.sys_created_on.getDisplayValue(),false);

   glInc.update();

   }

}

View solution in original post

Aka Guglielmo · ‎08-29-2016

Hi bcronrath,
Sys_audit is the right table.
Btw, as you noticed, it's not a good practice to query the audit log table, due to the large amount of records it contains.
So, try to explain your requirement, we can try to suggest you a better solution.

Regards,
William

bcronrath · ‎08-29-2016

Basically what I am trying to do is backfill some data for our incidents based on state changes. I have a system set in place that fills the values in moving forward, but am looking to back fill the data for incidents created in the 2016 calendar year. The fields I need to fill in are duration fields that track a few different durations for when a ticket was sitting in a state. For instance, time to first acknowledgement, time in customer court, time in operations court, time in development court, etc.

Is there a way around this outside of querying the audit table? I figure if I can simulate whatever call is made when going to History -> List on an individual incident I should be able to backfill this data, by looping through each incident created in 2016 and filling in the fields based on the historical state changes and their associated timestamps.

Best regards,

Brian

bcronrath · ‎08-29-2016

It actually looks like if I add a lower and upper bound to the sys_created_on field in addition to limiting to fieldname and documentkey I'm getting results pretty quickly now from the sys_audit table, I'll play around with this and see if perhaps I can create a script that only takes a few days worth at a time when querying the sys_audit table.

Best regards,

Brian

Aka Guglielmo · ‎08-29-2016

Consider also an alternative. If you have to restore few data, you can backup it into hidden fields inside the form.

William