Help with Script for Automatically Updating CI Install Status Based on Discovery Date

[Abdul333]

Hello Everyone,

I’m working on a story to automatically update the install_status of Configuration Items (CIs) based on the number of days since their last discovery. Specifically, I need to update CIs with the following criteria:

• If a CI hasn’t been discovered in the last 45–90 days, its install_status should be changed to Absent (100).
• If a CI hasn’t been discovered in the last 90+ days, its install_status should be changed to Retired (7 ), and the operational_status should be set to Non-Operational (2).

Class names: cmdb_ci_db_mssql_database,cmdb_ci_vm_instance,cmdb_ci_db_ora_instance,cmdb_ci_linux_server,cmdb_ci_vmware_instance

I created a system property with all the classes mentioned above. Property name(ci.auto.manage.classes)

Script:

(function () {
gs.info('=== CI Auto Absent / Retire Job STARTED ===');

// System Properties
var classList = gs.getProperty('ci.auto.manage.classes', '').split(',');
var absentDays = parseInt(gs.getProperty('ci.absent.days', '45'), 10);
var retireDays = parseInt(gs.getProperty('ci.retire.days', '90'), 10);

var absentInstallStatus = gs.getProperty('ci.absent.install_status', '100');
var retireInstallStatus = gs.getProperty('ci.retire.install_status', '7');
var retireOperStatus = gs.getProperty('ci.retire.operational_status', '2');

// Date calculations
var now = new GlideDateTime();

var absentCutoff = new GlideDateTime(now);
absentCutoff.addDaysUTC(-absentDays);

var retireCutoff = new GlideDateTime(now);
retireCutoff.addDaysUTC(-retireDays);

gs.info('Absent Cutoff : ' + absentCutoff.getDisplayValue());
gs.info('Retire Cutoff : ' + retireCutoff.getDisplayValue());

for (var i = 0; i < classList.length; i++) {
var table = classList[i].trim();
if (!table) continue;

gs.info('--- Processing Table: ' + table + ' ---');

var gr = new GlideRecord(table);
gr.addNotNullQuery('last_discovered');
gr.query();

while (gr.next()) {
var lastDiscovered = gr.last_discovered;

/* ===============================
RETIRE (>= 90 days)
================================ */
if (lastDiscovered < retireCutoff) {
if (gr.install_status != retireInstallStatus) {
gr.setWorkflow(false);
gr.autoSysFields(false);

gr.install_status = retireInstallStatus;
gr.operational_status = retireOperStatus;
gr.update();

gs.info('✅ RETIRED CI: ' + gr.name + ' | ' + gr.sys_id);
}
}

/* ===============================
ABSENT (45–89 days)
install_status ONLY
================================ */
else if (lastDiscovered < absentCutoff) {
if (gr.install_status != absentInstallStatus) {
gr.setWorkflow(false);
gr.autoSysFields(false);

gr.install_status = absentInstallStatus;
// ✅ operational_status untouched
gr.update();

gs.info('MARKED ABSENT (install_status only): ' + gr.name + ' | ' + gr.sys_id);
}
}
}
}

gs.info('=== CI Auto Absent / Retire Job COMPLETED ===');
})();

I created the system properties mentioned in the script.

Could you please help me review this script, and let me know if there are any improvements or changes I should make to ensure it works as expected? Specifically, I want to confirm if the logic for updating the install_status and operational_status is correct, and if any optimizations can be made to improve performance.

@Ankur Bawiskar @Amit Gujarathi @Ravi Gaurav 

Thanks,
Abdul

[Ankur Bawiskar]

@Abdul333 

so is the above script not working fine?

[Ankur Bawiskar]

@Abdul333 

try this optimized script

