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.

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); } } 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;}

}

Labels: