Most efficient glide query to get change requests affecting a specific ci

[Tim D Scott]

Hi All,

I need to create a glidequery that takes a CI as a parameter and returns an array of change requests where the CI is specified in the impacted and/or affected tabs (so ci_item & task_cmdb_ci_service tables).

I haven't done much in the change request area, has anyone got any advice on the best (and most performant) method to do this?

Thanks,

Tim.

[Sandeep Rajput · Accepted solution]

@Tim D Scott Please see if the following script help.

function getChangeRequestsForCI(ciSysId) {
    var changeRequests = [];

    // Query change_request table for direct CI impact (ci_item)
    var grChangeRequest = new GlideRecord('change_request');
    grChangeRequest.addQuery('ci_item', ciSysId);
    grChangeRequest.query();
    while (grChangeRequest.next()) {
        changeRequests.push(grChangeRequest.sys_id.toString());
    }

    // Query task_cmdb_ci_service table for affected CIs linked to tasks
    var grTaskCmdbCiService = new GlideRecord('task_cmdb_ci_service');
    grTaskCmdbCiService.addQuery('ci_item', ciSysId);
    grTaskCmdbCiService.query();
    while (grTaskCmdbCiService.next()) {
        var taskSysId = grTaskCmdbCiService.task.sys_id.toString();

        // Check if the task is a change request
        var grTask = new GlideRecord('task');
        if (grTask.get(taskSysId) && grTask.sys_class_name == 'change_request') {
            if (!changeRequests.includes(taskSysId)) {
                changeRequests.push(taskSysId);
            }
        }
    }

    return changeRequests;
}

// Example usage
var ciSysId = 'your_ci_sys_id_here';
var changeRequests = getChangeRequestsForCI(ciSysId);
gs.info('Change Requests: ' + changeRequests);

[Tim D Scott]

Hi Sandeep,

Thanks for this, I got it working, but ci_item seems to be the wrong field for the queries. I changed the first reference to cmdb_ci and the second to cmdb_ci_service. I also added some queries on the state fields, which was an additional requirement.

Thanks for getting me started!

function getChangeRequestsForCI(ciSysId) {
    var changeRequests = [];

    // Query change_request table for direct CI impact (ci_item)
    var grChangeRequest = new GlideRecord('change_request');
    grChangeRequest.addQuery('cmdb_ci', ciSysId);
	grChangeRequest.addQuery('state',-1).addOrCondition('state',0);
    grChangeRequest.query();
    while (grChangeRequest.next()) {
        changeRequests.push(grChangeRequest.sys_id.toString());
    }

    // Query task_cmdb_ci_service table for affected CIs linked to tasks
    var grTaskCmdbCiService = new GlideRecord('task_cmdb_ci_service');
    grTaskCmdbCiService.addQuery('cmdb_ci_service', ciSysId);
    grTaskCmdbCiService.query();
    while (grTaskCmdbCiService.next()) {
        var taskSysId = grTaskCmdbCiService.task.sys_id.toString();
        // Check if the task is a change request
        var grTask = new GlideRecord('task');
        if (grTask.get(taskSysId) && grTask.sys_class_name == 'change_request' && (grTask.state == -1 || grTask.state == 0)) {
            if (!changeRequests.includes(taskSysId)) {
                changeRequests.push(taskSysId);
            }
        }
    }

    return changeRequests;
}