CMDB Mass Downstream Relationship Query - collect ALL relationships with a script

gputker · ‎06-16-2020

Ever getting requests to be able to query your CMDB for all/every downstream relationship? It is something that has come up frequently for me. The GUI OOTB CMDB query builder is of course incredibly powerful (and easy to use) but I also feel as though the more advanced the query gets the LESS data it returns. This makes sense as per its design but what I really needed was a way to pick a top level CI (or class of CIs) and then pick up every single downstream relationship and CI. For this I wrote the script below and it is working incredibly well.

WARNING: This script will potentially generate tens of thousands, hundreds of thousands or even millions of rows of log file data. Use with extreme caution in a PROD instance. Prudent advice is to clone PROD into a sub-prod instance and then run this script there.

var ident = 'xvxvxvxvx01'; //unique identifier string to filter in LOGS.
gs.log(ident+' SCRIPT STARTED');
var getAllBusinessServices = new GlideRecord('cmdb_ci_service'); // scan Business Services CMDB class.
//getAllBusinessServices.setLimit(1); //use for safety when experimenting with script.
//getAllBusinessServices.addQuery('name','TEST Business Service'); //use for safety when experimenting with script.
getAllBusinessServices.addQuery('service_classification','Business Service'); // capture ALL business services.
getAllBusinessServices.query();
while (getAllBusinessServices.next()){
var businessServiceName = getAllBusinessServices.getDisplayValue('name');
var businessServiceSysID = getAllBusinessServices.getUniqueValue();
returnAllDownstreamCIRelationships(businessServiceName,businessServiceSysID); //call function to get first round all child downstream relationships.
}
gs.log(ident+' SCRIPT FINISHED');

function returnAllDownstreamCIRelationships(businessServiceName,businessServiceSysID){
var counter = 0;
var arrayForParents = [];
var finalList = [];
var ciRels0 = new GlideRecord('cmdb_rel_ci'); //scan CI relationships table seeking out PARENTS of all business services.
ciRels0.addQuery('parent',businessServiceSysID);
ciRels0.addQuery('parent.operational_status','!=','6');
ciRels0.addQuery('child.operational_status','!=','6');
ciRels0.query();
while(ciRels0.next()){ //for each parent found define all neede variables.
var childName0 = ciRels0.getDisplayValue('child');
var typeName0 = ciRels0.getDisplayValue('type');
var parentName0 = ciRels0.getDisplayValue('parent');
var childSysID0 = ciRels0.getValue('child');
var parentOpStatus0 = ciRels0.parent.operational_status.getDisplayValue();
var childOpStatus0 = ciRels0.child.operational_status.getDisplayValue();
var relConcat0 = parentName0+typeName0+childName0;
var getclass0 = ciRels0.child.sys_class_name;
var getDepartment0 = ciRels0.child.department.getDisplayValue();
var getEOLdate0 = ciRels0.child.u_end_of_life_month_year.getDisplayValue();
// call the dontLoadDuplicates function to ensure that duplicate relationships are STOPPED. This prevents LOOPING.
dontLoadDuplicates(relConcat0,parentName0,childName0,getDepartment0,getclass0,getEOLdate0,businessServiceName,parentOpStatus0,childOpStatus0);
//there is logic in the dontLoadDuplicates function that if a duplicate is found this while loop stops for the duplicate record.
arrayForParents.push(childSysID0); // the relationship is NOT a duplicate then loadit into an array.
}
getNextLayer(arrayForParents); //Call the next layer function.

//This function ensures that we do not load duplicates. This keeps our final data set clean and prevents looping.
function dontLoadDuplicates(relConcat0,parentName,childName,getDepartment,getclass,getEOLdate,businessServiceName,parentOpStatus,childOpStatus){
var dupeCheck = finalList.indexOf(relConcat0);
if(dupeCheck == -1){
finalList.push(relConcat0);
var length = finalList.length;
gs.log('For Business Service: $'+businessServiceName+'$Hop Tier: $'+counter+'$ Parent Op Status: '+parentOpStatus+'$ Child Op Status: '+childOpStatus+'$ For the Relationship name: $'+relConcat0+' $ For Department: $'+getDepartment+' $ For EOL: $'+getEOLdate+' $ '+getclass+' $ '+ident);
return true;
}
return false;
}
}

Hafsa1 · ‎06-20-2020

Hi

if I want to modify this to update one field from parent business service to all of downstream CIs in BR . where to modify it?

