Running a REST query to populate a table

[jshatney]

In the process of setting up an integration between a 3rd party CMDB and ServiceNow.   They have provided a REST API to query their database.
I have used both SOAP and REST queries in the past to pull specific data on demand and use it, but in this case, I'm looking to take the response and put it into a data source so that I can transform it into the CMDB.

Does anyone have any suggestions for the following:

1. Where should the REST messages be called from?   I was thinking a scheduled job since it does not need to be on demand.

2. What method should I use to take the response and write that to a table?

3. Where should I store the response form the REST message so that I can run a transform against it and populate various cmdb tables?   I was thinking for servers and computers, I could use the imp_computer table and utilize the transform script (with some adjustments) that already exists.

Thanks for your input.

[solutioningnow]

Hi John,

You need to write a scheduled job to post rest message and write a BR on ECC Queue table to parse response to insert records into your staging table or final cmdb records.

Please mark answer as correct/helpful, if it was helpful

Regards,

Solutioner

Enhance Knowledge NOW@ www.solutioningnow.com

http://www.solutioningnow.com/

[jshatney]

Solutioner - your response sounds helpful, but without an example of some sort, I am still completely missing the application of how this works exactly.

I have a nice JSON formatted REST response and now I want to loop through it a write the various results to a staging table to be transformed into the CMDB. The issue that I'm having is that I do not have an example of how that is done.

I've tried something to the effect of:

var response = r.execute();

var cmdb=response.getBody();

var gr;

for(var i=0; i<cmdb.length; i++){

      var insert=false;

      gr = new GlideRecord("u_my_staging_table");

      gr.u_name=cmdb[i]["name"];

      gr.u_other_attributes_etc=cmdb[i]["description"];

      gr.insert();

}

Logically it seems the above should work, but it does not.

[PeterWiles]

Hi John,

The response from the API, as you know, will be in JSON format. The third party should tell you how this is made up. But what you need to do is parse the response. http://wiki.servicenow.com/index.php?title=JSONParser

As I don't know what kind of JSON your third party is returning, my best guess is something like:

if(response.getStatusCode() == "200")   //Has it returned connected successfully to the third party.

{

        var parser = new JSONParser();

        var parsed = response.parser(response.getBody());//We now have "parsed" which should be an multi-array of the JSON result

        //I assume that the actual CMDB results returned will be in their own "JSON" var along with other information returned so take this element and loop.

        var cmdb = parsed["cmdb"];

        var gr;

        for(var i=0; i<cmdb.length; i++){

                var insert=false;

                gr = new GlideRecord("u_my_staging_table");

                gr.u_name=cmdb[i]["name"];

                gr.u_other_attributes_etc=cmdb[i]["description"];

                gr.insert();

        }

}

Again, without knowing the response, I can 100% confirm the above will work but hopefully you can amend accordingly but I use similar code.

Pete

[jshatney]

Peter - using the JSONParser has definitely helped me make some progress. I can now write to my import set tables and the transform is working as expected.

I do have 2 questions:

1. Importing Model_IDs - how is this accomplished? I created the model IDs beforehand so the import could reference them, but they are not sticking in the transform map

2. MAC Address - The JSON response includes these as "mac_address":"4D:CD:1D:12" - since it is separated by colons, it is only grabbing the first pair of numbers. Anyone dealt with this before?

[jshatney]

Ignore the MAC Address - the field type in the import set was set to Integer. Switching to string fixed this issue.

Now if I can figure out Model ID i'll be all set.