Help with Bulk Updating Most Recent Discovery Field Using Catalog Item and Excel template

[Community Alums]

Hi all,

I am trying to bulk update the Most Recent Discovery field on the CMDB CI table using a Catalog Item with an attached Excel file. The Excel file contains three fields:

 IP Address

MAC Address

Most Recent Discovery

Requirement:

I want to update the Most Recent Discovery field when either the Primary IP Address or MAC Address from the Excel file matches the corresponding field in the CMDB CI table. If either one matches, the update should occur.

Current Approach:

1. I am using Conditional Coalesce on the sys_id field in transform Map. 

2. I created a field map where the target field is sys_id and the source is a source script.

3. In the source script, I am using a GlideRecord query on the CMDB CI table to check for a match:

MAC Address == source.MAC Address OR

IP Address == source.IP Address

4. If a match is found, the sys_id is returned, and the Most Recent Discovery field is updated. (Coalesce True on SysID field) 

Issue:

This approach works fine when there is a unique match. However, when the same IP Address is associated with multiple CIs, only the first matched record gets updated. This is because:

The field mapping is only for sys_id, which is unique.

If multiple matches are found, either only the first record gets updated or no record gets updated (due to ambiguity).

FYR I have used the same kind of source Script PFB, modified as per mine condition. 

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0860511 

Question:

How can I update the Most Recent Discovery field for all matching records when multiple CIs have the same IP Address or MAC Address?

I appreciate any guidance on how to handle this scenario.

Thank you!

[Ankur Bawiskar · Accepted solution]

@Community Alums 

suggestions

1) don't use field map and don't use coalesce

2) handle completely with onBefore transform script which gives you the liberty to sort by ascending or descending i.e. most recently field

3) use normal GlideRecord, query and if query finds record, go ahead and update

4) if not then create new one

5) ensure you set ignore = true so that it doesn't create a record via import set

something like this

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	// Add your code here
	// query
	// if found then update
	// if not found then insert

	ignore = true; // use this line as last line

})(source, map, log, target);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Community Alums]

Hi @Ankur Bawiskar , Thank you for suggestion! I dont want to create new records just want to update if found, let me implement it, I will post the result here. 

[Ankur Bawiskar]

@Community Alums 

okay in that case ignore=true if not found so that it doesn't create.

the logic will work fine as per what I shared

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Community Alums]

Hi @Ankur Bawiskar , I have implemented the suggestion you provided, and it is working as expected. However, the transformation process is taking too much time. Are there any limits on the number of records in the transform map? Thank you!