Running a REST query to populate a table

hcoburn
Tera Contributor

on ‎04-15-2015 11:45 AM

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.

My suggestion would be:

1. As you say, scheduled job

2. You can script REST API calls so on the schedule job,   call the query from there. Details are on the wiki on how to script but when you set one up, there is a link that gives you demo code.

3. Either create a custom table to put the data in then transform against it or on the the scheduled job, just create the script to update the CMDB directly though that will depend on your policies and processes

Pete.

What method would you use to write the REST message response to a table. It comes back in CSV or JSON format.

The REST response will be in JSON format. ServiceNow has a JSON parser which can parse the response and then run the script against it.

The part I'm struggling with is how do I write a script to put the REST response into a table.


I'd like to to drop the records into a table and then run a transform on it as you suggested above.
Been looking for examples of something similar, but unable to locate anything.

Thanks

The wiki has some good information on this.

In particular I think you are looking for the next line in the code, as shown below:

var response = r.execute();

var body = response.getBody();

The body (or response.getBody()) should have the data returned from the REST web service that you are calling. Any information you need to populate into a record should come from there.

Note that the RESTMessage and the RESTResponse objects are Script Includes provided in your instance - and you should be to look at the code to see what additional methods are available to you (I would not modify them without good cause).

John,

Were you ever able to get this working? I have a similar task where I need to pull in CI's from a 3rd party CMDB (JAMF Casper) and import them into ServiceNow. I have the REST query configured to get the data I need but I'm at a loss as to where to go next.

Any help would be greatly appreciated.

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

Logo.png

Enhance Knowledge NOW@ www.solutioningnow.com

http://www.solutioningnow.com/

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.

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

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?

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.

Model_IDs?

In regards to the MAC address, I am not sure why it would do that since the parser should be taking it from between the quotes, even with the ":" in the string. Without testing, not sure whats going on there.

If you can't amend it at the source, you might want to look at a string replace before you parse the result. Loop through and look for the "mac_address" and then do a string replace between the quotes for the ":" and replace with a "." or something else.

Once parsed, do another sting replace.

Pete

I got the MAC addresses working by changing the field type in the import set to string instead of integer.

Model ID was also set to integer, but changing that to a string did not help bring that in on import. The model ID on a CI is just a reference to another table. I've already created all the model IDs associated with this specific import so it should just reference the correct one on transform.

