Cleaning up the CMDB

[Stijn Verhulst3]

Hi,

what would be the best practice to cleanup an enitre CMDB? I've tried to do it via a script but unfortunately it didn't work out as the platform couldn't seem to process the deletion/removal for too much records at once.

Thank you in advance,

Stijn

[Antony_Alldis]

gs.sql has been deprecated in Geneva.

Too many customers have corrupted their CMDB beyond all repair using "truncate".

DON'T DO IT!

[aaron_damyen · Accepted solution]

I would recommend utilizing the TableCleaner.   It is used by the Import Sets to clean out old data periodically and with minimal impact to the system.   You can use it clean any table in your instance based on a condition.   There are some configuration options you can put on each table to control some behaviours of the cleaner (Dictionary Attributes - ServiceNow Wiki).

The cleaner will perform a 'less than' on the value specified with the field you specify, as in

GlideTableCleaner(tableName, value, field)

This makes it very easy to delete old data.   Deleting data where an equality is needed is not possible.   I run this from the Scripts - Background and then leave for the weekend or setup a scheduled job.   It's not fast, but it is also not intrusive.   The first run of this took 2 1/2 weeks to clear out 4 million records with a 25,000 records per hour inflow.   If speed is more important than performance, this may not be your answer.

Here's my example use of it as a scheduled job script:

var ageDays = 0.25; // (6 hours)

var tableName = "employee_hr_db";

gs.log("Beginning clean-up of table: " + tableName);

var cleaner = new GlideTableCleaner(tableName, Math.ceil(ageDays * 86400000), 'sys_updated_on');

cleaner.clean();

gs.log("Ending clean-up of table: " + tableName);

[jesusemelendezm]

Hi Stijn,

How did it go? did you get to clean up your entire CMDB?

I am interested to know if It is recommended to clean it up to start from scratch, following this plan:

1) TURN OFF discovery schedules.

2) Clean up CMDB except business services (because these are already in use).

3) Upload inventory manually from excel through transform maps.

2) TURN ON discovery schedules to refresh and update inventory with configuration information.

Any thoughts?

Thanks,

Jesus E. Melendez M.

[aaron_damyen]

Hi Jesus Melendez,

CMDB clean went well.   I found this thread again as I'm finally able to get to the last table, and forgot the code.   While this may not be relevant to you now, maybe someone else will find it useful.

There wouldn't be any need to turn off discovery schedules while clearing out your CMDB.   Discovery will always create new records if need be.

However, uploading your inventory before running Discovery might be a problem.   The import will create records that Discovery might not use.   Discovery uses various (and numerous) different identifier to recognize unique devices.   Often, the name of the device is not one of them.   Thus, Discovery may decide that the device it has found is different than the entries in your inventory and create a new record.   Now, you'll have ghost devices.

By running Discovery first, the options for aligning the inventory data with the CMDB data grow.   You could use name (though it might not be unique), IP address, MAC address or even serial number to coalesce the transform map.   Thus, it is you who will be deciding if the device exists in the CMDB.

For years, we thought just by creating a Computer record in the CMDB with the name and IP address, that Discovery would automatically populate the record.   However, it did not and we now have a smattering of duplicate servers in our CMDB: one manual, one discovered.

[Chris York]

Try this script:

var tu = new TableUtils('cmdb_ci');

var ext = tu.getAllExtensions();

var ext1 = ext.toArray();



for (i = 0; i < ext1.length; i++) { 

   gs.print('Truncating table: ' + ext1[i]);

   gs.truncateTable(ext1[i]);

   }