Query cmdb_rel_ci table for parents parent

[abelal]

Hi all, 

I'm at my wits end with what should be simple (at least in SQL it is) but struggling and hoping someone can offer advice.

I've got a need to query VMWare OS templates from ServiceNow where the template is from a particular vCenter Datacenter. I'm using cmdb_rel_ci to query for this however, the parent for cmdb_ci_vmware_template is a datastore, not a vCenter instance but, the parent of datastore is a vCenter.

This is basically how it's presented in cmdb_ci_vmware_template:
datacenter -> datastore -> template

Although I can pull all VMWare templates using this query `parent.sys_class_name=cmdb_ci_vcenter_datastore^child.sys_class_name=cmdb_ci_vmware_template`

I also need to filter on where datastore is a child of datacenter "x".

Pseudocode query would be 'select templates where datastore parent is datacenter "mydatacenter"'

Any thoughts?

[HIROSHI SATOH]

How about this?

※Not tested

var datacenterName = 'mydatacenter';

var templates = new GlideRecord('cmdb_ci_vmware_template');
templates.addQuery('parent.sys_class_name', 'cmdb_ci_vcenter_datastore');
templates.addQuery('child.sys_class_name', 'cmdb_ci_vmware_template');
templates.addJoinQuery('parent', 'cmdb_rel_ci', 'child', 'sys_id');
templates.addJoinQuery('parent.parent', 'cmdb_rel_ci', 'child', 'sys_id');
templates.addJoinQuery('parent.parent.parent', 'cmdb_ci_vcenter_dc', 'sys_id', 'parent');
templates.addQuery('parent.parent.parent.name', datacenterName);
templates.query();

while (templates.next()) {
    gs.log(templates.name);
}

[abelal]

@HIROSHI SATOH thanks but unfortunately that doesn't work. Table cmdb_ci_vmware_template doesn't contain parent nor child

field name 'parent.sys_class_name' not found in table 'cmdb_ci_vmware_template'
field name 'child.sys_class_name' not found in table 'cmdb_ci_vmware_template'
field name 'parent.parent.parent.name' not found in table 'cmdb_ci_vmware_template'

 What I really need though is the this type of query:

parent.sys_class_name=cmdb_ci_vcenter_datastore^child.sys_class_name=cmdb_ci_vmware_template

Any further help would be greatly received.

[HIROSHI SATOH]

It's a brute force method, but I think it's easiest to loop through "cmdb_rel_ci" twice and search.

[abelal]

It was proving to be too difficult, so I've come up with an alternative. Using the MID server and a powershell action I'm making an API call directly to vCenter to get all templates. Thanks for the suggestion though @HIROSHI SATOH