question Re: Table API - Get Child Table Fields in Developer forum

Table API - Get Child Table Fields

mattp — Thu, 23 Feb 2017 23:08:27 GMT

We have a use case where we want to query the cmdb_ci table for specific classes, and return fields from those classes that aren't on the base cmdb_ci table. An example would be 'kernel_release', which exists on the 'cmdb_ci_linux_server' table, but not on cmdb_ci. Is there a way to return the field 'kernel_release' when it exists when querying cmdb_ci over the table API?

Thank you!

Re: Table API - Get Child Table Fields

rajeevlochan82 — Thu, 23 Feb 2017 23:22:09 GMT

I do not think so as Table API is out of box , so unless you write some custome web-service , there does not seem to any way to retrieve it from table api if the field does not directly exist in a table

Re: Table API - Get Child Table Fields

srinivasthelu — Thu, 23 Feb 2017 23:36:01 GMT

Hi Matt,

You might want to give a try with this.

https://instnace.service-now.com/api/now/table/cmdb_ci?sysparm_fields=ref_cmdb_ci_server.classification&sysparm_limit=1

The idea here is, you can pass the field names of specific tables if you know the already to see the values

Re: Table API - Get Child Table Fields

nosugrefr — Fri, 24 Feb 2017 00:15:44 GMT

I've put a little though into this for a use case I haven't gotten to yet, and didn't find an easy solution on the shelf.

I think... if you know the end class tables are of interest, you could do something like query each of those tables for the more specific droids you are looking for, then use an exclude filter in cmdb_ci to exclude the records you should already have based on a filter.

Apparently NOT IN works since sys_class_name is a pick list field.

.../api/now/table/cmdb_ci?sysparm_query=sys_class_nameNOT%20INcmdb_ci_server,cmdb_ci_hardware,cmdb_ci_computer&sysparm_fields=sys_class_name&sysparm_limit=1000

Re: Table API - Get Child Table Fields

nosugrefr — Fri, 24 Feb 2017 16:43:03 GMT

Perfect. Thank you Srinivas. I was looking for exactly that and didn't find it the other day.

Re: Table API - Get Child Table Fields

mattp — Fri, 24 Feb 2017 17:35:35 GMT

thanks, that works! sysparm_fields=ref_cmdb_ci_server.classification was the key. Just so I understand, where does the 'ref_' beefore cmdb_ci come from, or why is it needed?

Re: Table API - Get Child Table Fields

srinivasthelu — Sun, 26 Feb 2017 22:35:46 GMT

Hi Matt,

Glad that helped :-).

"ref_ " is to let the system know that you are looking for fields of extended tables. This feature is added(I think)with Geneva and comes in handy in scenarios like this.

Re: Table API - Get Child Table Fields

rajeevlochan82 — Sun, 26 Feb 2017 23:22:37 GMT

Good Information Srinivas !!

Re: Table API - Get Child Table Fields

Rishi18 — Mon, 26 Jun 2017 06:50:31 GMT

Hi Srinivas,

I have a similar requirement but this link is not working.

Can you help me out?

Re: Table API - Get Child Table Fields

rajeevlochan82 — Mon, 26 Jun 2017 19:55:02 GMT

Hi Rishi,

Thats a sample link posted by Sri. You need to replace the instance name with your instance name and your child field.