(function () {
    gs.info('=== CI Auto Absent / Retire Job STARTED ===');

    var classProp = gs.getProperty('ci.auto.manage.classes', '');
    var classList = classProp ? classProp.split(',') : [];

    var absentDays = parseInt(gs.getProperty('ci.absent.days', '45'), 10);
    var retireDays = parseInt(gs.getProperty('ci.retire.days', '90'), 10);

    var absentInstallStatus = gs.getProperty('ci.absent.install_status', '100');
    var retireInstallStatus = gs.getProperty('ci.retire.install_status', '7');
    var retireOperStatus = gs.getProperty('ci.retire.operational_status', '2');

    if (!classList.length) {
        gs.warn('No classes configured in ci.auto.manage.classes');
        return;
    }

    if (isNaN(absentDays) || isNaN(retireDays) || absentDays >= retireDays) {
        gs.error('Invalid property configuration. Ensure absentDays < retireDays.');
        return;
    }

    for (var i = 0; i < classList.length; i++) {
        var table = (classList[i] || '').trim();
        if (!table) {
            continue;
        }

        gs.info('--- Processing Table: ' + table + ' ---');

        // 1) Retire: last_discovered older than retireDays
        var retireGR = new GlideRecord(table);
        retireGR.addEncodedQuery(
            'last_discoveredRELATIVELT@dayofweek@ago@' + retireDays +
            '^NQlast_discoveredISEMPTY'
        );
        retireGR.addQuery('install_status', '!=', retireInstallStatus);
        retireGR.setValue('install_status', retireInstallStatus);
        retireGR.setValue('operational_status', retireOperStatus);
        retireGR.setWorkflow(false);
        retireGR.autoSysFields(false);
        var retiredCount = retireGR.updateMultiple();

        gs.info('Retire update issued for table ' + table + ', result: ' + retiredCount);

        // 2) Absent: between absentDays and retireDays
        var absentGR = new GlideRecord(table);
        absentGR.addEncodedQuery(
            'last_discoveredRELATIVELT@dayofweek@ago@' + absentDays +
            '^last_discoveredRELATIVEGE@dayofweek@ago@' + retireDays
        );
        absentGR.addQuery('install_status', '!=', absentInstallStatus);
        absentGR.addQuery('install_status', '!=', retireInstallStatus);
        absentGR.setValue('install_status', absentInstallStatus);
        absentGR.setWorkflow(false);
        absentGR.autoSysFields(false);
        var absentCount = absentGR.updateMultiple();

        gs.info('Absent update issued for table ' + table + ', result: ' + absentCount);
    }

    gs.info('=== CI Auto Absent / Retire Job COMPLETED ===');
})();

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Abdul333]

Hi @Ankur Bawiskar , @Kamva , @AhsanMThe provided scripts are functioning correctly. However, the CMDB team has introduced an additional condition: CIs with existing relationships should not be set to Absent or Retired.

In our system we are having one existing Schedule Job, which is having large query for reference we can check that one as well.

Existing Schedule Job:

var ng = new GlideRecord('cmdb_rel_ci');

ng.addEncodedQuery('parent.u_is_retired=false^parent.sys_class_nameINSTANCEOFcmdb_ci_db_instance^child.u_is_retired=true^parent.sys_created_by=MIDSERVER_DISCOVERY^parent.sys_class_name!=cmdb_ci_db_mssql_analysis^parent.sys_class_name!=cmdb_ci_db_mssql_integration^parent.sys_class_name!=cmdb_ci_db_mssql_reporting^parent.sys_updated_on<javascript&colon;gs.beginningOfLast3Months()^parent.sys_updated_by=system^ORparent.sys_updated_by=MIDSERVER_DISCOVERY^parent.discovery_source=ServiceWatch^ORparent.discovery_source=ServiceNow^child.sys_class_name=cmdb_ci_linux_server^ORchild.sys_class_name=cmdb_ci_server^ORchild.sys_class_name=cmdb_ci_win_server^type=468913da2ccc4100ccf94a530ce8bbf2^ORtype=60bc4e22c0a8010e01f074cbe6bd73c3^child.u_retirement_date<javascript&colon;gs.beginningOfLast3Months()^NQparent.u_is_retired=false^parent.sys_class_nameINSTANCEOFcmdb_ci_db_instance^child.u_is_retired=true^parent.sys_created_by=MIDSERVER_DISCOVERY^parent.sys_class_name!=cmdb_ci_db_mssql_analysis^parent.sys_class_name!=cmdb_ci_db_mssql_integration^parent.sys_class_name!=cmdb_ci_db_mssql_reporting^parent.sys_updated_on<javascript&colon;gs.beginningOfLast3Months()^parent.sys_updated_by=system^ORparent.sys_updated_by=MIDSERVER_DISCOVERY^parent.discovery_source=ServiceWatch^ORparent.discovery_source=ServiceNow^child.sys_class_name=cmdb_ci_linux_server^ORchild.sys_class_name=cmdb_ci_server^ORchild.sys_class_name=cmdb_ci_win_server^type=468913da2ccc4100ccf94a530ce8bbf2^ORtype=60bc4e22c0a8010e01f074cbe6bd73c3^child.u_retirement_dateISEMPTY^child.sys_updated_on<javascript&colon;gs.beginningOfLast3Months()');