gputker · ‎06-20-2020

Hi Hafsa,

Using this script to update a field on all downstream CIs would be incredibly dangerous 1) due to the consumption of system resources and 2) the ability to make a mistake that is potentially destructively pervasive to the entire CMDB. That said - you could certainly do it if you are willing to accept those risks. I would certainly make sure you perfect this in a sub-prod instance and I would use set-limiters first before unleashing the full power. Please see below in RED for my suggestions on what you need to do (I have NOT tested this)... (and some suggestions in BLUE)

var ident = 'xvxvxvxvx01'; //unique identifier string to filter in LOGS.
gs.log(ident+' SCRIPT STARTED');
var getAllBusinessServices = new GlideRecord('cmdb_ci_service'); // scan Business Services CMDB class.
//getAllBusinessServices.setLimit(1); //use for safety when experimenting with script.
//getAllBusinessServices.addQuery('name','TEST Business Service'); //use for safety when experimenting with script.
getAllBusinessServices.addQuery('service_classification','Business Service'); // capture ALL business services.
getAllBusinessServices.query();
while (getAllBusinessServices.next()){

var parentCIValueYouWishToPushDownStream = getAllBusinessServices.getValue('name of parent business service field');

//please note that the value you extract from the parent business service must be compatible with the type of value in the field you set.
var businessServiceName = getAllBusinessServices.getDisplayValue('name');
var businessServiceSysID = getAllBusinessServices.getUniqueValue();
returnAllDownstreamCIRelationships(businessServiceName,businessServiceSysID); //call function to get first round all child downstream relationships.
}
gs.log(ident+' SCRIPT FINISHED');

function returnAllDownstreamCIRelationships(businessServiceName,businessServiceSysID){
var counter = 0;
var arrayForParents = [];
var finalList = [];
var ciRels0 = new GlideRecord('cmdb_rel_ci'); //scan CI relationships table seeking out PARENTS of all business services.
ciRels0.addQuery('parent',businessServiceSysID);
ciRels0.addQuery('parent.operational_status','!=','6');
ciRels0.addQuery('child.operational_status','!=','6');
ciRels0.query();
while(ciRels0.next()){ //for each parent found define all neede variables.
var childName0 = ciRels0.getDisplayValue('child');
var typeName0 = ciRels0.getDisplayValue('type');
var parentName0 = ciRels0.getDisplayValue('parent');
var childSysID0 = ciRels0.getValue('child');
var parentOpStatus0 = ciRels0.parent.operational_status.getDisplayValue();
var childOpStatus0 = ciRels0.child.operational_status.getDisplayValue();
var relConcat0 = parentName0+typeName0+childName0;
var getclass0 = ciRels0.child.sys_class_name;
var getDepartment0 = ciRels0.child.department.getDisplayValue();
var getEOLdate0 = ciRels0.child.u_end_of_life_month_year.getDisplayValue();
// call the dontLoadDuplicates function to ensure that duplicate relationships are STOPPED. This prevents LOOPING.
dontLoadDuplicates(relConcat0,parentName0,childName0,getDepartment0,getclass0,getEOLdate0,businessServiceName,parentOpStatus0,childOpStatus0);
//there is logic in the dontLoadDuplicates function that if a duplicate is found this while loop stops for the duplicate record.

ciRels0.setValue('field you wish to set', parentCIValueYouWishToPushDownStream);

ciRels0.update();

//please note that the value you extract from the parent business service must be compatible with the type of value in the field you set.
arrayForParents.push(childSysID0); // the relationship is NOT a duplicate then loadit into an array.
}
getNextLayer(arrayForParents); //Call the next layer function.

function getNextLayer(theArray){ //here we simply repeat the process above however there is a hasNext query which STOPS the resturn of results if no parent records are found from child records.
var arrayForParents2 = [];
var ciRels1 = new GlideRecord('cmdb_rel_ci');
ciRels1.addQuery('parent','IN',theArray);
ciRels1.addQuery('parent.operational_status','!=','6');
ciRels1.addQuery('child.operational_status','!=','6');
ciRels1.query();
if(ciRels1.hasNext()){
counter ++;
while(ciRels1.next()){
var childName1 = ciRels1.getDisplayValue('child');
var typeName1 = ciRels1.getDisplayValue('type');
var parentName1 = ciRels1.getDisplayValue('parent');
var childSysID1 = ciRels1.getValue('child');
var parentOpStatus1 = ciRels1.parent.operational_status.getDisplayValue();
var childOpStatus1 = ciRels1.child.operational_status.getDisplayValue();
var relConcat1 = parentName1+typeName1+childName1;
var getclass1 = ciRels1.child.sys_class_name;
var getDepartment1 = ciRels1.child.department.getDisplayValue();
var getEOLdate1 = ciRels1.child.u_end_of_life_month_year.getDisplayValue();
var actionORnot = dontLoadDuplicates(relConcat1,parentName1,childName1,getDepartment1,getclass1,getEOLdate1,businessServiceName,parentOpStatus1,childOpStatus1);
if(actionORnot){
arrayForParents2.push(childSysID1);

ciRels1.setValue('field you wish to set', parentCIValueYouWishToPushDownStream);

ciRels1.update();

//please note that the value you extract from the parent business service must be compatible with the type of value in the field you set.
}
}
getNextLayer(arrayForParents2); //VERY IMPORTANT - recall the same function - deliberatly loop. Because of the hasNext we can safely get away with this.
}
}

//This function ensures that we do not load duplicates. This keeps our final data set clean and prevents looping.
function dontLoadDuplicates(relConcat0,parentName,childName,getDepartment,getclass,getEOLdate,businessServiceName,parentOpStatus,childOpStatus){
var dupeCheck = finalList.indexOf(relConcat0);
if(dupeCheck == -1){
finalList.push(relConcat0);
var length = finalList.length;
gs.log('For Business Service: $'+businessServiceName+'$Hop Tier: $'+counter+'$ Parent Op Status: '+parentOpStatus+'$ Child Op Status: '+childOpStatus+'$ For the Relationship name: $'+relConcat0+' $ For Department: $'+getDepartment+' $ For EOL: $'+getEOLdate+' $ '+getclass+' $ '+ident);
return true;
}
return false;
}
}

