GlideRecord Query Processing Limitation?

[kchorny]

I'm trying to create records in a custom table for 347063 CI records that meet certain conditions. My script is below. It processes about 147000 records (1000 at a time) and then stops. What do I need to do to get it to keep going until it's created all of the necessary records?

var qs = 'sys_class_name=cmdb_ci_vm^ORsys_class_name=cmdb_ci_comm^ORsys_class_name=cmdb_ci_peripheral^ORsys_class_name=cmdb_ci_facility_hardware^ORsys_class_name=cmdb_ci_hardware^ORsys_class_name=cmdb_ci_chassis^ORsys_class_name=cmdb_ci_ucs_chassis^ORsys_class_name=cmdb_ci_ucs_equipment^ORsys_class_name=cmdb_ci_comm_hardware^ORsys_class_name=cmdb_ci_computer^ORsys_class_name=cmdb_ci_handheld_computing^ORsys_class_name=cmdb_ci_pc_hardware^ORsys_class_name=cmdb_ci_server^ORsys_class_name=cmdb_ci_net_app_server^ORsys_class_name=cmdb_ci_converged_infra^ORsys_class_name=cmdb_ci_diskshelf_chassis^ORsys_class_name=cmdb_ci_enclosure^ORsys_class_name=cmdb_ci_equipment_holder^ORsys_class_name=u_faceplate^ORsys_class_name=cmdb_ci_iot^ORsys_class_name=cmdb_ci_imaging^ORsys_class_name=cmdb_ci_ip_camera^ORsys_class_name=cmdb_ci_netgear^ORsys_class_name=cmdb_ci_wap_network^ORsys_class_name=cmdb_ci_ot^ORsys_class_name=cmdb_ci_ot_industrial_3d_printer^ORsys_class_name=cmdb_ci_printing_hardware^ORsys_class_name=cmdb_ci_disk^ORsys_class_name=cmdb_ci_printer^ORsys_class_name=cmdb_ci_outofband_device^ORsys_class_name=cmdb_ci_storage_device^ORsys_class_name=cmdb_ci_storage_volume^ORsys_class_name=cmdb_ci_service^ORsys_class_name=u_cmdb_ci_quarantined^ORsys_class_name=cmdb_ci_imaging_hardware^ORsys_class_name=cmdb_ci_vm_object^ORsys_class_name=cmdb_ci_unclassed_hardware^ORsys_class_name=cmdb_ci_scanner^ORsys_class_name=cmdb_ci_multimedia^ORsys_class_name=u_cmdb_ci_ot_research_equipment';
var start = 0;
var end = 999;
//query the cmdb_ci table just to get record count
var ciList = new GlideRecord('cmdb_ci');
ciList.addEncodedQuery(qs);
ciList.query();
var count = ciList.getRowCount();
gs.log('Total CI Count: ' + count, "KC");
//since there are thousands of CIs, we want to process records in chunks of 1000
while (start < count) {
    var ci = new GlideRecord('cmdb_ci');
    ci.addEncodedQuery(qs);
    ci.orderBy('sys_created_on');
    ci.chooseWindow(start, end);
    ci.query();
    gs.log('Next chunk of records count: ' + ci.getRowCount() + ' From record number ' + start + ' to record number ' + end, "KC");
    while (ci.next()) {
		//first check to see if CI already has a CMMC profile. If not, create one.
        var cmmcProfile = new GlideRecord('u_mitll_cmmc');
        cmmcProfile.addQuery('u_configuration_item', ci.sys_id);
        cmmcProfile.query();
        if (!cmmcProfile.next()) {
            var ciClass = ci.sys_class_name.getDisplayValue();
            var decTbl = '0d84e7921b8c42d03a5585d1604bcb4d'; //CMMC Class Rules
            var dt = new sn_dt.DecisionTableAPI();
            var inputs = new Object();
            inputs['u_class'] = ciClass;
            var response = dt.getDecision(decTbl, inputs);
            if (response.result_elements.u_category != 'Not Found') {
                var category = response.result_elements.u_category;
                var si = response.result_elements.u_system_inventory;
                var subcategory = response.result_elements.u_subcategory;
                var cmmc = new GlideRecord('u_mitll_cmmc');
                cmmc.initialize();
                cmmc.u_configuration_item = ci.sys_id;
                cmmc.u_category = category;
                cmmc.u_subcategory = subcategory;
                cmmc.u_system_inventory = si;
                cmmc.insert();
            } else {
                gs.log('No rule found for CI class ' + ciClass + ' - CI: ' + ci.sys_id,"KC");
            }
        }
    }
    //update start and end to get the next set of 1000 records
    start += 1000;
    end += 1000;
}

[ArsalanChaudhry]

Hi @kchorny,

It looks like the issue might be related to the use of ci.chooseWindow(start, end) within the loop. The chooseWindow method is used to limit the result set, but it's not advancing the iterator position of the GlideRecord itself.

To resolve this issue, you should update the iterator position explicitly using ci.next() within the outer while loop. 

Try this:

