Change Request total Onhold duration

[Yashwanth_reddy]

Hi There,

There is requirement, to calculate total On Hold duration of Change Request.

If Change is On hold for X days in Assess state and moved to Authorize state.

In Authorize state change is On hold for Y days and moved to Scheduled State.

In Scheduled state change is On Hold for Z days and moved to Implement state.

Now need to calculate total On Hold duration in one field.

Total Duration = X + Y + Z

Can any one suggest me..

Below is the script I tried using before BR

var changeRequestGR = new GlideRecord('change_request');

var aggregate = new GlideAggregate('change_request');

    changeRequestGR.addQuery('On hold', 'true'); // Change request is on hold

    changeRequestGR.query();

    while (changeRequestGR.next()) {

        var duration = current.u_duration; //new GlideDuration();

        var states = ['assess', 'authorize', 'scheduled', 'implement', 'review']; // Add other states as needed

        for (var i = 0; i < states.length; i++) {

            var state = states[i];

            if (changeRequestGR[state + '_u_duration']) {

                duration.addAggregate(changeRequestGR['SUM', state + '_u_duration']);

            }

        }

        changeRequestGR.setValue('u_duration', duration);

        gs.info('Change request ' + changeRequestGR.number + ' total on-hold duration: ' + duration);

        changeRequestGR.update();

    }

[Yashwanth_reddy]

Hi @GlideScript LLC 

Thanks for script.

When I run the script it doesn't work and it throws below error:

Syntax Error or Access Rule Violation detected by database ((conn=312369) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM task task0 WHERE task0.`sys_class_name` IN ('change_request','change_re...' at line 1)

[GlideScript LLC]

Hey Yashwanth,

The error message you received indicates a syntax error in the SQL query. Here's how you may troubleshoot and potentially resolve the issue:

1. Check Field Names: Make sure that the field names you are using in your script, like 'assess_u_duration', 'authorize_u_duration', etc., are correct and exist in the 'change_request' table. Any discrepancy in field names can lead to syntax errors.

2. Correct Use of GlideAggregate: GlideAggregate is used for calculating aggregates like SUM, AVG, etc., across multiple records. Your script seems to be correctly using addAggregate to sum up durations. However, ensure that the fields you are aggregating ('assess_u_duration', etc.) are of a type that can be aggregated (like integer or duration).

3. Aggregate Query Logic: Your script adds aggregates and then immediately tries to retrieve them in the same while loop. The aggregate query should be executed separately from the record update logic. First, run the aggregate query, collect the results, and then in a separate loop, update the relevant change requests.

4. Check Permissions: The error message mentioning an "Access Rule Violation" suggests there might be a permissions issue. Ensure that the account running this script has the necessary permissions to execute these queries on the 'change_request' table.