Mass deletion of Records from Computer Table(cmdb_ci_computer)

[sharada_janagar]

Hi,

Could any one please guide me on how to delete all records from cmdb_ci_computer table using Glide Record query?( I have tried using "Delete all Records" By going to Tables and Columns but it is not deleting all records at once.)

Thanks and Regards,

Sharada.

[sharada_janagar]

Hi Harikrishnan and Pradeep,

Thanks for the quick responses.

I have tried "gs.sql("truncate table <table_name>");" its really very fast. Thanks for the script :).

Regards,

Sharada

[harikrish_v]

Glad to be of help

Thanks & Regards,

Hari

[pault1]

A word of caution when using gs.sql("truncate table <table_name>"):

While this does delete the records in the table you run it against, it leaves "orphaned" records in any tables that were extended by this table you are performing the action against. This is because the gs.sql commands DO NOT cascade deletes.

For example, I ran this against VMware Virtual Machine Instance [cmdb_ci_vmware_instance] and it did delete everything in that table. However, this tables extends Virtual Machine Instance [cmdb_ci_vm_instance], which extends Virtual Machine Object [cmdb_ci_vm_object], which extends Configuration Item Base [cmdb_ci]. Every single one of those tables will have a record "orphaned" for each of the records you deleted in your extended table.

This becomes a problem if you are doing a global search for all CIs against the Configuration Item Base table, let's say for a device named "WinPC1234", you can get back multiple records for one CI, but when you click on the "orphaned" records, it tries to open the record in the VMware Virtual Machine Instance table, and you get a nice "No Record Found" error.

Use extreme caution with gs.sql!!!

[pault1]

Thought I'd follow this up with the solution I used to clean up all the data. Again, use extreme caution and run this at your own risk! These are the background scripts I ran individually to delete the orphaned records from each of the extended tables:

//gs.sql("delete cii from cmdb_ci_vm_instance as cii JOIN cmdb_ci as ci ON cii.sys_id=ci.sys_id where ci.sys_class_name='cmdb_ci_vmware_instance'");

//gs.sql("SELECT COUNT(*) from cmdb_ci_vm_instance as cii JOIN cmdb_ci as ci ON cii.sys_id=ci.sys_id where ci.sys_class_name='cmdb_ci_vmware_instance'");

//gs.sql("delete cio from cmdb_ci_vm_object as cio JOIN cmdb_ci as ci ON cio.sys_id=ci.sys_id where ci.sys_class_name='cmdb_ci_vmware_instance'");

//gs.sql("SELECT COUNT(*) from cmdb_ci_vm_object as cio JOIN cmdb_ci as ci ON cio.sys_id=ci.sys_id where ci.sys_class_name='cmdb_ci_vmware_instance'");

//gs.sql("delete ci from cmdb_ci as ci where ci.sys_class_name='cmdb_ci_vmware_instance'");

//gs.sql("SELECT COUNT(*) from cmdb_ci as ci where ci.sys_class_name='cmdb_ci_vmware_instance'");

[mitzaka]

This was extremely helpful to me, as I just faced a similar orpahed records issue with deleting CIs of VMware types using truncate sql background script. Thanks!