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.

[Ravi Gupta1]

Hi @Joe B2,

I am using the similar script as you mentioned n onStart script. But it's running for every row.

I checked the count of query. Initially it's 1 and keeps increasing..

Means for every records it's gliding the the table.

First time it shows 1 record then 2 records and so on.

Is there any way we can update the newly created u_target_record column at once.

Is there any way we can directly update the source field like:

source.u_company_sys_id = 'sys_id goes here';

Below is my code written on OnStart script:

var grSource = new GlideRecord('u_workday_suppliers_staging');

grSource.addEncodedQuery('sys_import_set=' + import_set.sys_id.toString());

grSource.query();

log.info('Ravi 23 jan import_set getRowCount: ' + grSource.getRowCount());

while(grSource.next())

{

var company_sysID = '';

//var supplierFound ='0';

var gr = new GlideRecord('core_company');

gr.addQuery('u_active', true);

gr.addQuery('nameLIKE' + grSource.u_name);

gr.addQuery('u_supplier_id', grSource.u_supplier_id);

gr.query();

    log.info('Ravi 19Jan source.u_name: ' + grSource.u_name + ' ' + 'source.u_supplier_id :' + grSource.u_supplier_id);

//while(gr.next())

if (gr.next()) {

    var supplierName = gr.name;

    supplierName = supplierName.substring(0, supplierName.lastIndexOf(" (")); // remove supplier id if available in name.

    //compare supplier name of core_company table with source supplier name

    if (gr.name == grSource.u_name) {

        company_sysID = gr.sys_id; //if a match exists, return the sys_id of the matching company record

    }

    //compare supplier name (after removing the supplier id from name) of core_company with source supplier name

    else if (supplierName == grSource.u_name) {

        company_sysID = gr.sys_id; //if a match exists, return the sys_id of the matching company record

    }

    log.info('Ravi 19Jan company_sysID: ' + company_sysID + ' ' + 'supplierName :' + supplierName);

    grSource.u_company_sys_id = company_sysID;

    grSource.update();

}

}

Waiting for your input.

Thanks

Ravi

[Joe B2]

I can't spot anything inherently wrong here. Unsure how much data you have on the core_compnay table but I would suspect it this lookup that is doing it. I would:

1. If you haven't already, verify that it is certainly this script causing the slow down
2. Modify the log line after the gr.query() to return the found row count and encoded query. Use this query from the log in the manual table to see how many results are returned. If there are too many see if the query can be refined some more as it could be fetching many more records each time than expected

log.info('Ravi 19Jan source.u_name: ' + grSource.u_name + ' ' + 'source.u_supplier_id :' + grSource.u_supplier_id + '\nQuery Results: ' + gr.getRowCount() + '\nQuery: ' + gr.getEncodedQuery());

[Narsing1 · Accepted solution]

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

[Community Alums]

Hi @Narsing1 ,

Thanks for your reply!

I want to check source mac address first in Primary MAC column  and if not found then want to check Secondary MAC Column in target table, If found then update else insert

[Narsing1]

You can modify the field map script like this.  Change the field names according to your requirement on the encoded query.

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("primary_mac_address=" + s + "^ORprimary_mac_address=" + pattern1 + "^ORprimary_mac_address=" + pattern2 + "^ORprimary_mac_address=" + pattern3);
	grc.query();
	if (grc.hasNext()) {
		grc.next();
		id = grc.getUniqueValue();
	} else {
		grc.initialize();
		grc.addEncodedQuery("secondary_mac_address=" + s + "^ORsecondary_mac_address=" + pattern1 + "^ORsecondary_mac_address=" + pattern2 + "^ORsecondary_mac_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);

Thanks,

Narsing