Hafsa1 · ‎06-20-2020

update is not happening

. Seems we are gliding cmdb_rel_ci but updating cmdb_ci table

gputker · ‎06-20-2020

Yeah I had it wrong. You have to GET the record sysID from the CI Relationship table and then Glide over to the CMDB_CI table and run the update there. Try this:

var ident = 'xvxvxvxvx01'; //unique identifier string to filter in LOGS.
gs.log(ident+' SCRIPT STARTED');
var getAllBusinessServices = new GlideRecord('cmdb_ci_service'); // scan Business Services CMDB class.
//getAllBusinessServices.setLimit(1); //use for safety when experimenting with script.
//getAllBusinessServices.addQuery('name','TEST Business Service'); //use for safety when experimenting with script.
getAllBusinessServices.addQuery('service_classification','Business Service'); // capture ALL business services.
getAllBusinessServices.query();
while (getAllBusinessServices.next()){

var parentCIValueYouWishToPushDownStream = getAllBusinessServices.getValue('name of parent business service field');

//please note that the value you extract from the parent business service must be compatible with the type of value in the field you set.
var businessServiceName = getAllBusinessServices.getDisplayValue('name');
var businessServiceSysID = getAllBusinessServices.getUniqueValue();
returnAllDownstreamCIRelationships(businessServiceName,businessServiceSysID); //call function to get first round all child downstream relationships.
}
gs.log(ident+' SCRIPT FINISHED');

