Need Help with Scheduled Job with REST API parsing some custom fields into an array

[mattmm]

Hi,

I have a Scheduled Job doing a REST API GET from an external Asset dbase (using RESTMessage V2). I am parsing the responseBody fine for named properties, but there is a section for "customFields" that groups together properties and I am not sure how to parse these in my script without bringing the lot in (see ResponseBody example further below)?

Scheduled job Script

try {
    var r = new sn_ws.RESTMessageV2('ConnectWise', 'Default GET');
    r.setStringParameterNoEscape('yesterdayFormatted', gs.getProperty('connectwise.currentDateTime'));
    var response = r.execute();
    var responseBody = response.getBody();
    var httpStatus = response.getStatusCode();
    var parsed = JSON.parse(responseBody);

    var statusCodesProperty = gs.getProperty('http.response.status.codes');
    var httpStatusCodeArray = statusCodesProperty.split(', ');

    //Check if httpStatus exists in the array
    if (httpStatusCodeArray.indexOf(httpStatus.toString()) !== -1) {
        var crImpSet = new GlideRecord('sys_import_set');
        crImpSet.initialize();
        crImpSet.mode = 'asynchronous';
        crImpSet.table_name = 'u_connectwise_asset_data';
        crImpSet.state = 'loading';
        crImpSet.insert();

        var restGR = new GlideRecord('u_connectwise_asset_data');
        for (var i = 0; i < parsed.length; i++) {
            restGR.initialize();
            restGR.u_config_recid = parsed[i].id;
            restGR.u_configuration_type = parsed[i].type.name;
            restGR.u_status = parsed[i].status.name;
            restGR.u_company = parsed[i].company.name;
            restGR.u_expires = parsed[i].warrantyExpirationDate;
            restGR.u_serial_number = parsed[i].serialNumber;
            restGR.u_model_number = parsed[i].modelNumber;
            restGR.u_vendor = parsed[i].vendor.name;
            restGR.u_tag_number = parsed[i].tagNumber;
            restGR.u_contact = parsed[i].contact.name;
            restGR.u_installed = parsed[i].installationDate;
            restGR.u_locations = parsed[i].location.name;
            restGR.u_site_name = parsed[i].site.name;
            restGR.u_purchased = parsed[i].purchaseDate;
            restGR.u_manufacturer = parsed[i].manufacturer.name;

            restGR.sys_import_set = crImpSet.sys_id;
            restGR.insert();
        }
    }
    crImpSet.state = "loaded";
    crImpSet.load_completed = gs.nowDateTime();
    gs.setProperty('connectwise.currentDateTime', crImpSet.load_completed.getDisplayValue());
    crImpSet.update();

    var transformer = new GlideImportSetTransformer();
    transformer.transformAllMaps(crImpSet); //Transform the import set rows
    if (transformer.isError()) {
        gs.error('Error executing the transform');
    }
} catch (ex) {
    var message = ex.message;
}

A sample ResponseBody below shows 4 of the fields I am parsing without issue (id, status, company, contact), but I don't know how to parse the custom fields at the bottom called "Disposal Date" and "Date Last Sighted". I am a beginner at API scripting, so any help much appreciated. Please see ResponseBody below, but also the Scheduled Job Script above.

