Need assistance with Transform Map

Community Alums · ‎01-18-2024

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 · ‎01-19-2024

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:

Create a new column on your import table called u_target_record (or similar). This is a basic string field.
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
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.

View solution in original post

Narsing1 · ‎01-19-2024

In these cases you need to use dynamic coalesce like this

Field Map Script (if your target table is computer and target field is mac_address)

answer = (function transformEntry(source) {

    // Add your code here
    var s = source.mac_address.toString();
    var b = s.match(/.{1,2}/g) || [];
    var pattern1 = b.join("-");
    var pattern2 = b.join(".");
    var pattern3 = b.join(":");

    var id = -1;
    var grc = new GlideRecord("cmdb_ci_computer");
	grc.addEncodedQuery("mac_address=" + s + "^ORmac_address=" + pattern1 + "^ORmac_address=" + pattern2 + "^ORmac_address=" + pattern3);
	grc.query();
	if (grc.hasNext()) {
		grc.next();
		id = grc.getUniqueValue();
	}
    return id; // return the value to be put into the target field

})(source);

If the return value is -1, it is going to consider as insert, otherwise update.

Thanks,

Narsing

View solution in original post

Community Alums · ‎01-19-2024

Hi @Joe B2 ,

Thanks for you reply, Let me try!

Community Alums · ‎01-19-2024

Hi @Joe B2 , @Ravi Gupta1

I tried above suggestion and created one new field in source table and make coealese true to that field, and write one onStart script. It is working fine.

My query is I want to Skipped record update in target when Hardware status of target record is "Install". How I achieve this. Thanks!

Joe B2 · ‎01-21-2024

Hi Amit,

I see Narsing has provided an alternate solution below for a dynamic coalesce. The other would be to modify the onStart script you created to create the blank cmdb record at the time and then feed that sys_id back into the field.

That said I really like Narsing's solution which combines the dynamic lookup with the new sys_id matching field instead of requiring an onStart script.

Community Alums · ‎01-22-2024

Hi @Joe B2 ,

this approach is working fine but took 2 to 3 hours to transform 4 records, because in target we have approx 13lack records, is there any alternate approach, Thanks!

Joe B2 · ‎01-23-2024

Is this using the method I suggested or one of the other ones in this thread? I'm sorry to say I haven't experienced that however the onStart script I suggest would take place before the actual transform of any rows happens. I've used this method to match on a table with 85k+ asset records without issue and it runs nice and quickly.

If the records are matching with the onStart script I suggested and the records start to transform then the issue is most likely in one of the onBefore scripts.