Coalesce rules on multiple fields import transform map

[Katie A]

Hello,

We are in the process of setting up an integration with SCCM to import CI records.

We have decided not to use the out of box plugin, since it pulls in more information than we need also there are known issues as described in this post https://community.servicenow.com/thread/151244

I set up everything the same way by connecting via JDBC data source with SQL queries and transformation maps.

My question is regarding the Coalesce rules.

SCCM has a field called "ResourceID" which I am using to populate the "CorrelationID". That is set as the Coalesce.

The issue with the ResourceID in SCCM is that a new one is created each time a computer is re-imaged.

That is going to cause duplicate records when a computer is re-imaged.

I was thinking that we could use a combination of the Resource ID and Computer Name to resolve this issue. We wanted to use serial number but we have found some cases where the serial is missing or not correct.

How can I set that up in a transformation map?

Here is the outline:

• If a matching ResourceID is found, update that record
• If a matching ResourceID is NOT found, check for a matching computer name
• If neither matches are found, create a new record

[Jamsta1912 · Accepted solution]

Hello KDA,

Set up your field maps to coalesce just on ResourceID to CorrelationID, and then add a script like this on your table transform map (in the script field that appears when you tick the 'Run Script' check box):

if (action == 'insert'){ //So, this will run only if there is NO match on ResourceID.


   var sourceCompName = source.u_computer_name;


   var ci = new GlideRecord('cmdb_ci');
   ci.addQuery('u_computername', sourceCompName);
   ci.query(); //Look for an existing ci record with matching computer name.

   if (ci.next()){ //if match is found, update the fields on that record.
   ci.u_targetField1 = source.u_sourceField1;
   ci.u_targetField2 = source.u_sourceField2; //These are 'dummy' field names 
   ci.u_targetField3 = source.u_sourceField3;
   ci.update(); //save the updates
   ignore = true; //skip the updates defined in the map
   }

   //If there is NOT a match, a new record will automatically be inserted.

}

[Katie A]

Thanks Jamie! Works!

We have decided to use the Serial Number as a secondary match instead of computer name. The code template you provided works for this purpose.

Thank you

[alliej428]

Hi-

I have a very similar question to this, but I don't quite understand how to make it work.   We too use the SCCM plugin (2012 v2), and we'd like the system to first look at the Resource ID/Correlation ID, then at the serial number to update the record.   If it doesn't find these, then it would do an insert.   The script mentioned above- would that go into the Computer Identity transform map?   I want to make sure I'm putting it in the correct place.   Also, right now the coalesce is set to false for the correlation ID, but the records seem to be updating ok on this.   Does this need to be changed to true?

Thanks!

--------------------------------------------

[ryadavalli]

Hi jamie,

very helpful article. I have 2 questions:

1) When the record is not found, the system inserts a new one, sometimes the insert fails as their could be a unique criteria. In that case how can we check based on action

2) In the code, you mentioned if the secondary match is found, we set manually each field update, is there any way we can go update all the changes like delta, as we might not know fields must have changed?

Thanks,