FIx script issue for assignement group old data update

[Barathk]

Hi All
I am trying to update the original assignement group this year created data but original assignement group not updating because audit table history_set date is clearing every 30 days.
while opening the old record ,audit table history created date is changing today date.
Please help how to update the last 6month assignment group data to the original assignment group.
Here I am sharing the script FYR.

var sctask = new GlideRecord('sc_task');

sctask.addEncodedQuery('sys_created_onONThis year@javascript:gs.beginningOfThisYear()@javascript:gs.endOfThisYear()^u_original_assignment_groupISEMPTY');
sctask.setLimit(2);
sctask.query();

while (sctask.next()) {
gs.log('Processing task: ' + sctask.number);

var history = new GlideRecord('sys_history_set');
history.addQuery('id', sctask.getValue('sys_id'));
history.query();

if (history.next()) {
gs.log('Found history set for task: ' + sctask.number);

var historySysId = history.getValue('sys_id');
gs.log('History sys_id: ' + historySysId);

var auditH = new GlideRecord('sys_history_line');
auditH.addQuery('set', historySysId);
auditH.addEncodedQuery("field=assignment_group^newISNOTEMPTY^oldISEMPTY"); // new is not empty and old is empty
auditH.query();

if (auditH.next()) {
gs.log('Found history line for task: ' + sctask.number + ' with new assignment group: ' + auditH.getValue('new_value'));

sctask.u_original_assignment_group = auditH.new_value; // give correct field name here
sctask.setWorkflow(false);
sctask.update();

gs.log('Task ' + sctask.number + ' successfully updated to original assignment group.');
} else {
gs.log('No matching history line found for task: ' + sctask.number);
}
} else {
gs.log('No history set found for task: ' + sctask.number);
}
}

[Nicholas_Gann]

sys_history_set and sys_history_line are not reliable tables to use for this type of exercise. You will want to query the underlying audit table instead.

Knowing about History sets (servicenow.com)

var sctask = new GlideRecord('sc_task');
sctask.addEncodedQuery('sys_created_onONThis year@javascript:gs.beginningOfThisYear()@javascript:gs.endOfThisYear()^u_original_assignment_groupISEMPTY');
sctask.setLimit(2);
sctask.query();

while (sctask.next()) {
	gs.log('Processing task: ' + sctask.number);
	var grAudit = new GlideRecord('sys_audit');
	grAudit.addQuery('documentkey', sctask.getValue('sys_id'));
	grAudit.addQuery('fieldname', 'assignment_group');
	grAudit.orderBy('sys_created_on'); // Get the oldest entry for this field
	grAudit.setLimit(1);
	grAudit.query();
	if (grAudit.next()) {
		var oldValue = grAudit.getValue('oldvalue');
		var newValue = grAudit.getValue('newvalue');
		var assignmentGroup = oldValue ? oldValue : newValue;
		sctask.u_original_assignment_group = assignmentGroup; 
		sctask.setWorkflow(false);
		sctask.update();

		gs.log('Task ' + sctask.number + ' successfully updated to original assignment group.');
	}
	else {
		gs.log('No matching audit entry found for task: ' + sctask.number);
	}
}

I had to update this slightly as there needs to be a conditional check around what field to get assignment group from in the audit record

[Barathk]

Hi @Nicholas_Gann 
In our instance I can see only one week audit logs are available  is there any possibility to retrieve the data for old tickets

[Nicholas_Gann]

If the sys_audit records are also cleared out then I don't think they can be recovered. If the sys_audit data is being cleared out if its over a week old, then I don't see much point in the history data being retained for 30 days. This is due to the history data not existing at all until you try to view it the first time, which is why it's not a reliable source of historical data.

You other option could be to use metrics. Do you have any entries that come up in this list view?

/metric_definition_list.do?sysparm_query=active%3Dtrue%5Efield%3Dassignment_group&sysparm_view=

Looking on my OOTB instance I see metric instance records are created on change of assignment group for the incident table and it seems to contain enough data to determine the historical assignment group, this is assuming this data is not being cleared out like everything else as arguably it should be if you're not even allowed to hold old audit data. In your above script example it only references sc_task, which is NOT one of the OOTB metric definitions, so if you only want it for that table then you might be out of luck