Custom Connector - Parse a json object

jlazerus · June 2018

Hi,

I'll start this by saying that I don't know javascript at all, but I really want to figure out how to create a custom connector. So I was messing around and was able to get some data using the following partial script. What I want to do is to set the "resources" object of the response as a variable so that I can then append it to another URL to call which should finish this script.

Here's what I have so far:

httprequest.addHeader('Authorization', 'Basic ' + DOMO.b64EncodeUnicode(metadata.account.username + ':' + metadata.account.password));

var res = httprequest.get('https://URLREDACTED.com/devices/queries/devices/v1?filter=last_seen:>'2018-01-13'&amp;limit=4000');

DOMO.log('res: ' + res);

if(res.indexOf('Account.Name') > 0){auth.authenticationSuccess();}
else{auth.authenticationFailed('Your username and password are incorrect');}

This returns json that looks like this:

res: {"meta":{"query_time":0.132349044,"pagination":{"offset":3719,"limit":4000,"total":3719},"powered_by":"device-api","trace_id":"e1f320ef-b17e-4b20-8155-1e762e1fd250"},"resources":["24ee11d64f3347894277a798b9a15958","21139027abb043115e696961ac512a28","77c2a8a4abcc4cbb6dd57e29e8c18622","74fa4890ddaa42c85ff139edd2df36a8","84b89dead05f41607f1735ec2d99172c"],"errors":[]}

I then want to take the "resources" from above and append it to the URL here:

