Search code examples
servicenow

For table cmdb_rel_ci, I want to retrieve unique parent.sys_class_name with count for "type=In Rack::Rack contains"

For table cmdb_rel_ci, I want to retrieve unique parent.sys_class_name with count for "type=In Rack::Rack contains". I am doing practice in out of the box instance. At table level URL is as below:

URL

I want to retrieve result from above URL with my below script.

var count = new GlideAggregate('cmdb_rel_ci');
count.addQuery('type','e76b8c7b0a0a0aa70082c9f7c2f9dc64');// sys_id of type In Rack::Rack contains e76b8c7b0a0a0aa70082c9f7c2f9dc64
count.addAggregate('COUNT', 'parent.sys_class_name');
count.query();

while(count.next()){  
  var parentClassName = count.parent.sys_class_name.toString();
  var parentClassNameCount = count.getAggregate('COUNT','parent.sys_class_name');
  gs.log(parentClassName + " : " + parentClassNameCount );
}
The issue is I am getting parentClassName empty.

Solution

  • Try this instead:

    var parentClassName = count.getValue("parent.sys_class_name")

    Since it's a GlideAggregate query (instead of GlideRecord), the query being issued isn't returning all of the fields on the target table. With GlideRecord, dot-walking through a reference field (e.g. parent.sys_class_name) automatically resolves that referenced record to provide access to its field values. This is made possible by the fact that the driving/original query brought back the value of the parent field. This is not happening with GlideAggregate. The query in this case basically looks like:

    SELECT cmdb1.`sys_class_name` AS `parent_sys_class_name`, count(*) 
FROM (cmdb_rel_ci cmdb_rel_ci0  LEFT JOIN cmdb cmdb1 ON cmdb_rel_ci0.`parent` = cmdb1.`sys_id` )  
WHERE cmdb_rel_ci0.`type` = 'e76b8c7b0a0a0aa70082c9f7c2f9dc64' 
GROUP BY cmdb1.`sys_class_name` 
ORDER BY cmdb1.`sys_class_name`

    So, you actually have access specifically to that dot-walked sys_class_name that's being grouped, but not through the dot-walk. The call to getValue("parent.sys_class_name") is expectedly resolved to the returned column aliased as parent_sys_class_name.

    That being said, what you're doing probably should also work, based on user expectations, so you've not done anything incorrect here.