It does not appear to be doing this correctly. Do I need to change the field type on the import set to reference and point it to the hardware model table for this to work properly? (that's what I will be trying next)

I assume the value being imported into the import table is the sysID or the display value.

I would convert the modelID field it to a string, if not done so already on the transform table.

When the data has been load to transform, if you copy the ModelID and do a lookup on the referencing table sys_id, does it match? Just a confirmation that it's referencing correctly.

Pete

Hi Matthew.

i am currently facing the same issue. Would you mind give me some hints ?
I got the REST Message working to the JSSResource/computers/subset/basic for Casper and able to get the informations.

I am just stuck with the Parsing and bring into the Import set table.
Many thanks in advance

Frank

I did get it to work for the most part.

The code below is run from a schedule job, pulling everything into a custom import table, then transforming over to cmdb_ci_computer (coalescing on serial_number).

It's not the best or prettiest, but it works. I plan on cleaning this up and also grabbing the software inventory but just haven't had the time to do either lately

var r = new RESTMessage('JAMF-CasperComputerInventory', 'get');


var response = r.execute();






var jsonString = response.getBody();


var parser = new JSONParser();


var parsed = parser.parse(jsonString);






for (i = 0; i < parsed.computer_reports.length; i++) {


       var name = parsed.computer_reports[i].Computer_Name;


       var make = parsed.computer_reports[i].Make;


       var model = parsed.computer_reports[i].Model;


       var number_of_processors = parsed.computer_reports[i].Number_of_Processors;


       var processor_speed_mhz = parsed.computer_reports[i].Processor_Speed_MHz;


       var processor_Type = parsed.computer_reports[i].Processor_Type;


       var serial_number = parsed.computer_reports[i].Serial_Number;


       var asset_tag = parsed.computer_reports[i].Serial_Number;


       var total_ram_mb = parsed.computer_reports[i].Total_RAM_MB;


       var operating_system = parsed.computer_reports[i].Operating_System;


       var username = parsed.computer_reports[i].Username; //username field format in casper matches sys_user.user_name field format in ServiceNow


       var drive_capacity_mb = parsed.computer_reports[i].Drive_Capacity_MB;


       //var warranty_expiration = parsed.computer_reports[i].Warranty_Expiration;   //work in progress. need to conver to proper date format before import






       var rec = new GlideRecord('u_imp_casper_computer');


         rec.initialize();


                       rec.computer_name = name;


                       rec.make = make;


                       rec.model = model;


                       rec.number_of_processors = number_of_processors;


                       rec.processor_speed_mhz = processor_speed_mhz;


                       rec.processor_Type = processor_Type;


                       rec.serial_number = serial_number;


                       rec.asset_tag = asset_tag;


                       rec.total_ram_mb = total_ram_mb;


                       rec.operating_system = operating_system;


                       rec.username = username;


                       //rec.warranty_expiration = warranty_expiration;


                       rec.drive_capacity_mb = drive_capacity_mb;


         rec.insert();




}

Hi Matthew,

thank you so much for your response.This will help me a lot. Once i get this working i will look also in the Software Part (as we have SAM Active) and if i am faster then you , i will for sure let you know what i did.

Thanks again.


Frank

This document was generated from the following discussion: Running a REST query to populate a table

Preview file
12 KB
Preview file
12 KB
0 Helpfuls
  • 10,074 Views
Comments
davidself3
Tera Contributor
‎10-11-2016 12:36 PM

Thanks for sharing, very helpful.



I recently built an integration using JSON that up and switched over to XML only on me, so I had to adjust my code. I figured this might help save someone some time should they have this happen to them.



I'm using the XMLHelper script include ServiceNow made, works great!



try {

   //Send REST get to grab XML blob

   var r = new sn_ws.RESTMessageV2('Location API', 'get');

   r.setStringParameter('key', 'Private');

   var response = r.execute();

   var responseBody = response.getBody();

   var httpStatus = response.getStatusCode();

   var parsed = new XMLHelper().toObject(responseBody);

   for (var n in parsed.Location[0]){ 

   //gs.log("Parsed.Location[0][" + n +"]= " + parsed.Location[0][n]); //use this to see your XML objects

}

}catch(ex) {

   var message = ex.getMessage();

}

//Create a new import set

var crImpSet = new GlideRecord('sys_import_set');

crImpSet.initialize();

crImpSet.mode = 'synchronous';

crImpSet.table_name = 'u_location_api_import'; //Set the extended importset table

crImpSet.state = 'loading';

crImpSet.insert();

var locs = parsed.Location;

var restGR = new GlideRecord('u_location_api_import'); //Query extended import set rows table

//Loop through the xml chunks until 0 remain creating import set rows to be transformed

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

   restGR.initialize();

   restGR.u_id = locs[i].ID;

   restGR.u_name = locs[i].Name;

   restGR.u_typedescription = locs[i].TypeDescription;

   //gs.log("Tots " + locs[i].TypeDescription);

   restGR.u_latitude = locs[i].Address.Latitude;

   restGR.u_subdivision = locs[i].Address.Subdivision;

   restGR.u_typecode = locs[i].TypeCode;

   restGR.u_countryname = locs[i].Address.CountryName;

   restGR.u_addressline1 = locs[i].Address.AddressLine1;

   restGR.u_postalcode = locs[i].Address.PostalCode;

   restGR.u_formattedaddress = locs[i].Address.FormattedAddress;

   restGR.u_city = locs[i].Address.City;

   restGR.u_longitude = locs[i].Address.Longitude;

   restGR.u_phonenumber = locs[i].TelephoneNumber.PhoneNumber;

   restGR.u_county = locs[i].Address.County;

   restGR.u_timezonecode = locs[i].TimeZone.TimeZoneCode;

   restGR.u_address = locs[i].Address.FormattedAddress;

   restGR.sys_import_set = crImpSet.sys_id; //This is the sys_id from import set above

   restGR.insert();

}

//At this point we populate the importset and its rows then the transform map does its thing automatically


Version history
Last update:
‎04-15-2015 11:45 AM
Updated by: