Understanding the CMDB Extended Tables

[petercawdron]

This is a quick and dirty script, but quite useful as it allows you to see all the fields that are unique to extended tables in the CMDB. As tables inherit fields from their base table, it can be useful to see what is actually added by each CI class.

Run this in a background script...

var table = new TableUtils("cmdb_ci");
var allTables  = table.getHierarchy().toArray();

var fields = {};
var fieldLabels = {};
var tables = {};

allTables.forEach(function(thisTable){

  var dictionary = new GlideRecord('sys_dictionary');
  dictionary.addEncodedQuery('name='+thisTable+'^internal_type!=collection^ORinternal_type=NULL');
  dictionary.query();

  while(dictionary.next()){

    if(!fields.hasOwnProperty(dictionary.element.toString())){
      fields[dictionary.element.toString()] = thisTable; 
      fieldLabels[dictionary.element.toString()] = dictionary.column_label.toString()
    }

    if(!tables.hasOwnProperty(thisTable)){
      var getTable = new GlideRecord('sys_db_object');
      if(getTable.get('name',thisTable)){tables[thisTable] = getTable.label.toString(); }
    }
  }
}) 

for(var eachField in fields){
  gs.info(tables[fields[eachField]]+','+fields[eachField] + ',' + fieldLabels[eachField] + ',' + eachField)
}

And you'll get something like... 

Which you can dump into Excel. Find/replace the *** Script: portion and then use the Text to Columns function and...

Have fun