Script for Companies table cleanup

[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.

[Mark Manders · Accepted solution]

(function() {
    var grCompany = new GlideRecord('core_company');
    grCompany.addNullQuery('u_vendor_id'); // Assuming 'u_vendor_id' is the custom field for Vendor ID
    grCompany.query();
    while (grCompany.next()) {
        var companySysId = grCompany.sys_id;
        var grBusinessApp = new GlideRecord('cmdb_ci_business_app');
        grBusinessApp.addQuery('manufacturer', companySysId)
            .addOrCondition('vendor', companySysId)
            .addOrCondition('publisher', companySysId);
        grBusinessApp.query();
        if (!grBusinessApp.hasNext()) {
            gs.info('Deleting Core Company record: ' + grCompany.name);
            grCompany.deleteRecord();
        } else {
            gs.info('Core Company record is referenced in cmdb_ci_business_app and cannot be deleted: ' + grCompany.name);
        }
    }
})();

[Mark Manders · Accepted solution]

(function() {
    var grCompany = new GlideRecord('core_company');
    grCompany.addNullQuery('u_vendor_id'); // Assuming 'u_vendor_id' is the custom field for Vendor ID
    grCompany.query();
    while (grCompany.next()) {
        var companySysId = grCompany.sys_id;
        var grBusinessApp = new GlideRecord('cmdb_ci_business_app');
        grBusinessApp.addQuery('manufacturer', companySysId)
            .addOrCondition('vendor', companySysId)
            .addOrCondition('publisher', companySysId);
        grBusinessApp.query();
        if (!grBusinessApp.hasNext()) {
            gs.info('Deleting Core Company record: ' + grCompany.name);
            grCompany.deleteRecord();
        } else {
            gs.info('Core Company record is referenced in cmdb_ci_business_app and cannot be deleted: ' + grCompany.name);
        }
    }
})();

[palanikumar · Accepted solution]

Hi,

You can try the below code

var grCompany = new GlideRecord("core_company");
grCompany.addNullQuery("u_vendor_id");
grCompany.query();
while(grCompany.next()){
  var company = grCompany.sys_id
  var grBussApp = new GlideRecord("cmdb_ci_business_app");
 
 grBussApp.addCondition("manufacturer",company).addOrCondition("vendor",company).addOrCondition("publisher",company);
  grBussApp.query();
  if(grBussApp.hasNext()){
    grCompany.deleteRecord();
  }
}

[PrashanthS]

Hi @Mark Manders and @palanikumar 

Thanks for your replies. Since both had similar scripts, I used one of them here to get row count. However, the rowcount output from this query is equal to the number of records from the first Nullquery. In such case I'm not able to figure out the exact number of records in question:

var grCompany = new GlideRecord("core_company");
grCompany.addNullQuery('u_vendor_id');
grCompany.query();
while(grCompany.next()){
  var comp = grCompany.sys_id;
  var grBussApp = new GlideRecord("cmdb_ci_business_app");
 
 grBussApp.addCondition("manufacturer",comp).addOrCondition("vendor",comp).addOrCondition("company",comp).addOrCondition("u_hosting_provider",comp).addOrCondition("support_vendor",comp);
  grBussApp.query();
  if(grBussApp.hasNext()){
  gs.print(grCompany.getRowCount());
  //  grCompany.deleteRecord();
  }
}

Regards,

Prashant Singh

[palanikumar]

Hi Prashant,

Realized an issue with if condition. Replace last three lines with the below code.

Note: If you want the count of Business applications where the company is referred then use grBussApp instead of grCompany. grCompany will always return same count as it holds the company record which is not changed inside the while loop

if(grBussApp.hasNext()){
  gs.print(grBussApp.getRowCount());
  //  grCompany.deleteRecord();
}