while (start < count) {
    var ci = new GlideRecord('cmdb_ci');
    ci.addEncodedQuery(qs);
    ci.orderBy('sys_created_on');
    ci.chooseWindow(start, end);
    ci.query();
    gs.log('Next chunk of records count: ' + ci.getRowCount() + ' From record number ' + start + ' to record number ' + end, "KC");
    
    // Iterate over the result set using ci.next()
    while (ci.next()) {
        // ... your existing code ...

        // Move to the next record explicitly
        ci.next();
    }

    // Update start and end to get the next set of 1000 records
    start += 1000;
    end += 1000;
}

By adding ci.next() at the end of the inner loop, you ensure that the iterator position is advanced, allowing you to continue processing the next set of records in the outer loop.

Please mark this as correct answer and helpful if it is resolved, or mark this helpful if this helps you to reach towards solution.

Thanks,

Arsalan

[kchorny]

Thank you for your response, but the script is working and is creating 1000 records at a time (it is incrementing start and end appropriately), but after doing so 147 times, it just stopped. So my question was more about if I'm running into some kind of system limitation.

I did run the script again, and it completed as expected this time, so I'm stumped as to what happened the first time.

I am interested in what you said, though. I've never seen a case where an explicit next() would be required within a while next() block. Can you elaborate on the purpose of doing this?-

[ArsalanChaudhry]

I'm glad to hear that the script is now working as expected. Regarding the use of ci.next() within the inner loop, it appears there was a misunderstanding in my previous response, and I appreciate your attention to detail.

In GlideRecord, the while (ci.next()) loop is typically used to iterate through the result set automatically. The next() method moves the GlideRecord cursor to the next record in the result set, and the loop continues until there are no more records.

In your original script, you were using ci.next() correctly within the inner loop, and it was not necessary to add an extra ci.next() at the end of the inner loop. I apologize for any confusion caused by that statement.

As for the intermittent issue you encountered, it could be influenced by various factors such as system load, resource constraints, or other environmental conditions. If the issue persists or if you encounter similar problems in the future, you may want to consider logging additional information (e.g., error messages) to help diagnose the cause.

Thank you.

[Amit Gujarathi]

HI @kchorny ,
I trust you are doing great.
Please find the updated code for the same.

var qs = 'sys_class_name=cmdb_ci_vm^ORsys_class_name=cmdb_ci_comm^ORsys_class_name=cmdb_ci_peripheral^ORsys_class_name=cmdb_ci_facility_hardware^ORsys_class_name=cmdb_ci_hardware^ORsys_class_name=cmdb_ci_chassis^ORsys_class_name=cmdb_ci_ucs_chassis^ORsys_class_name=cmdb_ci_ucs_equipment^ORsys_class_name=cmdb_ci_comm_hardware^ORsys_class_name=cmdb_ci_computer^ORsys_class_name=cmdb_ci_handheld_computing^ORsys_class_name=cmdb_ci_pc_hardware^ORsys_class_name=cmdb_ci_server^ORsys_class_name=cmdb_ci_net_app_server^ORsys_class_name=cmdb_ci_converged_infra^ORsys_class_name=cmdb_ci_diskshelf_chassis^ORsys_class_name=cmdb_ci_enclosure^ORsys_class_name=cmdb_ci_equipment_holder^ORsys_class_name=u_faceplate^ORsys_class_name=cmdb_ci_iot^ORsys_class_name=cmdb_ci_imaging^ORsys_class_name=cmdb_ci_ip_camera^ORsys_class_name=cmdb_ci_netgear^ORsys_class_name=cmdb_ci_wap_network^ORsys_class_name=cmdb_ci_ot^ORsys_class_name=cmdb_ci_ot_industrial_3d_printer^ORsys_class_name=cmdb_ci_printing_hardware^ORsys_class_name=cmdb_ci_disk^ORsys_class_name=cmdb_ci_printer^ORsys_class_name=cmdb_ci_outofband_device^ORsys_class_name=cmdb_ci_storage_device^ORsys_class_name=cmdb_ci_storage_volume^ORsys_class_name=cmdb_ci_service^ORsys_class_name=u_cmdb_ci_quarantined^ORsys_class_name=cmdb_ci_imaging_hardware^ORsys_class_name=cmdb_ci_vm_object^ORsys_class_name=cmdb_ci_unclassed_hardware^ORsys_class_name=cmdb_ci_scanner^ORsys_class_name=cmdb_ci_multimedia^ORsys_class_name=u_cmdb_ci_ot_research_equipment';
var start = 0;
var batchSize = 1000;
var end = start + batchSize;
var totalProcessed = 0;

// Query the cmdb_ci table just to get record count
var ciList = new GlideRecord('cmdb_ci');
ciList.addEncodedQuery(qs);
ciList.query();
var totalCount = ciList.getRowCount();
gs.log('Total CI Count: ' + totalCount, "KC");

while (start < totalCount) {
    var ci = new GlideRecord('cmdb_ci');
    ci.addEncodedQuery(qs);
    ci.orderBy('sys_created_on');
    ci.chooseWindow(start, end);
    ci.query();
    gs.log('Processing records ' + start + ' to ' + end, "KC");
    while (ci.next()) {
        // Handle record processing here
        totalProcessed++;
        // Your record processing logic goes here
        
        if (totalProcessed >= totalCount) {
            gs.log('All records processed successfully', 'KC');
            return; // Exit loop if all records have been processed
        }
    }
    start += batchSize;
    end += batchSize;
}