Script to fetch all fields from a table which is not inherited

[ShAn21]

HI All,

I have a table which is extended from CMDB CI. There are also many custom fields created in the child table.

Does anyone have a way to fetch the list all the custom fields from the child table via script?. it should only give results of non extended fields belonging to the child. It a huge table so I cannot check fields one by one.

I have done this before but somehow unable to get hold of the script again.

Thanks

Shreyas

[ShAn21]

Hi Sanjiv

While starting a custom column name with 'u_' is a best practice. But its not mandatory in a scoped application atleast. Right?

Thanks

Shreyas

[SanjivMeher]

But I believe all the CMDB tables are in global scope....There there are any in a different scope, it should still add u_ to it. We use qualys ci table....All the fields we added has a u_.

[Hitoshi Ozawa]

Non-derived column names can be obtained by the following script:

var tableName = 'incident';

var grSys = new GlideRecord('sys_dictionary');
grSys.addEncodedQuery('name=' + tableName + '^internal_type!=collection^internal_typeISNOTEMPTY');
grSys.addOrderBy('column_label');
grSys.query();

while(grSys.next()) {
  gs.info(grSys.column_label + ',' + grSys.element + ',' + grSys.internal_type + ',' + grSys.reference + ',' + grSys.display + ',' + grSys.active + ',' + grSys.sys_created_by + ',' + grSys.sys_created_on);
}

All column names including those derived from parents can be obtained by the following script:

var tableName = 'cmdb_ci_linux_server';

var tableUtil = new TableUtils(tableName);
var parentTables = tableUtil.getTables().toArray();

for (var i=0; i<parentTables.length; i++) {
  var table = parentTables[i];
  var grSys = new GlideRecord('sys_dictionary');
  grSys.addEncodedQuery('name=' + table + '^internal_type!=collection^internal_typeISNOTEMPTY');
  grSys.addOrderBy('column_label');
  grSys.query();

  while(grSys.next()) {
    gs.info(table + ',' + grSys.column_label + ',' + grSys.element + ',' + grSys.internal_type + ',' + grSys.reference + ',' + grSys.display + ',' + grSys.active + ',' + grSys.sys_created_by + ',' + grSys.sys_created_on);
  }
}

[ShAn21]

Hi Hitoshi,

The first script you mentioned no longer works for any custom extended tables.

Each table will have its own cloned descendent for each column. Try extending a new table from CMDB_CI and run this script.

Thanks

Shreyas 

[Hitoshi Ozawa · Accepted solution]

My bad. The script worked on extended tables in cmdb but seems like it doesn't for custom tables.

Fixed the script.

var tableName = 'u_test_extend';

var tableUtil = new TableUtils(tableName);
var parentTables = tableUtil.getTables().toArray();
parentTables.shift();

var parentColumns = [];
for (var i=0; i<parentTables.length; i++) {
  var table = parentTables[i];
  var grSysParent = new GlideRecord('sys_dictionary');
  grSysParent.addEncodedQuery('name=' + table + '^internal_type!=collection^internal_typeISNOTEMPTY');
  grSysParent.addOrderBy('column_label');
  grSysParent.query();

  while(grSysParent.next()) {
    parentColumns.push(grSysParent.element.toString());
  }
}

var grSysCustom = new GlideRecord('sys_dictionary');
grSysCustom.addEncodedQuery('name=' + tableName + '^internal_type!=collection^internal_typeISNOTEMPTY');
grSysCustom.addOrderBy('column_label');
grSysCustom.query();

var customTableColumns = [];
while(grSysCustom.next()) {
  customTableColumns.push(grSysCustom.element.toString());
}

for (var j=0; j<parentColumns.length; j++) {
  var x = customTableColumns.indexOf(parentColumns[j]);
  if (x > -1) {
    customTableColumns.splice(x,1);
   }
}

gs.info(customTableColumns);