Automation for retiring services in "cmdb_ci_service"

[Mansi Motwani1]

Hi all, 

we have requirement to retire the services in "cmdb_ci_service" table on below conditions:

1. for the services which we have filtered, need to check for them in cmdb_rel_ci table in the parent field, if found any, we need to check the child record of it. 

2. We need to see if child record is operational and has class as "application", we need to skip the service related to those records. 

3. Even if one child has "application" as class and even if one is operational, we need don't have to consider it for retire. 

code tried:

// Step 1: Query cmdb_ci_service table

var service = new GlideRecord('cmdb_ci_service');

service.addEncodedQuery('service_classification=Application Service^sys_created_on<javascript&colon;gs.beginningOfLast9Months()^operational_status!=6^u_appservice_subcategory!=SAAS');

service.query();

// Step 2: Iterate through the fetched records

while (service.next()) {

    var hasApplicationChild = false; // Flag to track if any child record has class 'Application'

    // Step 3: Query cmdb_rel_ci to check if any related child record has class 'Application'

    var relCi = new GlideRecord('cmdb_rel_ci');

    relCi.addQuery('parent', service.sys_id);

    relCi.query();

    while (relCi.next()) {

        // Get the child record directly from cmdb_rel_ci table

        var childRecord = relCi.child.getRefRecord();

        if (childRecord.sys_class_name == 'cmdb_ci_appl') {

            // If any child record has class 'Application', set the flag to true and exit the loop

            hasApplicationChild = true;

            break;

        }

    }

    // Step 4: If any related child record has class 'Application', skip processing this service

    if (hasApplicationChild) {

        // Skip to the next service

        continue;

    }

    // Step 5: If none of the related child records have class 'Application', log the service name

    gs.info('Service Name: ' + service.name);

    // You can add further processing here if needed

}

It is giving few extra records for which we have class as application in child.

Please help on the same!!