Fix script to delete the Cloud Service Account Ci's from the DynamoDB table but it is not deleting

[MalathiP]

To reduce the ITOM License consumption, customer has asked to delete the AWS Sandbox Cloud Service Account CI's from the DynamoDB table but we are not able to delete any record.

PFB code created for this and help us if any modification is needed.

Code:

getCisFromServiceAccount();

function getCisFromServiceAccount() {
var gr = new GlideRecord('cmdb_ci_dynamodb_table');
gr.addEncodedQuery('nameLIKEsandbox');
gr.query();
while (gr.next()) {
var dbGR = new GlideRecord('sys_db_object');
dbGR.addQuery('label', gr.sys_class_name);
dbGR.query();
if (dbGR.next()) {
gs.info('Table Name from sys_db_object: ' + dbGR.name);
var ciGR = new GlideRecord(dbGR.name);
gs.info('Resource value: ' + gr.resource); // Added logging
if (ciGR.get(gr.resource)) { // Check if record is retrieved
gs.info('CI Name: ' + ciGR.name);
try {
ciGR.setWorkflow(false);
ciGR.deleteRecord();
gs.info('CI deleted: ' + ciGR.name);
} catch (e) {
gs.error('Error deleting CI: ' + e.message + ' - ' + e.stack);
}
} else {
gs.error('CI record not found with sys_id: ' + gr.resource);
}
} else {
gs.error('No sys_db_object found for class: ' + gr.sys_class_name);
}
}
gs.info('Total records in cmdb_ci_dynamodb_table: ' + gr.getRowCount());
}

[M Iftikhar · Accepted solution]

Hi @MalathiP, 

Your script fails because it unnecessarily queries the sys_db_object table and then tries to re-query the CI using ciGR.get(gr.resource). The .get() method requires a sys_id, and the resource field likely does not contain this value. The fix is to simplify the code and directly delete the record found in the initial query.

Try this script please:

 // Query for the specific DynamoDB table CIs with 'sandbox' in the name
    var gr = new GlideRecord('cmdb_ci_dynamodb_table');
    gr.addEncodedQuery('nameLIKEsandbox');
    gr.query();
    
    var count = gr.getRowCount();
    gs.info('Found ' + count + ' sandbox DynamoDB CIs to delete.');

    // Loop through the found records and delete them
    while (gr.next()) {
        try {
            var ciName = gr.getValue('name');
            gs.info('Attempting to delete CI: ' + ciName + ' (sys_id: ' + gr.getUniqueValue() + ')');
            
            // setWorkflow(false) prevents business rules or workflows from running on deletion
            gr.setWorkflow(false); 
            
            // Delete the current record from the table
            gr.deleteRecord();
            
            gs.info('Successfully deleted CI: ' + ciName);
        } catch (e) {
            gs.error('Error deleting CI ' + ciName + ': ' + e.message);
        }
    }
    gs.info('Deletion script complete.');

Hope this helps!

Thanks & Regards,
Muhammad Iftikhar
If my response helped, please mark it as the accepted solution and helpful so others can benefit as well.

[MalathiP]

Hi Iftikhar,

Thanks a lot sharing the updated script. It actually deleted he DynamoDB table CI's with the name contains sandbox.

But my actual requirement is to delete the DynamoDB table CI's which are "Hosted on::Hosts" on AWS Datacenter and with the Cloud Service Account Name contains sandbox. PFA.

[mahamst31]

Got it @MalathiP, 

Try this script on non prod env please, and check if this works for you:

deleteSandboxDynamoDBTables();