ResponseBody

 {
        "id": 19192,
        "name": "CWRR213",
        "type": {
            "id": 25,
            "name": "PC/Notebook",
            "_info": {
                "type_href": ""
            }
        },
        "status": {
            "id": 1,
            "name": "Active",
            "_info": {
                "status_href": ""            }
        },
        "company": {
            "id": 2791,
            "identifier": "RANDOM",
            "name": "Random Ltd",
            "_info": {
                "company_href": ""
            }
        },
        "contact": {
            "id": 12225,
            "name": "Random",
            "_info": {
                "contact_href": ""
            }
         },
        "deviceIdentifier": "",
        "serialNumber": "1234567",
        "modelNumber": "ThinkPad L480",
        "tagNumber": "A0012345",
        "installationDate": "2019-06-04T10:51:33Z",
        "warrantyExpirationDate": "2022-07-08T00:00:00Z",
        "vendorNotes": "",
        "notes": "Location Jan 2023 Sesame St Site Audit: Admin Office     \nHad a Sticker that has since worn off (old Asset Tag: A004321)\n\n123456 - User Exit: Joe Bloggs - appears to be a shared machine ",
        "macAddress": "",
        "lastLoginName": "",
        "billFlag": false,
        "backupSuccesses": 0,
        "backupIncomplete": 0,
        "backupFailed": 0,
        "backupRestores": 0,
        "backupServerName": "",
        "backupBillableSpaceGb": 0.00,
        "backupProtectedDeviceList": "",
        "backupYear": 0,
        "backupMonth": 0,
        "ipAddress": "",
        "defaultGateway": "",
        "osType": "",
        "osInfo": "",
        "cpuSpeed": "",
        "ram": "",
        "localHardDrives": "",
        "manufacturer": {
            "id": 12,
            "name": "Lenovo",
            "_info": {
                "manufacturer_href": ""
            }
        },
        "questions": [
             
            {
                "answerId": 60742,
                "questionId": 289,
                "question": "IMEI",
                "sequenceNumber": 3.00,
                "numberOfDecimals": 0,
                "fieldType": "Text",
                "requiredFlag": false
            }
        ],
        "activeFlag": true,
        "managementLink": "",
        "remoteLink": "",
        "mobileGuid": "",
        "companyLocationId": 72,
        "showRemoteFlag": false,
        "showAutomateFlag": false,
        "needsRenewalFlag": false,
        "manufacturerPartNumber": "",
        "_info": {
            "lastUpdated": "2024-05-29T01:15:00Z",
            "updatedBy": "",
            "dateEntered": "2019-06-27T06:31:55Z",
            "enteredBy": ""
        },
 "customFields": [
 {
                "id": 55,
                "caption": "Disposal Date",
                "type": "Date",
                "entryMethod": "EntryField",
                "numberOfDecimals": 0
            },
            {
                "id": 80,
                "caption": "Date Last Sighted",
                "type": "Date",
                "entryMethod": "EntryField",
                "numberOfDecimals": 0,
                "value": "2024-05-29T00:00:00Z"
            }
			
			     ]
    },

Any Help is more than greatly appreciated!

[Kris Moncada]

var parsed = [{
    "id": 19192,
    "name": "CWRR213",
    "type": {
        "id": 25,
        "name": "PC/Notebook",
        "_info": {
            "type_href": ""
        }
    },
    "status": {
        "id": 1,
        "name": "Active",
        "_info": {
            "status_href": ""
        }
    },
    "company": {
        "id": 2791,
        "identifier": "RANDOM",
        "name": "Random Ltd",
        "_info": {
            "company_href": ""
        }
    },
    "contact": {
        "id": 12225,
        "name": "Random",
        "_info": {
            "contact_href": ""
        }
    },
    "deviceIdentifier": "",
    "serialNumber": "1234567",
    "modelNumber": "ThinkPad L480",
    "tagNumber": "A0012345",
    "installationDate": "2019-06-04T10:51:33Z",
    "warrantyExpirationDate": "2022-07-08T00:00:00Z",
    "vendorNotes": "",
    "notes": "Location Jan 2023 Sesame St Site Audit: Admin Office     \nHad a Sticker that has since worn off (old Asset Tag: A004321)\n\n123456 - User Exit: Joe Bloggs - appears to be a shared machine ",
    "macAddress": "",
    "lastLoginName": "",
    "billFlag": false,
    "backupSuccesses": 0,
    "backupIncomplete": 0,
    "backupFailed": 0,
    "backupRestores": 0,
    "backupServerName": "",
    "backupBillableSpaceGb": 0.00,
    "backupProtectedDeviceList": "",
    "backupYear": 0,
    "backupMonth": 0,
    "ipAddress": "",
    "defaultGateway": "",
    "osType": "",
    "osInfo": "",
    "cpuSpeed": "",
    "ram": "",
    "localHardDrives": "",
    "manufacturer": {
        "id": 12,
        "name": "Lenovo",
        "_info": {
            "manufacturer_href": ""
        }
    },
    "questions": [

        {
            "answerId": 60742,
            "questionId": 289,
            "question": "IMEI",
            "sequenceNumber": 3.00,
            "numberOfDecimals": 0,
            "fieldType": "Text",
            "requiredFlag": false
        }
    ],
    "activeFlag": true,
    "managementLink": "",
    "remoteLink": "",
    "mobileGuid": "",
    "companyLocationId": 72,
    "showRemoteFlag": false,
    "showAutomateFlag": false,
    "needsRenewalFlag": false,
    "manufacturerPartNumber": "",
    "_info": {
        "lastUpdated": "2024-05-29T01:15:00Z",
        "updatedBy": "",
        "dateEntered": "2019-06-27T06:31:55Z",
        "enteredBy": ""
    },
    "customFields": [{
            "id": 55,
            "caption": "Disposal Date",
            "type": "Date",
            "entryMethod": "EntryField",
            "numberOfDecimals": 0
        },
        {
            "id": 80,
            "caption": "Date Last Sighted",
            "type": "Date",
            "entryMethod": "EntryField",
            "numberOfDecimals": 0,
            "value": "2024-05-29T00:00:00Z"
        }

    ]
}];

