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.

[sourav1999]

Here are the steps to achieve your requirement:

1. Create a new Transform Script in your Transform Map. This script will be executed before the transformation process. So, select the type as "onBefore".

2. In the script, you can access the source and target fields using the source and target objects respectively.

3. Normalize the target MAC addresses. You can use JavaScript's replace() function to remove all non-alphanumeric characters and convert the string to lowercase.

4. Compare the normalized source MAC address with the primary and secondary MAC addresses in the target.

5. If a match is found, update the existing CI. If not, create a new CI.

Here is a sample script:

javascript
(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
// Normalize the target MAC addresses
var primaryMac = target.u_primary_mac.replace(/[^a-z0-9]/gi, '').toLowerCase();
var secondaryMac = target.u_secondary_mac.replace(/[^a-z0-9]/gi, '').toLowerCase();

// Compare the source MAC address with the target MAC addresses
if (source.u_mac == primaryMac || source.u_mac == secondaryMac) {
// If a match is found, update the existing CI
target.update();
} else {
// If no match is found, create a new CI
target.insert();
}
})(source, map, log, target);

Please replace u_primary_mac, u_secondary_mac, and u_mac with your actual field names.

For asking ServiceNow-related questions try this :
For a better and more optimistic result, please visit this website. It uses a Chat Generative Pre-Trained Transformer ( GPT ) technology for solving ServiceNow-related issues.
Link - nowgpt.ai

[Mark Manders]

Don't you have another field to coalesce on? First: your values aren't the same, so you are coalescing on a value that you first need to validate (although I would normalize all existing fields, so you could do this, since your new source is bringing it in like that). Second: you want to validate your coalesce on two fields? 

You will have to script this in an onBefore script, but I'm not sure on how te ensure this is going correctly on every import. Maybe do the entire transform from that script?

[Community Alums]

Hi @Mark Manders,

Thank you for your prompt response. I currently have only one field designated for coalesce, and I don't have an additional field for this purpose.

would you recommend handling the entire transform within the onBefore script?

Your insights are greatly appreciated.

[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.