IntegrationHub ETL - 1-M Relations to JSON Objects in Array [WorkAround]

[BenzChua31]

WORKAROUND: We have decided to use JSON Import instead of CSV Import. We had a goal to simplify the process for our clients, but CSV Import seems to be complicating it even more than JSON Import. Not worth the time and effort to implement data from CSV Imports. 

Context: I have an array of JSON objects under a field called "relationships". Each JSON object represents the CI/AWS Resource that the current CI record has relations with. 

Here is an example:

What I want to achieve:

Create CIs for each JSON objects in the array, then associate it with its respective CI. This is a CSV Import from AWS Config Advanced Query. 

Reason for using IE-ETL:

This was a client request to implement a temporary IE-ETL as an alternative discovery. If this is indeed a limitation, then it's time for a re-evaluation. 

What I have tried:

• I tried using Multi-Script Operation w/ Multiple Output, but I don't know how to make it work.
  
  Issue with this approach... I need to explicitly/manually create an output column and then map the data to the column. I have tried creating a column directly from script, but it doesn't seem to work. Using this approach practically requires you to assume a max number of relationships this CI can have which is not ideal. 
  
  I believe this is what ServiceNow wants me to do in the meantime? Since you can add multiple lookups for Network Adapters in the attribute/field mapping view. 
  
  
• I thought of creating the CI directly from the script.
  
  But... how am I supposed to establish the relationship? One is in a staging environment, the other in a production environment. It is also not wise to do this way, as there won't be rollback options. 

Utah, latest version. PDI, I can grant access for experimenting if needed. 

[BenzChua31]

Here is what I've found from the ETL Definitions for Service Graph Connector for JAMF and SCCM:

JAMF mappings are utilizing an array [*] supported by Robust Import Set Transformer (RIST), to access key values in an array. By the looks of it, IE-ETL may not have this feature on its view and may need to directly modify its RIST. 

As for SCCM, it does not use array [*], so I'm assuming that it is involving 3 different Import Sets. One for CI "A", one for CI "B", one for relationship between CI "A" and CI "B". This is a tedious process, but I guess that is an element of manual processes. 

[pratiksha5]

Hi Benz, I did not understand the user case. However, I can give a sample script for multiple input-output transforms. 

function(batch, output) {
for (var i = 0; i < batch.length; i++) {
var input = batch[i].input; // Value of the input columns concatenated with '|'.

var manufacturer = batch[i].u_manufacturer;
var modelId = batch[i].u_model;
var manufacturerID;

// Set each output element below
//output[i] = output_variable_name;
var manufacturerGr = new GlideRecord('core_company');
manufacturerGr.addQuery('name',manufacturer);
manufacturerGr.query();
if(manufacturerGr.next()){
manufacturerID=manufacturerGr.getUniqueValue();
}
else {
manufacturerGr.initialize();
manufacturerGr.setValue('name',manufacturer);
manufacturerGr.insert();
manufacturerID=manufacturerGr.getUniqueValue();
}
var currentModel = new GlideRecord('cmdb_hardware_product_model');
currentModel.addQuery('name',modelId);
if(currentModel.next()){
//currentModel.setValue("maftfacturer",getMft);
output[i]= currentModel.getUniqueValue();
}
else{
currentModel.initialize();
//currentModel.setValue('model_number',modelId);
currentModel.setValue('name',modelId);
currentModel.setValue("manufacturer",manufacturerID);
currentModel.setValue("cmdb_model_category",'81feb9c137101000deeabfc8bcbe5dc4');
currentModel.insert();
output[i]=currentModel.getUniqueValue();
}
}

})(batch,output);

Let me know if it helps. 

[BenzChua31]

Given a JSON like this:

{
        "accountId": 962200000000,
        "awsRegion": "ap-southeast-2",
        "configuration.ebsOptimized": "FALSE",
        "configuration.imageId": "ami-0e5fa71b70000000",
        "configuration.blockDeviceMappings": [
          {
            "ebs": {
              "volumeId": "vol-011a2b57000000000",
              "deleteOnTermination": true,
              "attachTime": "2022-09-21T00:27:01.000Z",
              "status": "attached"
            },
            "deviceName": "/dev/sda"
          },
          {
            "ebs": {
              "volumeId": "vol-0cb18ad4ca3000000",
              "deleteOnTermination": true,
              "attachTime": "2022-09-21T00:27:01.000Z",
              "status": "attached"
            },
            "deviceName": "/dev/sdb"
          }
        ]
}

             
How would you extract the child JSON objects in the "configuration.blockDeviceMappings"? 
JSON objects within an array, and this array is stored in a variable/column. 

Creating the CIs directly via the Transform Script would not be the right way to do it... 

[pratiksha5 · Accepted solution]

Hi Benz,

What is the source of this data? 

Also try this :

https://docs.servicenow.com/en-US/bundle/sandiego-platform-administration/page/administer/import-set...