Profile your ServiceNow data with SAP Data Hub

[architectSAP]

Lately, I got a ServiceNow developer account. It comes with a REST API, so I was wondering whether I could profile its data with SAP Data Hub.

Since SAP Vora has become an integral component of SAP Data Hub, I leverage its OpenAPI Client and Vora Avro Ingestor operators to retrieve and store the content of the incident table:



As it turned out, I needed a custom operator to unmarshal the response from ServiceNow anyway, so I decided to implement a looping mechanism to handle potentially larger tables in chunks as well. (Please find the code for both operators in the appendix.) In this case this would not have been necessary since there are only 66 records as per header parameter openapi.header.x-total-count:

{

  "message.request.id": "",

  "openapi.header.content-type": "application/json;charset=UTF-8",

  "openapi.header.x-total-count": "66",

  "openapi.status_code": "200"

}

In the SAP Data Hub Metadata Explorer, I check the Fact Sheet:



So, I got 66 incidents from November 24th 2015 to March 16th 2020 of which 34.84% have been made SLA and none of them have their cause recorded. Interesting.

Appendix

ServiceNow API

Configuration

Script

$.addGenerator(gen);



$.setPortCallback("input",onInput);



sysparm_limit = $.config.getInt("sysparm_limit");

sysparm_offset = 0;



function gen(ctx) {

    var msg = {};

    msg.Attributes = {};

    msg.Attributes["openapi.query_params.sysparm_limit"] = sysparm_limit.toString();

    msg.Attributes["openapi.query_params.sysparm_offset"] = sysparm_offset.toString();

    $.output(msg);

}



function onInput(ctx,s) {

    var msg = {};

    msg.Attributes = {};

    sysparm_offset = sysparm_offset + sysparm_limit;

    msg.Attributes["openapi.query_params.sysparm_limit"] = sysparm_limit.toString();

    msg.Attributes["openapi.query_params.sysparm_offset"] = sysparm_offset.toString();

    $.output(msg);

}

ServiceNow Control

Script

import json



def on_input(in_message):

    dict = json.loads(in_message.body.decode("utf-8"))

    if dict["result"]:

        api.send("output", json.dumps(dict["result"]))

    else:

        api.send("stop", None)

    

api.set_port_callback("input", on_input)