function deleteSandboxDynamoDBTables() {
    var deletedCount = 0;
    
    // 1. Find all DynamoDB table CIs linked to a sandbox Cloud Service Account
    var dynamoGR = new GlideRecord('cmdb_ci_dynamodb_table');
    dynamoGR.addQuery('service_account.name', 'CONTAINS', 'sandbox');
    dynamoGR.query();
    
    var initialCount = dynamoGR.getRowCount();
    gs.info('Found ' + initialCount + ' DynamoDB CIs linked to a sandbox service account. Now checking relationships...');

    // Loop through each of these CIs
    while (dynamoGR.next()) {
        var ciName = dynamoGR.getValue('name');
        var ciSysId = dynamooGR.getUniqueValue();

        // 2. For each CI, check if the required "Hosted on::Hosts" relationship exists
        var relGR = new GlideRecord('cmdb_rel_ci');
        relGR.addQuery('child', ciSysId); // The DynamoDB table is the child
        relGR.addQuery('parent.sys_class_name', 'cmdb_ci_aws_datacenter'); // The parent is an AWS Datacenter
        relGR.addQuery('type.name', 'Hosted on::Hosts'); // The relationship type is 'Hosted on::Hosts'
        relGR.setLimit(1); // Optimization: we only need to confirm if at least one such relationship exists
        relGR.query();

        // If a relationship is found, proceed with deletion
        if (relGR.next()) {
            try {
                gs.info('CI "' + ciName + '" (sys_id: ' + ciSysId + ') meets all conditions. Deleting...');
                
                // setWorkflow(false) prevents business rules or workflows from running
                dynamoGR.setWorkflow(false); 
                
                // Delete the record
                dynamoGR.deleteRecord();
                
                gs.info('--> Successfully deleted CI: ' + ciName);
                deletedCount++;
            } catch (e) {
                gs.error('--> Error deleting CI ' + ciName + ': ' + e.message);
            }
        } else {
             gs.info('Skipping CI "' + ciName + '" as it does not have the required AWS Datacenter relationship.');
        }
    }
    gs.info('Script complete. Total CIs deleted: ' + deletedCount + ' out of ' + initialCount + ' initially found.');
}

Hope this helps!

Thanks & Regards,
Muhammad Iftikhar
If my response helped, please mark it as the accepted solution and helpful so others can benefit as well.

[M Iftikhar]

Ok @MalathiP, 

Try this script please:

deleteSandboxDynamoDBTables();

function deleteSandboxDynamoDBTables() {
    var deletedCount = 0;
    
    // 1. Find all DynamoDB table CIs linked to a sandbox Cloud Service Account
    var dynamoGR = new GlideRecord('cmdb_ci_dynamodb_table');
    dynamoGR.addQuery('service_account.name', 'CONTAINS', 'sandbox');
    dynamoGR.query();
    
    var initialCount = dynamoGR.getRowCount();
    gs.info('Found ' + initialCount + ' DynamoDB CIs linked to a sandbox service account. Now checking relationships...');

    // Loop through each of these CIs
    while (dynamoGR.next()) {
        var ciName = dynamoGR.getValue('name');
        var ciSysId = dynamooGR.getUniqueValue();

        // 2. For each CI, check if the required "Hosted on::Hosts" relationship exists
        var relGR = new GlideRecord('cmdb_rel_ci');
        relGR.addQuery('child', ciSysId); // The DynamoDB table is the child
        relGR.addQuery('parent.sys_class_name', 'cmdb_ci_aws_datacenter'); // The parent is an AWS Datacenter
        relGR.addQuery('type.name', 'Hosted on::Hosts'); // The relationship type is 'Hosted on::Hosts'
        relGR.setLimit(1); // Optimization: we only need to confirm if at least one such relationship exists
        relGR.query();

        // If a relationship is found, proceed with deletion
        if (relGR.next()) {
            try {
                gs.info('CI "' + ciName + '" (sys_id: ' + ciSysId + ') meets all conditions. Deleting...');
                
                // setWorkflow(false) prevents business rules or workflows from running
                dynamoGR.setWorkflow(false); 
                
                // Delete the record
                dynamoGR.deleteRecord();
                
                gs.info('--> Successfully deleted CI: ' + ciName);
                deletedCount++;
            } catch (e) {
                gs.error('--> Error deleting CI ' + ciName + ': ' + e.message);
            }
        } else {
             gs.info('Skipping CI "' + ciName + '" as it does not have the required AWS Datacenter relationship.');
        }
    }
    gs.info('Script complete. Total CIs deleted: ' + deletedCount + ' out of ' + initialCount + ' initially found.');
}

Hope this helps!

Thanks & Regards,
Muhammad Iftikhar
If my response helped, please mark it as the accepted solution and helpful so others can benefit as well.