ng.query();

var arr = [];

var ArrayUtils = new ArrayUtil();

var c = 0;

while (ng.next()) {

    arr.push(ng.parent.toString());

    c = c + 1;

}

//var arr2 = [];

var h = 0;

var a1 = ArrayUtils.unique(arr);

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

    var s = a1[i];

    var gr = new GlideRecord('cmdb_rel_ci');

    var a = 'parent.sys_idIN' + s;

    var b = 'child.u_is_retired=false^child.sys_class_name=cmdb_ci_linux_server^ORchild.sys_class_name=cmdb_ci_server^ORchild.sys_class_name=cmdb_ci_win_server';

    var v = a + '^' + b;

    //gs.info(v);

    gr.addEncodedQuery(v);

    gr.query();

    if (!gr.next()) {

        h = h + 1;

        var cig = new GlideRecord('cmdb_ci');

        cig.addEncodedQuery('sys_idIN' + s);

        cig.query();

        if (cig.next()) {

            //gs.info(cig.u_id);

            cig.install_status = 7;

            cig.update();

        }

    }

    else {

        gs.info('Skipping this record as it has multiple parents'+ s);

        continue;

    }

}

gs.info(c);

gs.info("This is h: " + h);

//gs.info(arr);

Currently, the scheduled job retires CIs only for specific classes. We now need to either enhance the existing scheduled job or create a new one to support additional logic.

For the following five CI classes, the job should evaluate relationship presence and staleness before updating the CI status to Absent or Retired:

• cmdb_ci_db_mssql_database – Mark as Absent after 60 days
• cmdb_ci_vm_instance – Mark as Absent after 45 days
• cmdb_ci_db_ora_instance – Mark as Absent after 60 days
• cmdb_ci_linux_server – Mark as Absent after 45 days
• cmdb_ci_vmware_instance – Mark as Absent after 60 days

Retirement should occur after 90 days for all classes.

Expected Logic

• The job should run class-wise and apply the corresponding day thresholds.
• A CI should be updated to Absent or Retired only if it has no existing relationships.
• If any relationship exists, the CI must be excluded from Absent and Retirement updates, regardless of discovery age.
• Status transitions should follow:
  • Active → Absent (based on class-specific threshold)
  • Absent → Retired (after 90 days)

Please help confirm and implement this logic so that CIs are updated correctly based on their class, discovery timelines, and relationship status.

Thanks,

Abdul

[AhsanM]

Hi Abdul,

Thanks for sharing! Based on the new requirements here is an enhanced script that handles all five CI classes with their individual thresholds, relationship checks, and correct status transitions.