function returnAllDownstreamCIRelationships(businessServiceName,businessServiceSysID){
var counter = 0;
var arrayForParents = [];
var finalList = [];
var ciRels0 = new GlideRecord('cmdb_rel_ci'); //scan CI relationships table seeking out PARENTS of all business services.
ciRels0.addQuery('parent',businessServiceSysID);
ciRels0.addQuery('parent.operational_status','!=','6');
ciRels0.addQuery('child.operational_status','!=','6');
ciRels0.query();
while(ciRels0.next()){ //for each parent found define all neede variables.
var childName0 = ciRels0.getDisplayValue('child');
var typeName0 = ciRels0.getDisplayValue('type');
var parentName0 = ciRels0.getDisplayValue('parent');
var childSysID0 = ciRels0.getValue('child');
var parentOpStatus0 = ciRels0.parent.operational_status.getDisplayValue();
var childOpStatus0 = ciRels0.child.operational_status.getDisplayValue();
var relConcat0 = parentName0+typeName0+childName0;
var getclass0 = ciRels0.child.sys_class_name;
var getDepartment0 = ciRels0.child.department.getDisplayValue();
var getEOLdate0 = ciRels0.child.u_end_of_life_month_year.getDisplayValue();
// call the dontLoadDuplicates function to ensure that duplicate relationships are STOPPED. This prevents LOOPING.
dontLoadDuplicates(relConcat0,parentName0,childName0,getDepartment0,getclass0,getEOLdate0,businessServiceName,parentOpStatus0,childOpStatus0);
//there is logic in the dontLoadDuplicates function that if a duplicate is found this while loop stops for the duplicate record.

var goUpdateChild = new GlideRecord('cmdb_ci');
goUpdateChild.get(childSysID0);
goUpdateChild.setValue('field you wish to update', parentCIValueYouWishToPushDownStream);
goUpdateChild.update();

//please note that the value you extract from the parent business service must be compatible with the type of value in the field you set.
arrayForParents.push(childSysID0); // the relationship is NOT a duplicate then loadit into an array.
}
getNextLayer(arrayForParents); //Call the next layer function.

function getNextLayer(theArray){ //here we simply repeat the process above however there is a hasNext query which STOPS the resturn of results if no parent records are found from child records.
var arrayForParents2 = [];
var ciRels1 = new GlideRecord('cmdb_rel_ci');
ciRels1.addQuery('parent','IN',theArray);
ciRels1.addQuery('parent.operational_status','!=','6');
ciRels1.addQuery('child.operational_status','!=','6');
ciRels1.query();
if(ciRels1.hasNext()){
counter ++;
while(ciRels1.next()){
var childName1 = ciRels1.getDisplayValue('child');
var typeName1 = ciRels1.getDisplayValue('type');
var parentName1 = ciRels1.getDisplayValue('parent');
var childSysID1 = ciRels1.getValue('child');
var parentOpStatus1 = ciRels1.parent.operational_status.getDisplayValue();
var childOpStatus1 = ciRels1.child.operational_status.getDisplayValue();
var relConcat1 = parentName1+typeName1+childName1;
var getclass1 = ciRels1.child.sys_class_name;
var getDepartment1 = ciRels1.child.department.getDisplayValue();
var getEOLdate1 = ciRels1.child.u_end_of_life_month_year.getDisplayValue();
var actionORnot = dontLoadDuplicates(relConcat1,parentName1,childName1,getDepartment1,getclass1,getEOLdate1,businessServiceName,parentOpStatus1,childOpStatus1);
if(actionORnot){
arrayForParents2.push(childSysID1);

var goUpdateChild2 = new GlideRecord('cmdb_ci');
goUpdateChild2.get(childSysID1);
goUpdateChild2.setValue('field you wish to update', parentCIValueYouWishToPushDownStream);
goUpdateChild2.update();

//please note that the value you extract from the parent business service must be compatible with the type of value in the field you set.
}
}
getNextLayer(arrayForParents2); //VERY IMPORTANT - recall the same function - deliberatly loop. Because of the hasNext we can safely get away with this.
}
}

//This function ensures that we do not load duplicates. This keeps our final data set clean and prevents looping.
function dontLoadDuplicates(relConcat0,parentName,childName,getDepartment,getclass,getEOLdate,businessServiceName,parentOpStatus,childOpStatus){
var dupeCheck = finalList.indexOf(relConcat0);
if(dupeCheck == -1){
finalList.push(relConcat0);
var length = finalList.length;
gs.log('For Business Service: $'+businessServiceName+'$Hop Tier: $'+counter+'$ Parent Op Status: '+parentOpStatus+'$ Child Op Status: '+childOpStatus+'$ For the Relationship name: $'+relConcat0+' $ For Department: $'+getDepartment+' $ For EOL: $'+getEOLdate+' $ '+getclass+' $ '+ident);
return true;
}
return false;
}
}

Hafsa1 · ‎06-24-2020

If I have to go downstream as well as upstream CI and get some values?

gputker · ‎06-24-2020

For going upstream I'd start very simple: go with the CIUtils script include and call the function 'servicesAffectedByCI'. Its very simple, fast, powerful and non-invasive (i.e it wont generate thousands of rows of log file data).

If you want to use the script I wrote above but reverse it then alter it so that instead of it calling the child relationships - make it call the parent relationships. Start with this line of code:

arrayForParents.push(childSysID0);

Instead of passing in the 'childSysID0' pass in the parent sysid. I would imagine you will only need to do this in a few places within the script to reverse the way it works. Have a try and let me know how you go.

GP.