The Problem

A few days ago, we started having strange behavior in our Configuration Management Database (CMDB).  When looking at certain classes of Configuration Items (CI) we would see the User Interface (UI) display repeated buttons, and there were no CIs in the List View.  Additionally, if you went to the Tables & Columns module and tried to delete all records from cmdb_ci_netgear, the UI would report that the table was already empty (but it wasn’t):

IPNetGear

Worse still, none of the New or Edit… buttons worked on this List View any longer.

The Investigation

Our partner talked internally, and found another consultant who had seen this behavior previously, he informed us that it occurs within CMDB List Views if you have a CI with an invalid sys_class_name.  What’s a sys_class_name you may ask?  The sys_class_name is a reference field that identifies a sub-class of a base table.  The base table of your CMDB is cmdb_ci, this is where all of the primary attributes of a CI are stored.  However, CIs in this table all have a sys_class_name that defines the sub-class of CI.  For example, an IP Router will have its base information stored in cmdb_ci, but it will have a sys_class_name of cmdb_ci_ip_router which identifies that CI as an IP Router (technically, it’s sys_id will be stored in the cmdb_ci_ip_router table, so ServiceNow can also do reverse lookups from the IP Router table into the base CMDB).

In order to see all of the sys_class_name entries you have stored in your CMDB, you have to run a Background Script.

**A Note About Background Scripts:  **These are very dangerous.  You will have to enable security_admin to even see the choice in the navigation, making mistakes in Background Scripts can have disastrous results on your ServiceNow Instance.  You have been warned.

The ServiceNow partner I’m currently working with gave me the following Background Script in order to find the invalid sys_class_name entries:

1 2 3 4 5 6 7 8 (function() { var gr = new GlideRecord("cmdb_ci_hardware"); gr.orderBy("sys_class_name"); gr.query(); while (gr.next()) { gs.print("Class = " + gr.getValue("sys_class_name") + " Name = " + gr.getValue("name")); } })();
The above script will produce output that looks like this:
1 2 3 4 5 6 7 Rebuilt metadata for table: cmdb_ci_hardware *** Script: Class = cmdb_ci_computer Name = AWORKSTATION *** Script: Class = cmdb_ci_esx_server Name = AUCSVIEWSRV *** Script: Class = cmdb_ci_ip_router Name = AROUTER *** Script: Class = cmdb_ci_ip_vpn Name = someasafw *** Script: Class = cmdb_ci_win_server Name = AWINSERVER *** Script: Class = cmdb_ci_vcenter_server   Name = AVCENTERSRV
All **sys_class_name** entries MUST reference an EXISTING table.  So, how do you find the bad **sys_class_name**?  Compare all of your **sys_class_name** entries to the tables found under **Tables & Columns** in the **System Definition** application.

When I compared ours to the existing tables I found two sets that were incorrect, there were no such tables as cmdb_ci_ip_vpn or cmdb_ci_vcenter_server.

How did this happen?  This is how:  Transform Map Scripts – ServiceNow Wiki

See section 2.2.1 Example 1: Populating Child Tables by Setting Class Names in an Import to the CMDB.  You see, we were importing records into the CMDB from an external source system, and since our external source system had a variety of classes of CIs in it, we were setting the sys_class_name ourselves to get them sorted into the correct CI table in the CMDB.  Unfortunately, we used the incorrect sys_class_name, the same result would happen if you had a typo.  The code in that example on the ServiceNow Wiki is:

1 2 3 4 5 6 7 8 9 10 var operating_system = source.u_operating_system.toString(); //This if statement uses JavaScript regular expressions to search the operating system if ( operating_system.match(/linux/i) != null ){ target.sys_class_name="cmdb_ci_linux_server"; }; if ( operating_system.match(/win/i) != null ){ target.sys_class_name="cmdb_ci_win_server"; };
If you use the incorrect **sys_class_name** in that code, or have a typo, you’ll trash your List Views for those objects (and potentially parent and child List Views of those objects).  ServiceNow’s UI really doesn’t like invalid **sys_class_name** entries in the CMDB (or anywhere, as this is used everywhere in the system).

The Solution

So, now we knew why the UI was behaving so strangely, we figured we could fix it with another Background Script.  The following script was used to try to clean the offending entries out of the CMDB:

1 2 3 4 5 6 7 8 9 10 11 12 (function() { var gr = new GlideRecord("cmdb_ci_hardware"); gr.orderBy("sys_class_name"); gr.query(); while (gr.next()) { gs.print("Class = " + gr.getValue("sys_class_name") + "   Name = " + gr.getValue("name")); if(gr.sys_class_name == "cmdb_ci_vcenter_server") { gs.log("Deleting record " + gr.getDisplayValue(), "Bad CMDB HW Class Cleanup"); gr.deleteRecord(); } } })();
Looks like that code should work and clean up the invalid **cmdb_ci_vcenter_server****sys_class_name** entries.  However, it wouldn’t run.  We just got Javascript Null Pointer Exceptions, because the standard Glide tools also don’t like invalid **sys_class_name** entries either.

We contacted ServiceNow about the issue and they stated that since it wasn’t a bug in their system that caused the problem (more on that in a bit) that they would refer us to ServiceNow Professional Services to correct the problem.  To be fair, they did correct the initial two entries we found in our system, after 4 days of working with them, but when that didn’t resolve the issue, they pointed out that we had more, and they referred the case to PS.

So, how do you fix this issue without paying ServiceNow to fix it for you? Something much more dangerous than Background Scripts, raw SQL.

Another Solution

When we ran into a wall with the Background Script, I started looking for a solution that would give us even lower-level control of the tables.  What I found is an undocumented GlideSystem call:  gs.sql().

A Note About gs.sql():  I cannot stress this enough. This is undocumented for a reason. There are VERY few security checks when you use gs.sql() and the damage you do with this GlideSystem call can be absolute and unrepairable. You should not use it, you should forget you found it here. Incorrect use, a mistake, a typo, can easily render your Instance completely and totally unusable.

The gs.sql() statement can be used as a Background Script and can allow you to make repairs or validate data that you may not have the ability to do through other means. In our case, we needed to delete (or change) the invalid sys_class_name entries on our records in the cmdb_ci table. We didn’t have another option.

So, to get the UI in our List Views working again, I deleted the invalid CI entries in cmdb_ci using the following Background Script:

1 2 gs.sql("delete from cmdb_ci where sys_class_name='cmdb_ci_ip_vpn'") gs.sql("delete from cmdb_ci where sys_class_name='cmdb_ci_vcenter_server'")
Following the two updates above, our UI List View errors were corrected, and the records we weren’t seeing suddenly appeared correctly.  Problem solved!

What I Learned

In retrospect, I should have changed them to be the correct reference, rather than deleting the entries from cmdb_ci.  The reason is that the UI just didn’t like the invalid sys_class_name, so correcting them would have corrected the actual problem, without exposing us to Orphaned Records in the CMDB, which we then figured out was another problem (that’s a different Post).

Finally, this is a ServiceNow bug.  Period.  Using documented GlideSystem calls and even using the code they provided as examples in our Transform Maps should not have put us into this situation.  If you try to set an invalid sys_class_name, using ServiceNow’s documented code, the system should report an error on insert and abort the insert.  Just writing the invalid sys_class_name to the cmdb_ci table silently, and then having that completely corrupt your List View is clearly a bug.