Generate Report using metric and databse view

[servicenowt7048]

How can I calculate the duration between specific state transitions in a record, such as the time spent from 'Open' to 'Work in Progress' and from 'Work in Progress' to 'Closed'? I want to achieve this using Metric Definitions  Any guidance or best practices would be appreciated."
Note: avoid creation of field in form for keep the value of duration.

Thanks in advance

[servicenowt7048]

How can I create a metric on the 'state' field to capture the duration between the 'New' and 'Work in Progress' states only? I want the metric to trigger when the state changes from 'New' and stop when it reaches 'Work in Progress,' without recording other state transitions. Any best practices or guidance would be appreciated.
@Ankur Bawiskar 

[Dr Atul G- LNG]

Hi @servicenowt7048 

You can use PA (KPI's) which is quick easily available. 

[servicenowt7048]

Hi AG
actually in my project they are not suggesting go with KPI
i tried with script calculation in metric definition table
i wrote the script like this

// Define the metric definition sys_id (replace with the actual sys_id of your metric definition)

var definitionSysId = "95f786df831a5210303d5fd6feaad3c2"; // Replace with your metric definition sys_id

gs.print("test"+definitionSysId);// Define the metric definition sys_id (replace with the actual sys_id of your metric definition)

//var definitionSysId = "95f786df831a5210303d5fd6feaad3c2"; // Replace with your metric definition sys_id

// Query the table where you want to calculate metrics (e.g., "incident")

var gr = new GlideRecord('u_testtime'); // Replace 'incident' with your target table

gr.addEncodedQuery('stateIN1,2'); // Fetch only records with states relevant to your metric

gr.query();

while (gr.next()) {

// Check if the state has transitioned from 'New' (1) to 'Work in Progress' (2)

//if (gr.state.changesTo(2) && gr.state.was(1)) {

gs.print("test123"+gr.state);

gs.print("Processing record: " + gr.sys_id);

createMetric(gr);

//}

}

function createMetric(record) {

// Create a MetricInstance object with the provided definition and current record

var mi = new MetricInstance(definitionSysId, record);

// var metricRecord = mi.getNewRecord();

// gs.print("Target Table: " + metricRecord.getTableName());

// Check if the metric already exists to avoid duplicates

if (mi.metricExists()) {

gs.print("Metric already exists for record: " + record.sys_id);

return;

}

// Create a new record for the metric instance

var metricRecord = mi.getNewRecord();

metricRecord.start = record.sys_created_on; // Start time

metricRecord.end = record.sys_updated_on; // End time

metricRecord.duration = gs.dateDiff(metricRecord.start.getDisplayValue(), metricRecord.end.getDisplayValue(), true); // Calculate duration

metricRecord.calculation_complete = true; // Mark the calculation as complete

metricRecord.insert(); // Insert the metric record

//var insertSysId = metricRecord.insert();

//gs.print("Record Inserted with sys_id: " + insertSysId);

gs.print("Metric created for record: " + record.sys_id);

}

I have implemented a script calculation in the Metric Definition table to create entries in the Metric Instance table. While the script works fine in the Background Script (logs confirm processing records), no entries are being created in the Metric Instance table.
could you please help me to get that