How to calculate old data values in a newly created metric definition.

[Vibhuti Rajput]

Hi All,

I have created a new metric for table change request -> field value duration -> on field state.

The metric instances are getting created correctly from the time the metric definition was created .

But our instance has changes being created from last 6 months .

How can i get their state duration so that i can report the duration correctly .

Any idea ? maybe by some background script or schedule job ?

what script to use?

[Boyan1 · Accepted solution]

Hello,

I have done similar tasks and I have generated the metric instances retroactively, based on the data available in sys_audit table using a script job.

Let me know I shall provide you a sample script.

Best regards,

Boyan

[Vibhuti Rajput]

Hi Boyan,

It would be really helpful .

Thankyou

[DJL2]

Hi Vibhuti,

As Boyan says, capturing Metrics retrospectively can be done via a script procedure - however, I would advise you carefully plan when to execute it as 6mths has the potential to create an enormous amount of records which could impact performance.

Thanks,

DJL

[Boyan1]

Hi Vibhuti,

You can use something like this:

var old_state = "";

var new_state ="";

var old_date = "";

var new_date = "";

var definition = new GlideRecord("metric_definition");
definition.get("name", <your metric name>)

var current = new GlideRecord("change_request");

current.addEncodedQuery (<add custom filter for the records in scope>);

current.query();

while(current.next())

history = new GlideRecord("sys_audit");
history.addQuery("documentkey", current.sys_id);

history.addQuery("fieldname", ''state")

history.orderBy("sys_created_on");

history.query();

while(history.next())

{

<do the calculation>

}

I have not tested this script and only outlined the main steps.

Best regards,

Boyan