Calculate Total Outage Duration Across Multiple Outages Related to Incident

[Jordan_M]

Hello, 

I am having some trouble writing the business rule script to get this working as intended. We have many situations where there are multiple outage records associated to a single incident. I am trying to get the difference in time between the earliest outage time among related outages and the latest end time. I am trying to write it to a duration field on the incident record. 

Any help would be greatly appreciated! 

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

	// Get the related incident record
	var outtask = current.task_number;
	var incident = new GlideRecord('incident');
	incident.addQuery('number', outtask);
	incident.query();
	if (incident.next()) {
// Query related outage records
var outageGR = new GlideRecord('cmdb_ci_outage');
outageGR.addQuery('task_number', incident.number);
outageGR.query();

var earliestStart = null; 
var latestEnd = null; 

while (outageGR.next()) {
// Find the earliest start time
if (outageGR.begin < earliestStart) {
	earliestStart = outageGR.begin;
	}

// Find the latest end time
if (outageGR.end > latestEnd) {
latestEnd = outageGR.end;
 }
}

// Calculate the duration
var duration = latestEnd - earliestStart;

// Set the Total Outage Duration field on the incident record
 var durationGDT = new GlideDuration(duration);
 incident.u_total_outage_time = durationGDT;

 incident.update();
	}

})(current, previous);

[Jordan_M]

I have tried the script a few different ways and attempted to accomplish the same in flow designer but unfortunately not having much luck. 

[Rajesh_Singh]

Your script looks quite good, but there are a few minor issues that need to be addressed. Here's a revised version of your script with comments explaining the changes:

(function executeRule(current, previous /*null when async*/) {
    // Get the related incident record
    var outtask = current.task_number;
    var incident = new GlideRecord('incident');
    incident.addQuery('number', outtask);
    incident.query();

    if (incident.next()) {
        // Query related outage records
        var outageGR = new GlideRecord('cmdb_ci_outage');
        outageGR.addQuery('task_number', incident.number);
        outageGR.query();

        // Initialize earliestStart and latestEnd as GlideDateTime objects
        var earliestStart = new GlideDateTime();
        earliestStart.setDisplayValue('9999-12-31 23:59:59'); // Set a future date
        var latestEnd = new GlideDateTime();
        latestEnd.setDisplayValue('1970-01-01 00:00:00'); // Set a past date

        while (outageGR.next()) {
            // Create GlideDateTime objects for the begin and end times
            var outageBegin = new GlideDateTime(outageGR.begin);
            var outageEnd = new GlideDateTime(outageGR.end);

            // Find the earliest start time
            if (outageBegin.before(earliestStart)) {
                earliestStart = outageBegin;
            }

            // Find the latest end time
            if (outageEnd.after(latestEnd)) {
                latestEnd = outageEnd;
            }
        }

        // Calculate the duration as a GlideDateTime object
        var duration = GlideDateTime.subtract(earliestStart, latestEnd);
        
        // Convert the duration to a GlideDuration object
        var durationGD = new GlideDuration(duration.getNumericValue());

        // Set the Total Outage Duration field on the incident record
        incident.u_total_outage_time = durationGD;

        incident.update();
    }
})(current, previous);

Changes made:

1. Initialized earliestStart and latestEnd as GlideDateTime objects with initial values set to a future date and a past date, respectively.
2. Created GlideDateTime objects outageBegin and outageEnd for the begin and end times of the outages.
3. Used before() and after() methods to compare the outage begin and end times with the earliestStart and latestEnd, respectively.
4. Calculated the duration as a GlideDateTime object by using the GlideDateTime.subtract() method.
5. Converted the duration GlideDateTime object to a GlideDuration object using the GlideDuration() constructor.

This should now correctly calculate the duration between the earliest start time and the latest end time of related outages and store it in the incident's u_total_outage_time field.

[Jordan_M]

Unfortunately, I am running into some issues with the suggested solution. After just updating the business rule it did not want to fire for me. I decided to test the code by stripping the business rule bit off the top and bottom and ran it with outtask set to a specific incident number. It calculated my days in the millions.