Core Companies table cleanup script

[PrashanthS]

Hi, We have requirement where we need to delete Core Companies record where Vendor ID(custom field) is empty.

The only relationship we need to look for is in the Business Application(cmdb_ci_business_app) table. If the core company record is referenced in any of the 3 fields: manufacturer, vendor or publisher; the record can't be deleted.

Everything else has to be deleted. Please let us know how can this be achieved.

[Jaap]

Hi @PrashanthS ,
Did you look into this specific documentations?
https://docs.servicenow.com/bundle/washingtondc-platform-administration/page/administer/managing-dat...
Regards,
Jaap

[Jaap · Accepted solution]

@PrashanthS  I would advise to do this via a normal scheduled job which you can script.

Set the values like name, check conditional, set when to run, timezone and the time. Then insert something like this condition:

// Condition: Core Companies with empty Vendor ID
var companyGR = new GlideRecord('core_company');
companyGR.addNullQuery('u_vendor_id'); // Adjust 'u_vendor_id' to your actual field name for Vendor ID
companyGR.query();

// Check if there are any records that match the condition
if (companyGR.hasNext()) {
    gs.log("Found Core Companies with empty Vendor ID, proceeding with deletion.");
    return true;
} else {
    gs.log("No Core Companies with empty Vendor ID found.");
    return false;
}

Then in 'Run this script' do the following:

var companyGR = new GlideRecord('core_company');
companyGR.addNullQuery('u_vendor_id'); // Adjust 'u_vendor_id' to your actual field name for Vendor ID
companyGR.query();

while (companyGR.next()) {
    var companySysId = companyGR.sys_id;

    // Check for references in the cmdb_ci_business_app table
    var bizAppGR = new GlideRecord('cmdb_ci_business_app');
    bizAppGR.addQuery('manufacturer', companySysId);
    bizAppGR.addOrCondition('vendor', companySysId);
    bizAppGR.addOrCondition('publisher', companySysId);
    bizAppGR.query();

    if (bizAppGR.hasNext()) {
        // If any references are found, skip the deletion
        gs.log('Skipping deletion for company: ' + companyGR.name + ' as it is referenced in Business Applications.');
        continue;
    }

    // No references found, delete the company record
    gs.log('Deleting company: ' + companyGR.name);
    companyGR.deleteRecord();
}