How to populate metric instance from audit for historical data?

[Utkarsha]

Hello Experts,

I have a requirement to create metric definition on particular table to track reference type of field changes,

i have created a metric as below-

Table: Cafe table(custom table)

Field: Data Visited(u_data_visited)

Type: Reference

Scripted Calculation:

var grp = current.u_data_visited;

if (grp != "") {

createMetric();

}

function createMetric() {

var mi = new MetricInstance(definition, current);

var gr = mi.getNewRecord();

gr.value=current.getDisplayValue('u_data_visited');

gr.insert();

}

The metric is working but not tracking data from sys_audit table
I want to populate metric instances from sys_audit table where this field has changed from old value to new value
Could anyone suggest me how can I achieve the same?

Should I write fix script, I tried the one below-

Fix Script:

// Requirement 3: Calculate for historical data

var records = new GlideRecord('u_cafes');

//Get all change requests that were closed a couple of quarters ago. Can also be something else...

records.addQuery('vendor=true');

records.query();

while (records.next()) {

//Trigger metric.update with the change request, send the trigger parameters

gs.eventQueue('metric.update', records, '[u_data_visited]', records.sys_mod_count, 'metric_update');

}
The issue is fix script is not showing metric field changed records under metric instance

Any thoughts on this,

@Ankur Bawiskar 
@Anand Kumar P 

Thank you in advance!

[AshishKM]

Hi @Utkarsha  ,

The metric definition for has option to select table name & field name , and here your main table is "Cafe table" and Field "Data Visited", so this metric definition will record any change in this field only. 

If you check, you cant select sys_audit table in this metric definition  form. Seems like you have 2 separate requirement ongoing change or old data fix. 

-Thanks,

AshishKMishra

[Utkarsha]

Hello Sir,

Thank you for replying. Yes I have two requirements

First one to capture latest records and second is to capture historical records data which already exist in the system.

For the first one metric is working but for historical records, i need to go with fix script I guess