IntegrationHub ETL Nested Ojbects

[Ned F]

Hello!

I've been tasked with installing and configuring the Service Graph Connector for Google Console to maintain our ChromeOS devices with our CMDB. I got the baseline installation of the plugin to work and data is flowing. The end user requested the customization of including CPU and RAM details. The RAM part was easy since the systemRamTotal was already in the top level. I simply added a transform to convert the value to MBs and updated the mapping.

The CPU details is proving to be a little more challenging as it has Nested Objects. It's an array that seems to have a record per CPU core. I'm primarily trying to access the model (cpuInfo[0].model) record. That data point seems to be the same per core, so I only need one of the values.

I've attempted to update the mapping to have the model added, but quickly discovered that doesn't work between levels.

I've also attempted to update the Robust Transform Engine to see if I could have the model added to the top level with no luck. I don't see the JSON record on the top level record either. I do see it in a u_data column in the staging table.

Is anyone familiar with working with nested objects in a Service Graph Connector that can help, point me at a good reference, or recommend something from ServiceNow University that can help me?

Thank you!

[Vijaya_Mnpram]

@Ned F  Before you do the mapping, how about creating a variable in Step 2 of the IntegrationHub ETL and load the value of CPUInfo.Model into that. In the Step3 of the IntegrationHub ETL, you can map that variable to the CPU field. 

Just like any other variable, which are modified before mapping. Also, here is the video from Servicenow Academy related to nested objects.

https://youtu.be/jonkMkFiNaQ?si=A9oZiMfF6E7EEYOk

[Ned F]

@Vijaya_Mnpram 

Thanks for your response! I watched the video, but I'm still at a lose. I'm not sure where I'd be able to create the variable in Step 2 (Prepare Source Data for Mapping) like you mentioned, would you be able to elaborate on that?

I did attempt to use a table lookup to the staging table, but couldn't find field to match records. I ran into a similar issue when using the mapping. I added another computer class and associated the model there, but I couldn't pull in pills on that level that would match back to the top level for the relationship. I have to be missing something, but I don't know what yet.

[Vijaya_Mnpram · Accepted solution]

@Ned F When you can create a transform map, you get an option 'Output Column Name'. That will be the variable you use in further steps. (You click on 3 lines right to the variable name and click 'Create Transform').

You can use a 'Script Operation' Transform type to glide in another table and script as you wish. A sample script below

(function(batch, output) {
for (var i = 0; i < batch.length; i++) {
var input = batch[i].input;
output[i] = new sn_sg_azure_integ.SGAzureCommonsUtil().getInstallStatus(input);
}
})(batch, output); 

[Ned F]

@Vijaya_Mnpram, thank you!

I was able to create a New Transform from the cleanse_serialnumber and use that to find a matching record and extract the CPU model information. From there I was able to edit the mapping and map the new column easily.

(function(batch, output) {
   for (var i = 0; i < batch.length; i++) {
      var input = batch[i].input;
      
      var deviceGr = new GlideRecord('{your_staging_table}');
      deviceGr.addQuery('u_data', 'CONTAINS', input);
      deviceGr.setLimit(1);
      deviceGr.query();
      
      if (deviceGr.next()) {
         input = JSON.parse(deviceGr.u_data);
      }
      
      output[i] = input.cpuInfo[0].model;
   }
})(batch, output);