Help with the best way to report on Location field updates including the previous value / new value

[jlaue]

Hello - 

I am trying to find the best way to report on Location field updates, where I can include the old/previous values and the new/current values.  Typically when the location information updates, there are multiple field updates happening at the same time based on our daily data import feed we are running to update Locations from an external data source via JDBC connection.

For example:

Location "123" has the following fields updated today:  Street, City, State, Zip Code, Phone Number.

How can I report on these updates with the following output, like an email notification that includes this info the following way:

Subject:  Location 123 has updates

Body:

Street:

Old Value = 

New Value = 

City:

Old Value = 

New Value = 

and so on....

I have been through Community and have discovered that I may be able to run a business rule that can retrieve 'previous.fieldname', but that would only account for one field update, where I would need multiple field updates all in one report.  

I also found a very old solution, however this one does not include the 'Old/Previous' value:

https://servicenowguru.com/scripting/business-rules-scripting/checking-modified-fields-script/

When I look at the Audit History of the Location records, I can see the line items from the sys_history_line table, even added that table into the UI Properties so I can try and report on that, however the table never shows up in the reporting module even after adding it based on this:

https://docs.servicenow.com/en-US/bundle/tokyo-now-intelligence/page/use/reporting/concept/c_ReportO...

I would imagine that other customers would have a requirement like this, so I was hoping the Community would be able to provide some guidance for this.

Thanks!!

[Bert_c1]

A business rule has access to all field in the 'current' and 'previous' objects of the record. Do NOT rely on Audit data, bad practice to query the sys_audit table. I have an update set that does what you ask for incident and sc_request tables.  the BRs capture both current and previous values of the interested fields, and create a record in my custom 'audit' table.  the BR runs on Insert and Update, and for "When" if 'After':

(function executeRule(current, previous /*null when async*/) {

	// create audit history.
	var fieldName = "";
	var newValue = "";
	var oldValue = "";
//	gs.info("PAN Audit Incident: " + current.number + ", Field: " + current.fieldname + ", Old: " + current.oldvalue + ", New: " + sarec.newvalue + ".");
	if (current.assigned_to != previous.assigned_to) {
		fieldName = 'assigned_to';
		newValue = current.assigned_to;
		oldValue = previous.assigned_to;
		createRecord(current, fieldName, newValue, oldValue);
	}
	if (current.assignment_group != previous.assignment_group) {
		fieldName = 'assignment_group';
		newValue = current.assignment_group;
		oldValue = previous.assignment_group;
		createRecord(current, fieldName, newValue, oldValue);
	}
	if (current.priority != previous.priority) {
		fieldName = 'priority';
		newValue = current.priority;
		oldValue = previous.priority;
		createRecord(current, fieldName, newValue, oldValue);
	}
	if (current.state != previous.state) {
		fieldName = 'state';
		newValue = current.state;
		oldValue = previous.state;
		createRecord(current, fieldName, newValue, oldValue);
	}


	function createRecord(curRec, recFieldName, recNewValue, recOldValue) {
		// Create audit record
		var panAudit = new GlideRecord('u_pan_audit');
		panAudit.initialize();
		panAudit.u_documentkey = curRec.sys_id;
		panAudit.u_fieldname = recFieldName;
		panAudit.u_newvalue = recNewValue;
		panAudit.u_oldvalue = recOldValue;
		panAudit.u_tablename = 'incident';
		panAudit.u_updated_on = curRec.sys_updated_on;
		panAudit.u_user = curRec.sys_updated_by;
		if (!panAudit.insert()) {
			gs.info("PAN Audit Incident: Error creating audit, Table: " + panAudit.u_tablename + ", Field: " + panAudit.u_fieldname + ", Old: " + panAudit.u_oldvalue + ", New: " + panAudit.u_newvalue + ".");
		}
	}

})(current, previous);

See if the above helps you.

[Bert_c1]

And you can then define reports on you custom table.