def url = ('https://URLREDACTED.com/devices/queries/devices/v1?ids=' + VAR

var devices = httprequest.get(url); datagrid.magicParseJSON(devices);

Can anyone help a noob out?

jlazerus · July 2018

So I actually figured out what I was trying to do here, but it led me to two follow up questions.

Is it possible through the custom connector to pull more than 2000 rows of data? (I get an error if I try)
Can someone assist me in pulling nested json objects? See the sample of what I'm trying to get below. I noted on the right which ones I'm having trouble with.

Code:

  /* Parse JSON and Create DataGrid */
  var parsedDevices = datagrid.magicParseJSON(res);

  var data = JSON.parse(res).resources;
  DOMO.log(data)
  /* Add data to cells */
  for(var i = 0; i < data.length; i++){

        var deviceDetails = data[i];

        datagrid.addCell(deviceDetails.meta_query_time);
        datagrid.addCell(deviceDetails.meta_powered_by);
        datagrid.addCell(deviceDetails.meta_trace_id);
        datagrid.addCell(deviceDetails.device_id);
        datagrid.addCell(deviceDetails.cid);
        datagrid.addCell(deviceDetails.agent_load_flags);
        datagrid.addCell(deviceDetails.agent_local_time);
        datagrid.addCell(deviceDetails.agent_version);
        datagrid.addCell(deviceDetails.bios_manufacturer);
        datagrid.addCell(deviceDetails.bios_version);
        datagrid.addCell(deviceDetails.config_id_base);
        datagrid.addCell(deviceDetails.config_id_build);
        datagrid.addCell(deviceDetails.config_id_platform);
        datagrid.addCell(deviceDetails.external_ip);
        datagrid.addCell(deviceDetails.mac_address);
        datagrid.addCell(deviceDetails.hostname);
        datagrid.addCell(deviceDetails.first_seen);
        datagrid.addCell(deviceDetails.last_seen);
        datagrid.addCell(deviceDetails.local_ip);
        datagrid.addCell(deviceDetails.machine_domain);
        datagrid.addCell(deviceDetails.major_version);
        datagrid.addCell(deviceDetails.minor_version);
        datagrid.addCell(deviceDetails.os_version);
        datagrid.addCell(deviceDetails.ou_0); // How do I get this nested level
        datagrid.addCell(deviceDetails.ou_1); 
        datagrid.addCell(deviceDetails.ou_2);
        datagrid.addCell(deviceDetails.ou_3);
        datagrid.addCell(deviceDetails.platform_id);
        datagrid.addCell(deviceDetails.platform_name);
        datagrid.addCell(deviceDetails.policy_type); // Same
        datagrid.addCell(deviceDetails.policy_id);
        datagrid.addCell(deviceDetails.applied);
        datagrid.addCell(deviceDetails.settings_hash);
        datagrid.addCell(deviceDetails.assigned_date);
        datagrid.addCell(deviceDetails.applied_date);
        datagrid.addCell(deviceDetails.prevention_policy_type);
        datagrid.addCell(deviceDetails.prevention_policy_id);
        datagrid.addCell(deviceDetails.prevention_applied);
        datagrid.addCell(deviceDetails.prevention_settings_hash);
        datagrid.addCell(deviceDetails.prevention_assigned_date);
        datagrid.addCell(deviceDetails.prevention_applied_date);
        datagrid.addCell(deviceDetails.sensor_update_policy_type);
        datagrid.addCell(deviceDetails.sensor_update_policy_id);
        datagrid.addCell(deviceDetails.sensor_update_applied);
        datagrid.addCell(deviceDetails.sensor_update_settings_hash);
        datagrid.addCell(deviceDetails.sensor_update_assigned_date);
        datagrid.addCell(deviceDetails.sensor_update_applied_date);
        datagrid.addCell(deviceDetails.device_control_policy_type);
        datagrid.addCell(deviceDetails.device_control_policy_id);
        datagrid.addCell(deviceDetails.device_control_applied);
        datagrid.addCell(deviceDetails.device_control_assigned_date);
        datagrid.addCell(deviceDetails.device_control_applied_date);
        datagrid.addCell(deviceDetails.global_config_policy_type);
        datagrid.addCell(deviceDetails.global_config_policy_id);
        datagrid.addCell(deviceDetails.global_config_applied);
        datagrid.addCell(deviceDetails.global_config_settings_hash);
        datagrid.addCell(deviceDetails.global_config_assigned_date);
        datagrid.addCell(deviceDetails.global_config_applied_date);
        datagrid.addCell(deviceDetails.groups_0);
        datagrid.addCell(deviceDetails.groups_1);
        datagrid.addCell(deviceDetails.group_hash);
        datagrid.addCell(deviceDetails.product_type);
        datagrid.addCell(deviceDetails.product_type_desc);
        datagrid.addCell(deviceDetails.provision_status);
        datagrid.addCell(deviceDetails.site_name);
        datagrid.addCell(deviceDetails.status);
        datagrid.addCell(deviceDetails.system_manufacturer);
        datagrid.addCell(deviceDetails.system_product_name);
        datagrid.addCell(deviceDetails.modified_timestamp);
        datagrid.addCell(deviceDetails.slow_changing_modified_timestamp);
        datagrid.addCell(deviceDetails.meta_version);
        datagrid.addCell(deviceDetails.groups_2);

        datagrid.endRow();


  }

Sample Data Structure (not my real data):

{
    "meta": {
        "query_time": 0.017458709,
        "powered_by": "device-api",
        "trace_id": "7aea2f44-9891-4892-b633-bf0544830e55"
    },
    "resources": [
        {
            "device_id": "8562fe0f4ce845347a85da0e5bf9623d",
            "cid": "aaabbbdddcccddd",
            "agent_load_flags": "1",
            "agent_local_time": "2017-09-15T06:13:15.223Z",
            "agent_version": "3.5.5606.0",
            "bios_manufacturer": "Phoenix Technologies LTD",
            "bios_version": "6.00",
            "config_id_base": "65994753",
            "config_id_build": "5606",
            "config_id_platform": "3",
            "external_ip": "24.16.20.181",
            "mac_address": "00-50-56-8c-17-81",
            "hostname": "TESTMACHINE",
            "first_seen": "2017-07-19T02:08:24Z",
            "last_seen": "2017-09-25T23:45:55Z",
            "local_ip": "172.16.20.30",
            "machine_domain": "hello.info",
            "major_version": "6",
            "minor_version": "1",
            "os_version": "Windows 7",
            "ou":[
                "Desktop","User computers","Computers","Insurance"],  // <-- HOW DO I GET THIS?
            "platform_id": "0",
            "platform_name": "Windows",
            "policies": [  
                {
                    "policy_type": "prevention",// <-- OR THESE
                    "policy_id": "aaabbbdddcccddd",
                    "applied": true,
                    "settings_hash": "ed4a7460",
                    "assigned_date": "2017-09-14T13:03:33.038805882Z",
                    "applied_date": "2017-09-14T13:03:45.823683755Z"
                }
            ],
            "device_policies": {
                "prevention": {
                    "policy_type": "prevention", // <-- OR THESE
                    "policy_id": "aaabbbdddcccddd",
                    "applied": true,
                    "settings_hash": "ed4a7460",
                    "assigned_date": "2017-09-14T13:03:33.038805882Z",
                    "applied_date": "2017-09-14T13:03:45.823683755Z"
                },
                "sensor_update": {
                    "policy_type": "sensor-update",
                    "policy_id": "aaabbbdddcccddd",
                    "applied": true,
                    "settings_hash": "65994753|3|2|automatic",
                    "assigned_date": "2017-09-14T05:15:40.878196578Z",
                    "applied_date": "2017-09-14T05:16:20.847887649Z"
                }
            },
            "product_type": "1",
            "product_type_desc": "Workstation",
            "site_name": "Default-First-Site-Name",
            "status": "normal",
            "system_manufacturer": "VMware, Inc.",
            "system_product_name": "VMware Virtual Platform",
            "modified_timestamp": "2017-09-25T23:46:06Z",
            "meta": {
                "version": "49662"
            }
        }
    ],
    "errors": []
}

Jon · July 2018

First, yes it's possible to do more that 2000 rows, I built a connector that does THOUSDANDS of rows.

The API that I'm calling does pagination, so my loop is done in batches of 1000. My datagrid.addCell() calls are within the loop. Maybe that will get you on the right path.

As for nested objects, well, I had to run my loops twice. The data I'm using has a "Campus" attached to an "Event". As you might imagin we only have a handful of campuses, but thousands of events.

The brick wall I hit was persistence in Domo. My first attempt was to create a JSON object on the fly, and populate my campuses when I encountered a new one, then push that dataset to Domo at the end. All kinds onf hinkyness there. Couldn't make it work. Domo seemed to drop my JSON object, and I never quite figured out how to push a second dataset within the connector. (maybe someone can teach me that here).

So, I used two "reports" in the connector, using the same loop, but slightly different logic. On the "campuses" loop, I populate a JSON object (only for reference) and if the campus isn't in the object, add it to the domo dataset at that moment.

This methodology is working fine for me.

Custom Connector - Parse a json object

Comments

Categories