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

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();

   }

}