Reporting on Duration field in Incident Tasks

[Ross Walker]

i have a field on incident task called actual effort - this is a basic duration field where the engineer inputs the amount of time worked on an incident. im not using a time worked as it was requested as a simple input.
i have a business requirement to report on that duration field value per day, currently its for the last 2 entries
example

INC Number   Assigned to   Actual effort day1  Actual Effort day2
INCXXXXXX  User1            3hrs 30mins             5hrs 15mins

my thought is to set up a metric to capture the value at the end of the day but currently im unsure where to start

any help would be gratefully recieved 

[Rajesh Chopade1 · Accepted solution]

Hi @Ross Walker 

You can use a combination of Scheduled Jobs, Metrics, and a custom table to store daily records.

Custom table - create table with fields - INC number, Assigned to, Actual effort, date.

Schedule job - it should run Daily on specific time and in script section write logic to capture actual effort of each incident task.

var gr = new GlideRecord('incident_task'); // Replace with your task table name
gr.addActiveQuery(); // Optional: only include active tasks
gr.query();

while (gr.next()) {
    // Create a new record in Daily Actual Effort table
    var dailyEffort = new GlideRecord('u_daily_actual_effort'); // Replace with your custom table name
    dailyEffort.initialize();
    
    dailyEffort.u_inc_number = gr.sys_id; // Set the incident task reference
    dailyEffort.u_assigned_to = gr.assigned_to; // Set the assigned to field
    dailyEffort.u_actual_effort = gr.u_actual_effort; // Capture the actual effort
    dailyEffort.u_date = new GlideDateTime().getDate(); // Set the current date
    dailyEffort.insert(); // Save the record
}

now create report on your custom table and add columns for INC Number, Assigned To, Actual Effort, and Date.

I hope my answer helps you to resolve your issue, if yes please mark my answer helpful and correct.

thank you

Rajesh

[Rajesh Chopade1]

hi @Ross Walker 

You can definitely incorporate the calculation of the "Delta" field into your existing scheduled job for capturing daily efforts. This will streamline the process and reduce maintenance overhead.

Ensure your custom table has a new field named Delta.

Modify scheduled job as bellow:

var grIncidentTask = new GlideRecord('incident_task');
grIncidentTask.query();

while (grIncidentTask.next()) {
    var dailyEffort = new GlideRecord('u_daily_effort_metrics');
    dailyEffort.initialize();

    // Link back to the incident task
    dailyEffort.inc_number = grIncidentTask.sys_id; // Reference to the incident task
    dailyEffort.assigned_to = grIncidentTask.assigned_to; // Reference to assigned user
    dailyEffort.date = new GlideDateTime(); // Today's date

    // Get the last two entries for Actual Effort
    var actualEffortGR = new GlideRecord('u_daily_effort_metrics');
    actualEffortGR.addQuery('inc_number', grIncidentTask.sys_id);
    actualEffortGR.orderByDesc('date'); // Ensure you're ordering by date
    actualEffortGR.setLimit(2);
    actualEffortGR.query();

    var effortDay1 = 0;
    var effortDay2 = 0;
    var dayCount = 0;

    while (actualEffortGR.next()) {
        if (dayCount === 0) {
            effortDay2 = actualEffortGR.actual_effort_day1; // Most recent
        } else if (dayCount === 1) {
            effortDay1 = actualEffortGR.actual_effort_day1; // Previous day
        }
        dayCount++;
    }

    // Set today's efforts
    dailyEffort.actual_effort_day1 = grIncidentTask.actual_effort; // Adjust this to your actual field name

    // Calculate Delta
    dailyEffort.Delta = dailyEffort.actual_effort_day1 - effortDay2; // Current effort - Previous day's effort

    // Save the daily effort metrics
    dailyEffort.insert();
}

[Ross Walker]

Hi @Rajesh Chopade1 
thank you for that
im having issues incorprating it in to my current script and getting it to run

My Current script is

var gr = new GlideRecord('incident_task'); // Replace with your task table name

gr.addActiveQuery(); // Optional: only include active tasks

gr.query();

while (gr.next()) {

    // Create a new record in Daily Actual Effort table

    var dailyEffort = new GlideRecord('u_actual_effort'); // Replace with your custom table name

    dailyEffort.initialize();

    dailyEffort.u_number = gr.number; // Set the incident task number

    dailyEffort.u_assigned_to = gr.assigned_to.name; // Set the assigned to field

    dailyEffort.u_assignment_group = gr.assignment_group.getDisplayValue(); // Set the assigned to field

    dailyEffort.u_short_description = gr.short_description; // Set the short description field

    dailyEffort.u_actual_effort = gr.u_actual_effort; // Capture the actual effort

    dailyEffort.u_date = new GlideDateTime().getDate(); // Set the current date

    dailyEffort.u_task_type = gr.sys_class_name.getDisplayValue();// Link back to the task

    // Link back to the task

    dailyEffort.u_task_sys_id = gr.sys_id; // Set the incident task reference

    dailyEffort.insert(); // Save the record

}