Delete duplicate entries from sys_history_line table

[Imran1]

Hi All,

I have performance issues in my instance as I am loading audit records(sys_history_line) while the record is being displayed in my Service Portal.

I noticed that few of the my scripts had made enormous duplicate entries into the actual records thus creating volumes of historical records for few of the fields.

I am deploying two scripts one to identify duplicates and other to delete them.

My scripts that updates the values in a newly created field [u_reason(string 1000 char)] doesn't seem to work but if I manually enter any values in the field, it takes.

Script:

var gr = new GlideRecord('sys_history_line');
gr.addEncodedQuery("field=clint_owne^ORfield=do_actual_due_date^ORfield=do_due_dt^ORfield=do_in_sub_dt^ORfield=open_age^ORfield=u_assigned_to_list^ORfield=bucket^set.table=Table_name^set=b79f31a887ef1150316933fd3fbb35ba");

gr.addNullQuery('u_reason');
gr.query();
while (gr.next()) {
gr.u_reason = gr.set+ gr.field + gr.set.table + gr.user_name + gr.old + gr.getValue('new')+gr.user;
gr.autoSysFields(false);
gr.setWorkflow(false);
gr.update();
}

I have restricted the above query & update just to one record but once this works I will be doing it for entire table

I have modified the ACL for the table and allowed write and delete operations too along with removing nobody role and adding admin role here.

I know that making any entries/modification into this table is not recommended but I want you assist me on how to make the above script work so that my instance performance improves

I understand that sys_history_line & sys_history_set generates the records from the rotational tables  - Is there anyway that I can modify the content within the core table from where it generates this. { I have already deleted duplicate entries from sys_audit table fyi }

Appreciate your suggestions on how to make the script work and any other inputs.

Regards,

Imran

[Gunjan Kiratkar]

Hi @Imran1 ,

Please refer the response from chuck tomasi

Is there a way to update the 'Update Time' field in 'sys_history_line' table  

[Imran1]

Hi Gunjan,

This is not helping me - I understand all that it involves and the intent of why making changes are not permitted. However, as I said I am able to make manual update as well as delete the record from sys_history_line table.

If you know anything I could do to make it work it would be nice

Regards,

Imran

[Imran1 · Accepted solution]

Hi All,

I was able to achieve the desired result using the below script 

var arr = [];
var trackerObj = {};
var gr = new GlideRecord('sys_history_line');
gr.addEncodedQuery("field=clint_owne^ORfield=do_actual_due_date^ORfield=do_due_dt^ORfield=do_in_sub_dt^ORfield=open_age^ORfield=u_assigned_to_list^ORfield=bucket^set.table=Table_Name");

gr.query();
while (gr.next()) {
var context = gr.set+ gr.field + gr.set.table + gr.user_name + gr.old + gr.getValue('new')+gr.user;
arr.push(context);
if(trackerObj[context]){
trackerObj[context].push(gr.sys_id.toString());
}
else{
trackerObj[context] = [];
trackerObj[context].push(gr.sys_id.toString());
}

}

for(var combination in trackerObj){
if(trackerObj[combination].length > 1)
gs.info(combination + ":" +trackerObj[combination].toString());
var deleteGR = new GlideRecord('sys_history_line');
deleteGR.addQuery('sys_id','!=',trackerObj[combination][0]);
deleteGR.addEncodedQuery('sys_idIN'+trackerObj[combination].toString());
deleteGR.query();
//gs.info('RowCount: '+deleteGR.getRowCount());
deleteGR.deleteMultiple();
}

Thank You for your response

Imran