(function () {
    gs.info('=== CI Auto Absent / Retire Job STARTED ===');

    // Class configuration with individual thresholds
    var classConfig = {
        'cmdb_ci_db_mssql_database': { absentDays: 60, retireDays: 90 },
        'cmdb_ci_vm_instance':        { absentDays: 45, retireDays: 90 },
        'cmdb_ci_db_ora_instance':    { absentDays: 60, retireDays: 90 },
        'cmdb_ci_linux_server':       { absentDays: 45, retireDays: 90 },
        'cmdb_ci_vmware_instance':    { absentDays: 60, retireDays: 90 }
    };

    var ABSENT_STATUS  = '100';
    var RETIRE_STATUS  = '7';
    var RETIRE_OPER    = '2';
    var ACTIVE_STATUS  = '1';

    // Check if a CI has any existing relationships
    function hasRelationships(ciSysId) {
        var relGr = new GlideRecord('cmdb_rel_ci');
        relGr.addQuery('parent', ciSysId);
        relGr.setLimit(1);
        relGr.query();
        if (relGr.next()) return true;

        var relGr2 = new GlideRecord('cmdb_rel_ci');
        relGr2.addQuery('child', ciSysId);
        relGr2.setLimit(1);
        relGr2.query();
        return relGr2.next();
    }

    var now = new GlideDateTime();

    for (var table in classConfig) {
        var config = classConfig[table];
        gs.info('--- Processing: ' + table + ' | Absent after ' + config.absentDays + ' days | Retire after ' + config.retireDays + ' days ---');

        var absentCutoff = new GlideDateTime(now);
        absentCutoff.addDaysUTC(-config.absentDays);

        var retireCutoff = new GlideDateTime(now);
        retireCutoff.addDaysUTC(-config.retireDays);

        var gr = new GlideRecord(table);
        gr.addNotNullQuery('last_discovered');
        gr.addQuery('last_discovered', '<', absentCutoff.getValue());
        gr.addQuery('install_status', 'NOT IN', [RETIRE_STATUS]); // skip already retired
        gr.query();

        var absentCount = 0;
        var retireCount = 0;
        var skippedCount = 0;

        while (gr.next()) {
            var lastDiscoveredGDT = new GlideDateTime(gr.getValue('last_discovered'));

            // Skip CIs with existing relationships
            if (hasRelationships(gr.sys_id.toString())) {
                gs.info('Skipping CI with relationships: ' + gr.name + ' | ' + gr.sys_id);
                skippedCount++;
                continue;
            }

            // Retire: last discovered more than retireDays ago
            if (lastDiscoveredGDT.before(retireCutoff)) {
                if (gr.getValue('install_status') != RETIRE_STATUS) {
                    gr.setValue('install_status', RETIRE_STATUS);
                    gr.setValue('operational_status', RETIRE_OPER);
                    gr.setWorkflow(false);
                    gr.autoSysFields(false);
                    gr.update();
                    gs.info('RETIRED: ' + gr.name + ' | ' + gr.sys_id);
                    retireCount++;
                }
            }
            // Absent: last discovered more than absentDays ago but less than retireDays
            else if (lastDiscoveredGDT.before(absentCutoff)) {
                if (gr.getValue('install_status') != ABSENT_STATUS) {
                    gr.setValue('install_status', ABSENT_STATUS);
                    gr.setWorkflow(false);
                    gr.autoSysFields(false);
                    gr.update();
                    gs.info('MARKED ABSENT: ' + gr.name + ' | ' + gr.sys_id);
                    absentCount++;
                }
            }
        }

        gs.info('Table: ' + table + ' | Absent: ' + absentCount + ' | Retired: ' + retireCount + ' | Skipped (has relationships): ' + skippedCount);
    }

    gs.info('=== CI Auto Absent / Retire Job COMPLETED ===');

})();

Key changes from the original script:

The class configuration object at the top makes it easy to adjust thresholds per class without touching the core logic. Each class now has its own absentDays value as requested.

The hasRelationships() function checks both parent and child sides of cmdb_rel_ci for each CI. If any relationship exists on either side the CI is skipped entirely regardless of discovery age, which matches the new CMDB team requirement.

The date comparison now uses GlideDateTime.before() instead of direct comparison operators, which is the reliable way to compare dates in ServiceNow server side scripts.

setWorkflow(false) and autoSysFields(false) are applied before each update to prevent triggering unnecessary Business Rules and audit entries on bulk updates.

One thing to confirm before running in production:

The hasRelationships() function currently checks for any relationship. If the CMDB team has a more specific definition of "existing relationship" such as only active relationships or only specific relationship types, the query in that function can be refined accordingly. Let me know if that is the case.