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 · Accepted solution]

Getting the referenced table name is not an issue but as i mentioned above the performance is the key here, dictionary table might have over 1 lac+ records that we are going to query for each single sys_audit record. Again, understanding the use case is important before randomly executing the script. 

Completely dynamic code with improved performance.

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

    var dictionaryData = new GlideRecord('sys_dictionary');

    dictionaryData.addQuery('element', historyData.fieldname);
    dictionaryData.addQuery('name', historyData.tablename);
    dictionaryData.setLimit(1); // As there could be only 1 record so limit the record search to 1 in order to enhance performance.
    
    dictionaryData.query();

    while (dictionaryData.next()) {
        if(dictionaryData.reference)
            auditData(dictionaryData.reference, historyData.tablename, historyData.fieldname, historyData.newvalue);    
        else
            auditData('sys_choice', historyData.tablename, historyData.fieldname, historyData.newvalue);        
    }

}

// Fetch Label/Display Value based on table, field and value combination
function auditData(grTable, tableName, fieldName, valueName) {
	
    var record = new GlideRecord(grTable);
    
    if (grTable != 'sys_choice'){
        record.addQuery('sys_id', valueName);
    }    
    else{    
        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 guys!   A combination of your answers gave me the solution I'm after

[hammad ul aziz2]

please mark my answer at least helpful 😁

thanks

Hammad

[MrMuhammad]

Glad to be of Help. Have a great day 🙂