Issue to set reference fields on CI Relationship table via Kafka Script Consumer/ETL definitions

VarunP947598761
Tera Contributor

‎09-11-2024 12:26 PM

Hi all,

 

We are facing an issue while setting reference fields "parent" and "child" on "CI Relationship" (cmdb_rel_ci) table via Kafka script consumer/ETL definitions approach.

We have an input JSON, which looks like below:

 

{
"action": "CREATE",
"entity": {
"type": "Relationship",
"startexternalsystemid": "EXT1",
"reltype": "Connects to:: Connected by",
"endexternalsystemid": "EXT2"
}
};

 

We have created ETL definition to consume this JSON and created ETL entities corresponding to Import Set (corresponding to the input data that has been loaded into staging table), Temporary entity and actual target table to be loaded, in this case CI Relationship (cmdb_rel_ci).

 

We have also created additional entity fields on Temp entity to correspond to parent and child which are set via RTE Entity Operations

 

Below screenshot provides 4 ETL Entity fields created for Temp Entity - 2 to capture value coming in input JSON and 2 for the transformed sys_id value to be stored in  CI Relationship table record.

VarunP947598761_0-1726081453870.png

 

We are using Glide Lookup operation to search cmdb_ci table for correlation_id = <<Value in Temp Start External Id>> and picking up name field from matching CI record.

VarunP947598761_1-1726081688578.png

We observe that when we use this, for the corresponding CI record fetched via correlation_id, if there are more than 1 CI records found with the same name like below -

NameCorrelation_id
8EXT1
8EXT3
8EXT4
8EXT5
8EXT6

 

SN will randomly pick up any record and not particularly the one with Correlation_id = EXT1 and eventually reference it in "parent" field in CI relationship record to be created.

 

If instead, we use Glide Target Fields as sys_id, SN will consider the sys_id of CI record with Correlation_id = EXT3 and create a new CI record with name = <<Sys_Id of CI record fetched with Correlation_id = EXT3>>. This new CI record will be referenced by parent field in CI Relationship new record created.

 

Hence, we are unable to set the appropriate CI record (in this case CI with Correlation_id = EXT1) as parent in CI relationship created by ETL definition. Same holds good for child field as well.

 

Anyone else faced the same issue? Any help would be welcome.

 

Thanks

0 Helpfuls
  • 581 Views
3 REPLIES 3

Mani A
Tera Guru

‎09-12-2024 01:20 AM - edited ‎09-13-2024 08:10 PM

A2 **Use Glide Lookup Operation:**
-  to map `correlation_id` to the sys_id of the CI records.

Example:
// Start CI
var ciGr = new GlideRecord('cmdb_ci');
ciGr.addQuery('correlation_id', tempEntity.startexternalsystemid); 
ciGr.query();
if (ciGr.next()) {
// Set the sys_id in Temp Entity for parent
tempEntity.parent_sys_id = ciGr.sys_id;
} 

 

//End CI
ciGr.initialize();
ciGr.addQuery('correlation_id', tempEntity.endexternalsystemid); // Ensure the exact match
ciGr.query();
if (ciGr.next()) {
// Set the sys_id in Temp Entity for child
tempEntity.child_sys_id = ciGr.sys_id

 

B4 *Update the Final Transformation Logic*

Example of transforming Temp Entity to CI Relationship.


var relGr = new GlideRecord('cmdb_rel_ci');
relGr.initialize();
relGr.setValue('parent', tempEntity.parent_sys_id); // Set parent CI
relGr.setValue('child', tempEntity.child_sys_id); // Set child CI
relGr.setValue('type', tempEntity.reltype);
relGr.setValue('start_external_system_id', tempEntity.startexternalsystemid);
relGr.setValue('end_external_system_id', tempEntity.endexternalsystemid);
relGr.insert();

0 Helpfuls

VarunP947598761
Tera Contributor
In response to Mani A

‎09-13-2024 12:28 PM

Hi @Mani A  : Thank you for your response.

 

Regarding you step A2 (Use Glide Lookup Operation:), in the screenshot I have provided in the question, I have used RTE Glide Lookup Operation and not the script that you mentioned. Do you propose to create a script instead here?

Also, when I use sys_id as glide target fields, SN considers the sys_id of CI record with Correlation_id = EXT3 and creates a new CI record with name = <<Sys_Id of CI record fetched with Correlation_id = EXT3>>. This new CI record will be referenced by parent field in CI Relationship new record created.  (As I mentioned in initial post).

 

And, for B4 (Update the Final Transformation Logic), ETL definitions, provide a mechanism to configure RTE Entity mappings from "Import Set to Temp" (Order: 100) and "Temp to CI Relation" (Order: 200). In the RTE Entity mapping for "Temp to CI Relation", we have setup RTE Field Mapping from "Temp Parent" to "Parent". Not sure where do you recommend to add the script that you have mentioned.

 

Could you please help?

0 Helpfuls