Script to list all cmdb_ci tables with records

[shane_davis]

We are working on a proof of concept in our SandBox instance and deleting all records from our "cmdb_ci_" tables.   As there are well over 800 tables, I need a script to list out the tables that are not empty so I will know which ones I need to delete the records from.

I ran the script below as a scheduled job and it deleted over 200k CIs in a few hours, but I don't know if that deleted all records from all cmdb_ci_ tables or if I have more cleanup to do.  

How would I create a script to loop through all the cmdb_ci_ tables and list out the ones with records?   Thank you for any assistance!

doit("cmdb_ci"); 
function doit(cmdb_ci) { 
var gr = new GlideRecord(cmdb_ci); 
gr.deleteMultiple(); 
} 

[Chuck Tomasi · Accepted solution]

That should do it. If you don't think so, check a sub table like cmdb_ci_hardware and see if there are records there. If you still have leftover data, I can help you build a list of tables. If you don't have to, then let's not go there.

[shane_davis]

Thank you, Chuck.   I checked several of the tables including cmdb_ci_server and cmdb_ci_computer; no records from any of them.   We need a pristine clean CMDB for our proof of concept so I needed my comfort level raised.   Thank you for doing that!

[Mike Allen]

I designed this background script to hep you with peace of mind:

var table = new TableUtils("cmdb_ci");

var table_string = String(table.getAllExtensions());

var mod_table_str = table_string.slice(1, -1);

var table_array = mod_table_str.split(",");

for(i in table_array){

              var table_name = '';

              if(table_array[i].indexOf(" ") == 0)

                      table_name = table_array[i].slice(1);

              else

                      table_name = table_array[i];

              var count = 0;

              var cis = new GlideRecord(table_name);

              cis.query();

              count = cis.getRowCount();

              gs.print(table_name + " - " + count);

}

It should give you all the cmdb tables and how many records are in them.   I had to mess around with it a bit to get it to run right, but I got an output of:

[Mike Allen]

You could even go further and say:

var table = new TableUtils("cmdb_ci");

var table_string = String(table.getAllExtensions());

var mod_table_str = table_string.slice(1, -1);

var table_array = mod_table_str.split(",");

for(i in table_array){

              var table_name = '';

              if(table_array[i].indexOf(" ") == 0)

                      table_name = table_array[i].slice(1);

              else

                      table_name = table_array[i];

              var count = 0;

              var cis = new GlideRecord(table_name);

              cis.query();

              count = cis.getRowCount();

              if(count > 0)

                      gs.print(table_name + " - " + count);

}

to get: