Import data using a JSON (file) Data Source over HTTPS

[Yonz]

Hello all!

I'm building a (simple) application using AWS Lambda to collect various data items destined for ServiceNow. To simplify the flow I want ServiceNow to issue a simple HTTPS request (kind of a Webhook) to trigger the Lambda Function. It  will then do its thing and return the data as a JSON structure in a proper ServiceNow format:

So far so good.

(We have an ImportSet staging table that has exactly those fields)

{
"records": [
    {
        "element_id": "100100101",
        "element_name": "ION-7000v-1",
        "tkmx_site_id": "DEU04399",
        "tkmx_site_name": "Azure Data Center",
        "description": "ION Box test 1 in Azure DC",
        "hardware_id": "1234567891",  
        "model_name": "ION-7000",
        "role": "spoke",
        "serial_number": "47114711",
        "software_version": "5.4b",
        "state": "bound" 
    },
    {
        "element_id": "100100102",
        "element_name": "ION-7000v-2",
        "tkmx_site_id": "DEU04399",
        "tkmx_site_name": "Azure Data Center",
        "description": "ION Box test 2 in Azure DC",
        "hardware_id": "1234567892",  
        "model_name": "ION-7000",
        "role": "spoke",
        "serial_number": "47114712",
        "software_version": "5.4b",
        "state": "bound" 
    },
    {
        "element_id": "100100103",
        "element_name": "ION-7000v-3",
        "tkmx_site_id": "DEU04399",
        "tkmx_site_name": "Azure Data Center",
        "description": "ION Box test 3 in Azure DC",
        "hardware_id": "1234567893",  
        "model_name": "ION-7000",
        "role": "spoke",
        "serial_number": "47114713",
        "software_version": "5.4b",
        "state": "bound" 
    }
    ]
}

My question is how do I set this up?

Defining a data source of type "FILE" with File retrieval method set to "HTTPS" is the closest I've got. However, this expects a file to be uploaded; I just want to return the results in the HTTP Response, so along these lines:

    response = {
                'statusCode'        : 0,
                'headers'           :  {
                        'Content-Type': 'application/json',
                        'Access-Control-Allow-Origin': '*'
                    },
                'body'              : json.dumps(data_collected_dict, default=str),
                "isBase64Encoded"   : False  
    }

Any ideas?

The solution must be simple and elegant, and utilise Basic OOTB ServiceNow features - AND should use MID-Server to actually issue the HTTPS Request.

BTW: I can of course to it the other way around, and push the data via ImportSet API (Insert Multiple) that works ok. But it is not exactly what I want, as then the whole process can not be 100% controlled within ServiceNow (Scheduled import)

Regards,

Yonz

[Mark A_ Miller · Accepted solution]

Somewhat counterintuitively, it is possible to send a REST message from the instance to its own Import Set API endpoint in a script include, so the entire integration can be run from ServiceNow.

Necessary components:

1. Import table and transform map for desired target table on instance.
2. REST Message with GET method record for retrieval from remote API, with MID Server selected on the GET method.
3. REST Message with POST method record for import to instance Import Set API.
4. Script include that performs data retrieval, parsing, and import.
5. Scheduled job to trigger a public method from the script include that begins the process.

In your script include, retrieve remote JSON, convert to Javascript objects, repackage the objects as JSON according to the Import Set API specification, then push the JSON to the insertMultiple endpoint on the Import API.

Here is a generic code snippet for pushing JSON to an import endpoint using a preconfigured REST message that I hope can get you started. Obviously the class variables need to be set up in the initialize() method of the script include.

_sendImportMessage: function () {
    
    var jsonData = this.importJSON; // JSON with array of objects according to Import SET API specs
        
    try {
        var request = new sn_ws.RESTMessageV2(this.importRestMessageName, 'POST');
        request.setStringParameter('instanceURL', this.instanceURL);
        request.setStringParameter('importTableName', this.importTableName);
        request.setRequestBody(jsonData);
        var response = request.execute();
        var responseBody = response.getBody();
            
    } catch (e) {
        gs.log('_sendImportMessage() REST error: ' + e.message, this.type);
    }
    
    return responseBody;

},

I don't know for sure about whether HTTPS is used for the REST messages. I have a faint recollection that it is. https://community.servicenow.com/community?id=community_question&sys_id=6ce68076db7a18d0fa192183ca96...

Please let me know if this helps or if I can help you any further.

[Yonz]

Thanks Mark,

in discussions with colleagues this is also what we came up with. 

@ServiceNow : If Product Management is reading this: Please consider this as an enhancement request ... To be able to return a JSON List of records with a simple HTTP GET/POST request.

BR

Yonz