Get all downstream relationship(child relations) from Relationship Table(cmdb_rel_ci)for each record

[Kartik Choudha1]

Requirement - 

Irrespective of the CI Class, We want to get all the sys_ids of 'child relationship record' in the Array.

Let's suppose, parent records "A" is having two child(downstream relationship) "Ab" and "Ac", These two child record has two more child relationships("Abc" and Abd).

Parent - "A"

Child of Parent "A" are - Ab, Ac

Check child of Ab(now Ab is parent and it's child is) -> Abc

Check child of Abc(now Abc is parent and it's child is) -> No child left and return array

Check child of Ac(now Ac is parent and it's child is) -> Abd

Check child of Abd(now Abd is parent and it's child is) -> No child left and return array

Total child records of "A" is Ab, Ac, Abc, Abd. These 4 record's sys_id will get stored in an Array for further use.

Solution - This has been done via recursion successfully. (In script, Parent records are all type of Business application 'CI Class')

try {
    var vArrChild = [];
    var vChild = '';
    var gaRelation = new GlideAggregate('cmdb_rel_ci');
    gaRelation.addEncodedQuery('parent.sys_class_name=cmdb_ci_business_app');
    gaRelation.groupBy('parent');
    gaRelation.groupBy('child');
    //gaRelation.setLimit(1);
    gaRelation.query();
    while (gaRelation.next()) {
        //gs.print(gaRelation.getDisplayValue('child') + " & " + gaRelation.getDisplayValue('parent'));
        vChild = gaRelation.getValue('child');
        vArrChild.push(getChildRelation(vChild));
        gs.info("Child "+vArrChild);
    }


} catch (err) {
    gs.info("Child ERROR: " + err);
}

function getChildRelation(vChild) {

    var vArrChild = [];
    vArrChild.push(vChild);
    var vNestedChild = '';
    var gaRelation = new GlideAggregate('cmdb_rel_ci');
    gaRelation.addQuery('parent', vChild);
    gaRelation.groupBy('parent');
    gaRelation.groupBy('child');
    gaRelation.query();
    while (gaRelation.next()) {
        // gs.print("rec " + gaRelation.getDisplayValue('child') + " & " + gaRelation.getDisplayValue('parent'));
        vNestedChild = gaRelation.getValue('child');

        vArrChild.push(getChildRelation(vNestedChild));
    }
    //gs.print('rec func end');
    return vArrChild;
}

This code works correctly.

Challenge:

This code works for such records which has 100 or 500 childs etc. But we have few record which has more than 1000 child. And ServiceNow doesn't support more than 1000 function calls or recursion call and stops the execution of script. PFB error caught in catch block.

ERROR: org.mozilla.javascript.RhinoStackOverflowException: Maximum JavaScript call depth of 1000 exceeded

Any ideas or suggestion would be appreciated.

I am already trying to reduce the child record criteria with business.

[Bert_c1]

I would use GlideRecord instead of GlideAggregate. Since you are not using anything from here:

https://developer.servicenow.com/dev.do#!/reference/api/washingtondc/server_legacy/c_GlideAggregateA...

And I don't see any stop condition in your script. The following is from a script include used by a Dynamic Filter to get subordinate users for the current user. (sys_user table manager field.)

function mySubordinateUsers() {
	var userManager = gs.getUserID();
//	gs.info('mySubordinateUsers: Manager = ' + userManager);

	var users = [];			// users to check
	var subUsers = [];		// found subordinate users

	// add the current "user" to support check for resursion in data
	users.push(userManager);	// starting user to get subordinates
	subUsers.push(userManager);

	var foundNew = true;
	//Get the users where current user is the 'manager'
	while (foundNew) {
		var subs = [];
		for (i = 0; i < users.length; i++) {
			foundNew = getSubordinateUsers(users[i], subs);
		}
		// clear array to process and add subordinate users to array
		users.length = 0;
		for (i = 0; i < subs.length; i++) {
			// check to see if found sub is in the current list, prevent resursion
			if (subUsers.indexOf(subs[i]) == -1) {
				subUsers.push(subs[i]);
				users[i] = subs[i];		// reload users array
			}
		}
//		if (foundNew)
//			gs.info("processing users: " + users);
	}
//	gs.info("user list = " + subUsers);
	return subUsers;
}

function getSubordinateUsers(mgrUser, usrArray) {
	// next get the list of child manager users
	var foundRec = false;
	var subUsr = new GlideRecord('sys_user');
	subUsr.addQuery('manager', mgrUser);
	subUsr.query();
//	gs.info("SubordinateUsers: Found " + subUsr.getRowCount() + " record where manager = " + mgrUser);
	while (subUsr.next()) {
		usrArray.push(subUsr.getValue('sys_id'));
//		gs.info("SubordinateUsers: adding " + subUsr.user_name + " as subordinate");
		foundRec = true;
	}
//	gs.info("SubordinateUsers: subordinate users = " + usrArray);
	return foundRec;
}

 Th 

[Kartik Choudha1]

I am using groupBy here and GA functionality is required to me later when i accommodate this script in some other logic.

No stop condition is required as whenever it doesn't find the child record, it returns the array.

Thanks.

[Bert_c1]

Are you checking for the following scenario: A children - Ac, Ab

Ac children - Aca, AcB

Aca children - A

Seems you script never stops.