Updating child field value based on parent change

[Leonard Brown]

Hi All,

I'm working on a script for Database records stored in the CMDB. In the CMDB we have two tables I'm focusing on both: cmdb_ci_db_mssql_instance(Parent) and cmdb_ci_db_mssql_database(child)

This parent/child relationship is set on the cmdb_rel_ci table with the relationship type:  Contains::Contained by

Example - SQL65@tx102wptsql65(Parent) | Contains::Contained by | model@tx102wptsql65\SQL65.

So for some of you that may not know how this is set whatever the Database instance name value is the child database will include that in its name after the @ symbol just in a different format. For this example SQL65@tx102wptsql65 can be seen as "instancename@server"  while model@tx102wptsql65\SQL65 could be seen as database@server\instancename. Note: Notice how the format for server/instance is different for both the parent and child records but still includes the same information. 

Now with that being said I have created an "Connection name(u_db_name)" field that is to match the original name field on insert. This was created so in the event the dba wants to associate an Connection name for this record they could put that information in that field leaving the original name alone and only edited by Discovery. What I would like to accomplish is if anyone changes the Connection name for the Database Instance(parent) then all Databases(Children) alias name field will update reflecting the new instance values. See what I have so far below:

BR

- After

-Insert/Update

- Condition: Connection name changes

(function executeRule(current, previous /*null when async*/ ) {

var contains = '55c95bf6c0a8010e0118ec7056ebc54d'; //Set the relationship type Contains:ContainedBy to variable
var parentConnectName = current.u_db_name; //Set current records Connection Name to a variable

// ----------------Create the NAME---------------
parentConnectName = parentConnectName.toString();
var hostName = parentConnectName.substring(0, parentConnectName.lastIndexOf("@")); //Parse out host name
var instanceName = parentConnectName.substring(parentConnectName.lastIndexOf("@") + 1); //Parse out instance name
var fullName = '@' + instanceName + '\\' + hostName; //This will put the name information in the correct order needed for the child record format.

var dbRel = new GlideRecord('cmdb_rel_ci');
dbRel.addQuery('parent', current.sys_id);
dbRel.addQuery('type', contains);
dbRel.query();

while (dbRel.next()) {

var childDatabase = dbRel.child.u_db_name; //Gets all of the child databases from the relationship table
var dbname = dbRel.child.database; // Get Database field value so using the example above this would be "model"
var newdbName= dbname + fullName; //This will now add "Model" +  "tx102wptsql65\SQL65" and set in variable
dbRel.child.u_db_name = newdbName; //Set the connection field value to the full name
dbRel.update();

}

})(current, previous);

It appeared at first that this was all setup properly but then I noticed the field was not updating for all child records when the connection name was updated for the parent. I then noticed that the system logs would provide the information I needed for each record just was not setting it to the field on their table. So after investigating I found that the field would not update because first off the business rule is set for this table cmdb_ci_db_mssql_instance and the glide record in the script is for cmdb_rel_ci leaving out any additional query for the child table cmdb_ci_db_mssql_database that contains those records. I'm now stuck and unsure how to add to the script to accomplish what I need or possibly rewriting the script in a different way to accomplish the same. Does anyone have any idea on how to accomplish this task whether I add onto to this script or just rewrite a new one? I'm not advanced at scripting so I still have much to learn and if possible please provide comments for any script lines recommended for better understanding of whats going on.

Thanks,

[Brad Bowman · Accepted solution]

Hi Leonard,

You've got a good start on this.  Where it goes sideways is during the relationship query.  You can retrieve the child records from this, but to update those child CI records you need to add a query containing the CIs, not update the relationship record.  Assuming this is working down to dbRel query returning the correct records, your script would look more like this

(function executeRule(current, previous /*null when async*/ ) {

    var contains = '55c95bf6c0a8010e0118ec7056ebc54d'; //Set the relationship type Contains:ContainedBy to variable
    var parentConnectName = current.u_db_name.toString(); //Set current records Connection Name to a variable

    // ----------------Create the NAME---------------
    var hostName = parentConnectName.substring(0, parentConnectName.lastIndexOf("@")); //Parse out host name
    var instanceName = parentConnectName.substring(parentConnectName.lastIndexOf("@") + 1); //Parse out instance name
    var fullName = '@' + instanceName + '\\' + hostName; //This will put the name information in the correct order needed for the child record format.
	var childCis = []; 
    var dbRel = new GlideRecord('cmdb_rel_ci');
    dbRel.addQuery('parent', current.sys_id);
    dbRel.addQuery('type', contains);
    dbRel.query();
    while (dbRel.next()) {
		childCis.push(dbRel.child.toString());//create an array of all of the child databases (sysids) from the relationship table
	}
	
	var childGR = new GlideRecord('cmdb_ci_db_mssql_database');
	childGR.addQuery('sys_id', 'IN', childCis.join(','));
	childGR.query();
	while(childGR.next()){
		childGR.u_db_name = childGR.database + fullName; //Set the connection field value to the full name
        childGR.update();
	}
})(current, previous);

[Leonard Brown]

Hi Brad, 

Thank you this worked out great and taught me the value of using push and join. I owe you a virtual drink 🙂 

Thanks,

Leonard