Using arrays in Transform Map Scripts

[shris]

Hi.

I am working on a CMDB import of software installations. I have one already for computers, and another for software packages, and the cmdb_software_instance table ties them together.

I have a custom field on cmdb_ci_computer for the AD SID of the machine. I have another custom field on software packages for the ID number of the software title. So my import of instances includes the AD SID of the machine and the ID number of the package.

The issue that I have is that the AD SID of the machine is on cmdb_ci_computer instead of cmdb_ci. So using the "Referenced value field name" of the AD SID doesn't work, because it's in the wrong table.

I could make an onBefore script that looks up the AD SID in the computer table and sets the target value. But I have 80,000+ installation records and that number is growing every day. 80K lookups of any table is going to slow down the import in a really big way.

So the question is this: Can I use an onStart script to build arrays of all the computers and their AD SIDs, and then refer to the arrays in the onBefore script so I'm just checking the arrays instead of querying the table over and over? Will an array built in an onStart script be available to the onBefore script, or is it lost when the onStart is completed?

[ShaneBrazeal]

I'd recommend using a source script on your field map instead of building an array in a transform map script.

I've attached a screenshot and your script should look something like the following:

var cmdbRec = new GlideRecord('cmdb_ci_computer');
cmdbRec.addQuery('u_ad_sid', source.u_ad_sid);
cmdbRec.query();
if(cmdbRec.next()){
  answer = cmdbRec.sys_id;
}
else{
  gs.log("Error - AD SID not found: " + source.u_ad_sid);
  error = true;
}

[shris]

That will certainly work, but won't it result in 80,000 glideRecord queries being run for one import? Is that really the *best* way? I would think it would be slow..

[shris]

Yeah, I tried this apprach and it works, but the transform takes something like 6 hours for 71K records. Bleh. It would be far better to find a faster way..

I don't mean to seem ungrateful, but our volume really needs the speed..

[Dominik Simunek]

Yes, if you define global variable in onStart script, you can access it in onBefore script. I used it several times when I imported data choice labels and wanted to validate that particular choice label exists in the choice table, storing choice value at the end in the target record - I built arrays of choice label choice value mappings just once and used them in either onBefore scripts / source scripts or any other transform script.

Note: in latest releases (I guess since Geneva) there is template with function generated in onStart script which means that variables defined in it are not global and so not available to other scripts. I am not sure if there is better support for making variables from onStart available to onBefore and other transform map scripts than global variables.