Cleaning up the CMDB

Stijn Verhulst3 · ‎01-27-2014

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

aaron_damyen · ‎05-20-2014

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);

View solution in original post

jesusemelendezm · ‎11-10-2014

Hi Chris, Thanks. I decided not to run with this idea. There are already many referenced records from other apps incident, change, problem, etc. I'll keep this script for any future need. Regards Jesus

Antony_Alldis · ‎06-01-2016

Please DO NOT use the above code under any circumstances!

ServiceNow do not recommend running table truncation on any of the CMDB tables due to the relationships that are built from extended tables, references, SNC Component metric CI's, and all of the "undeletable" assets and models that can be left over.

Running a script that just truncates the cmdb_ci table in this manner, can result in "ghost" (orphaned) records that can cause issues with the UI forms loading data etc... and in the worst case, can result in an unusable CMDB that may require your whole instance to be restored from backup!

This is why the use of gs.sql was deprecated in the Geneva Release, to stop issues like this from happening.

The following Documentation article discusses the safest way to remove data from a table.

Delete all records from a table

You may also consider using a Table Cleaner to remove data, which will also delete the data using a cascade delete mechanism in a SAFE manner.

arron_ubhi · ‎06-01-2016

I agree with Tony. There have been too many cases where truncating the CMDB has caused orphan records to occur. The safest methods would be to use the procedure as Tony has advised.

techsnow · ‎12-28-2016

Stijn,

I know its month ago......But I think you are better prepped now to share what worked ! I am in the same boat now but from years of experience it seems cleaning up should NEVER mean deletion as CIs already have relationships to Incidents, Changes and Problems.

Did you start a fresh or changed classes for CI's ? What worked ?

Stijn Verhulst3 · ‎12-28-2016

Hi Fred,

in my case I had to cleanup the CMDB which was still in an experimental phase, as we had to implement it at the customer site starting from scratch while implementing Discovery.

In the end I used a Fix Script applying the GlideTableCleaner API (see the post of Aaron above), which worked pretty fine.

However; if not misunderstood from my side, you have to cleanup a CMDB already being used in the past, correct? As for the latter, there's no other option than, in my humble opinion:

(1) Drawing out some kind of map / hierarchy reflecting the different CI classes used to store your key CIs within your infrastructure.

(2) Verifying which particular CI classes are already present in ServiceNow and thus can be used. For non-existing CI classes, you'll have to create those of course.

(3) If possible, change the Class of the existing CI to its new class. However; keep in mind changing the Class to a child class will keep all of the existing information, changing to a parent class will let you loose information (think of the table / field inheritance witin the ServiceNow platform). This way your relatinships towards ny existing tasks like incidents, changes & problems shouldn't be in danger. Do the experiment first though!

(4) In case it's not possible by changing the Class but you have to recreate the CI; create for example a Fix Script which will replace the sys ID of the CI in any existing task with the sys ID of the new one.

Hope this helps you forward! If not or I would have misunderstood your question, let me know.

Kind regards,

Stijn