gs.info(parsed[0].customFields[0].id); // returns 55
gs.info(parsed[0].customFields[1].numberOfDecimals); //returns 0

//sample of iterating over customFields

for(var i in parsed[0].customFields){

	var obj = parsed[0].customFields[i];

	if(obj.value)
		gs.info(obj.value);

	gs.info(obj.numberOfDecimals);

}

CustomFields  is an array of objects; of which I gave a sample way of traversing that property.

Hope this helps.

[mattmm]

Apologies, I'm not big on the coding side of ServiceNow. I've built this off the back of others' handy work. Where would I insert this within my existing script above? Also would it be something like the below (what do I put where the question mark is?), and would this go under my section where I have the other parsing?

for (var i in parsed[0].customFields) {
       var obj = parsed[0].cusomtFields[i];
       
       if(obj.id == 80) {
restGR.u_date_last_sighted = obj.<?>

[Kris Moncada]

Hi @mattmm ,

You are doing great. We all started as newbs.=) 

Since it appears you are trying to specifically get the Last Date Sighted,  you would need to put "value" in the condition block if(obj.id == 80).

Please see below.

try {

    var r = new sn_ws.RESTMessageV2('ConnectWise', 'Default GET');
    r.setStringParameterNoEscape('yesterdayFormatted', gs.getProperty('connectwise.currentDateTime'));
    var response = r.execute();
    var responseBody = response.getBody();
    var httpStatus = response.getStatusCode();
    var parsed = JSON.parse(responseBody);

    var statusCodesProperty = gs.getProperty('http.response.status.codes');
    var httpStatusCodeArray = statusCodesProperty.split(', ');

    //Check if httpStatus exists in the array
    if (httpStatusCodeArray.indexOf(httpStatus.toString()) !== -1) {
        var crImpSet = new GlideRecord('sys_import_set');
        crImpSet.initialize();
        crImpSet.mode = 'asynchronous';
        crImpSet.table_name = 'u_connectwise_asset_data';
        crImpSet.state = 'loading';
        crImpSet.insert();

        var restGR = new GlideRecord('u_connectwise_asset_data');
        for (var i = 0; i < parsed.length; i++) {
            restGR.initialize();
            restGR.u_config_recid = parsed[i].id;
            restGR.u_configuration_type = parsed[i].type.name;
            restGR.u_status = parsed[i].status.name;
            restGR.u_company = parsed[i].company.name;
            restGR.u_expires = parsed[i].warrantyExpirationDate;
            restGR.u_serial_number = parsed[i].serialNumber;
            restGR.u_model_number = parsed[i].modelNumber;
            restGR.u_vendor = parsed[i].vendor.name;
            restGR.u_tag_number = parsed[i].tagNumber;
            restGR.u_contact = parsed[i].contact.name;
            restGR.u_installed = parsed[i].installationDate;
            restGR.u_locations = parsed[i].location.name;
            restGR.u_site_name = parsed[i].site.name;
            restGR.u_purchased = parsed[i].purchaseDate;
            restGR.u_manufacturer = parsed[i].manufacturer.name;

            //insert here
            for (var j in parsed[i].customFields) {
                var obj = parsed[i].customFields[j];

                if (obj.id == 80) {
                    restGR.u_date_last_sighted = obj.value; //this gets the date of last sighted.
                }


            }

            restGR.sys_import_set = crImpSet.sys_id;
            restGR.insert();


        }
        crImpSet.state = "loaded";
        crImpSet.load_completed = gs.nowDateTime();
        gs.setProperty('connectwise.currentDateTime', crImpSet.load_completed.getDisplayValue());
        crImpSet.update();

        var transformer = new GlideImportSetTransformer();
        transformer.transformAllMaps(crImpSet); //Transform the import set rows
        if (transformer.isError()) {
            gs.error('Error executing the transform');
        }
    }


} catch (ex) {
    var message = ex.message;
}

[mattmm]

Thanks for the help so far @Kris Moncada !!  Unfortunately this didn't work. When inserting that code, the Import set just sits on loading and won't progress after the scheduled job ran. I'll do some more testing with and without it today using the "Execute Now" function to be sure.