Need assistance with Transform Map

[Community Alums]

Hi All,

I have a transform map where the MAC address field is currently set with `coalesce=true`. The source MAC address always comes in a normalized format (e.g., 123abcde6f97). In the target, I have MAC addresses in various formats:

- 11-7A-BC-DE-EF-E3

- 00.17.23.a4.73.79

- u0:18:98:4e:01:63

- 123ABCDC6F97

Before the transformation, I want to:

1. Normalize the MAC addresses in script for comparison purpose only (Primary and Secondary both) in the target to the same format as the source (e.g., 123abcde6f97).

2. Compare the normalized source MAC address first with the primary MAC address in the target.

3. If no match is found, check against the secondary MAC address in the target.

4. If a match is found, update the existing configuration item (CI). If not, create a new CI.

I'm unsure where to place the script for normalizing the target MAC addresses and conducting this comparison – whether in the field script or onBefore script. Could you please advise on the best practice and how to structure the script for this scenario?

Thanks!

[Joe B2 · Accepted solution]

I've done something similar in the past (slightly different example, in mine it's because I wanted to do secondary and tertiary coalesces if the first value wasn't found).

Approach may be slightly convoluted but it worked:

1. Create a new column on your import table called u_target_record (or similar). This is a basic string field.
2. Create an onStart script for the transform map. This script will loop through all records in the current import set and attempt to match to a record in the target table. In your case you can do multiple searches on the MAC address (possibly easier with a regex query) to try and find a target record. If one is found populate the sys_id of the record into the newly created u_target_record column
3. Set your transform map coalesce on the u_target_record field to match against sys_id. As you've already done the work to match you know this will be accurate.

Script below on how to lookup only records related to the current import set on your import table.

var grUpdates = new GlideRecord('IMPORT TABLE NAME HERE');
grUpdates.addEncodedQuery('sys_import_set=' + import_set.sys_id.toString());
grUpdates.query();

Sorry if I've grabbed the wrong end of the stick but hopefully this helps.

[Community Alums]

Hi @Narsing1 ,

Thanks, Let me check with this!

[Narsing1]

@Community Alums - is the solution working? If so, accept the solution so that this thread can be closed.

Thanks,

Narsing

[Community Alums]

Hi @Narsing1 ,

dynamic Coalease not working, it is creating duplicates, it is not updating existing records, I am using like below, Can you please help!

Field script (sys_id)

Field Source script(sys_id): I checked script that you provide but it is creating duplicates so tried below one still it is creating duplicates, not updating the existing records

reference Link: Conditional coalesce script for sys_id creates record with sys_id of "-1" - Support and Troubleshoot...

FYI: All transform scripts are active FALSE

[Narsing1]

Hi @Community Alums 

Normally when you use dynamic Coalesce, on onBefore script you don't need to depend on the IRE Engine to take care of insert/update because of its nature.  You can use "ignore=true" always if it is trying to insert/update and write your own code.

Based on the screen shot, I could see 5 transform scripts available. Not sure what it is doing, but you can do one field map script for getting the sysid and one onBefore script to handle the complete transform.

Please check the below link where it uses CMDBTransformUtils class to handle.  However, this will also go through IRE Engine and update, but you need to make sure you are always ignoring the record.

CMDBTransformUtil Usage in Transform Maps 

(Or)

You can write a simple GlideRecord query to update/insert 

Thanks,

Narsing