Query sys_audit in background script using GlideRecord and retrieve reference, choice values

[IanBradley1]

I'm querying sys_audit using GlideRecord in a background script and wish to retrieve the display values of the sys_audit.oldvalue and newvalue fields, for selected columns.  e.g. if the tablename is 'incident' and 'fieldname' = 'state, the oldvalue field will have the state number.  I need the actual state description from the sys_choice table.   If the fieldname is 'assignment_group' oldvalue will have the sys_id of the group, I need the group name, etc.   Basically, simulating what the dv_ fields provide, but in a background script.  Can anyone point me in the right direction?

[MrMuhammad]

Hi @IanBradley,

Below script will help you get started. I GlideRecord sys_audit table first and passing table name, field name and value to the dynamic function that will GlideRecord sys_choice table to fetch Label of choice. 

var historyData = new GlideRecord('sys_audit');
//historyData.addQuery('name', value); //add your filters here.
historyData.query();

while (historyData.next()) {
    gs.info(historyData.tablename + "-" + historyData.fieldname + " - " + historyData.newvalue);

    if(historyData.newvalue)
	    auditData(historyData.tablename, historyData.fieldname, historyData.newvalue);
}

// Fetch Label/Display Value based on table, field and value combination
function auditData(tableName,fieldName, valueName) {
	
	var record = new GlideRecord('sys_choice');
	record.addQuery('name', tableName);
	record.addQuery('element', fieldName);
	record.addQuery('value', valueName);
	record.setLimit(1); 
	record.query();
    
	if (record.next()) {
		gs.info(record.label); // replace with your logic		
	}
}

Please mark this correct & helpful if it answered your question.

Thanks & Regards,
Sharjeel

[IanBradley1]

Thanks Sharjeel. This is helpful. But how do I know to go sys_choice, vs sys_user vs sys_user_group, etc?  Is there a way of automatically determining which reference table to lookup based on the tablename, columnname combination?  Or, do you need to apply case based logic ?

[MrMuhammad]

We can design our script that way but all the tables involve would have huge data and in order to achieve this there would be bunch of GlideRecords involve based off table name and query it requires that will ultimately degrade the performance. I will suggest going case based logic.

Can you please share your use case that way may be I can give you better suggestion.

Thanks & Regards,

Sharjeel

[hammad ul aziz2]

here is the script that automatically determines which table to look for in case if the sys_audit table query is returning the reference field.

var referenceClass='';
	var historyData = new GlideRecord('sys_audit');
	historyData.addEncodedQuery('tablenameSTARTSWITHincident^documentkeySTARTSWITH552c48888c033300964f4932b03eb092^fieldname=assignment_group^ORfieldname=caller_id');// ihave placed and encoded query just to limit the search for testing purpose
	historyData.query();
	while (historyData.next()) {
		//gs.info(historyData.tablename + "-" + historyData.fieldname + " - " + historyData.newvalue);
		var gDictionary=new GlideRecord('sys_dictionary');
		gDictionary.addQuery('elementSTARTSWITH'+historyData.fieldname);
		gDictionary.query();
		if(gDictionary.next())
		{
			//checking if the field is reference field
			if(gDictionary.internal_type.getDisplayValue().toLowerCase()=='reference')
			{
				referenceClass=gDictionary.reference;//getting the reference class name to get the actual value
				getReferenceData(referenceClass,historyData.newvalue,historyData.fieldname);
			}

		}
}
	function getReferenceData(referenceClass,valueName,fieldName)
	{
		var referenceGr=new GlideRecord(referenceClass);
		referenceGr.addQuery('sys_id',valueName);
		referenceGr.query();
		if(referenceGr.next())
			{
				gs.info(fieldName+' is '+referenceGr.getDisplayValue(referenceGr.getDisplayName())+' reference to '+referenceClass);
			}
		
		
	}

end results are shown below

please mark correct and helpful if this helped

thanks

Hammad