We're running into an issue where right now we are using Scheduled Jobs to kick off a script include that uses a MID Server to connect to a 3rd party system for importing data into a ServiceNow table directly. What we want to do is use Import Sets and Transform maps also, so that we aren't pulling for 3rd part software using REST and entering data directly into the tables using the script.
With that said, we are stuck on figuring out how exactly how to "Load Data" into the import set and then kicking off then "Run Transform". We know the transform map needs to be asynchronous and that if we were using odbc, file, xml, we would go the Import Sources route, but we can't. Have to be ServiceNow scheduled import using MID server to consume the REST services.
Any help would be appreciated.
I may be oversimplifying this, but why not use Scheduled Imports?
http://wiki.servicenow.com/index.php?title=Scheduling_Data_Imports
You would just create a data source that pulls the data via file, HTTP, JDBC, etc from the third party source. This would automatically bring the data in through an Import Set and Transform Map. These results would automatically be processed without user interaction.
That's what we want to do, but the only access to the remote data is using a REST Service. My understanding is to do the scheduling data imports, we will need to have either a File, LDAP, or JDBC way of grabbing it, which none of which we can do (without getting into, in my opinion, more complicated solutions).
Thanks for clarifying. The way I would approach it is to make the REST call to the third party and then take the responding data and instead of having the scheduled script set the data into the table directly, load it into the Import Set table directly.
This will automatically trigger the mapping and the transform of the data and will update the proper table automatically.
Maybe I am missing something ,and I am fairly new to the Integration side of SN, but how do we actually post the XML or JSON response from a REST call straight to the Import Set table through a script. I think my brain is making this more difficult that it should be. NOTE: via the REST module in Berlin we are successfully making GET calls and can see success via logging. Our current REST call returns and XML file...how do we, via scripting, load that into a table?
Any pointers in the right direction would be most helpful!
Here are a couple of articles that may be helpful to you...
This references a handle ImportSetUtils library available in the instance:
http://www.john-james-andersen.com/blog/service-now/converting-xml-to-a-record-in-servicenow.html
The following link is an article that talks about SOAP responses, but an XML REST response would be handled in the same way as far as getting it into an import set:
http://www.john-james-andersen.com/blog/service-now/tutorial-handling-a-soap-response-in-servicenow.html
Jacob - were you ever able to figure this out? I'm in the same situation where I have a JSON formatted REST response that I'm looking to write to an import table.
I looked at both of John Andersen's links and tried going down the path of converting the response into XML and then using the various XML tools to write to a table, but I either don't know the proper XPATH or the JSON response isn't converting correctly into XML.
Hi John,
Can you please suggest a way for loading data (obtained from REST call) in an asynchronous mode. Actually I want to hold the data in import set table until it's fully loaded and once that's done then I want to transform it to regular table.
Thanks
Bhupinder
THANK YOU! I was having an issue trying to figure out how I would get my data into an import table and then transform it. I didn't realize the transform would happen automatically. I really appreciate this.
Wasn't sure if this was answered yet but the following is a list of steps to import data from an Outbound REST call in combination with a MID Server using a Scheduled Job and hold it with in an Import Set table using Asynchronous Import Set and Transform Map. Then schedule a job to perform the transform.
- Create a table that extends Import Set Row
- Give it a name
- Create a new application menu or Place module under Existing application menu or none
- Uncheck the auto create user role if not desired
- Create necessary columns
- Create Transform Map
- Enter name of above table with .list appended in the text filter box
- <table_name>.list
- Click on Transform Maps
- Click New
- Give it a name
- Assign the target table
- Enter name of above table with .list appended in the text filter box
- Create an import set
- Enter sys_import_set.do in text filter box
- Set mode to Asynchronous
- Keep State at Loading
- Set table to import set table created in step one
- Give description if needed
- Create Outbound REST call
- When creating the call method be sure to populate the MID Server field with desired MID Server
- Create a Scheduled Job (not a Scheduled Import) to populate the import set
- Click New
- Choose to run a script
- Set the desired times when to execute script
- Grab the scripted code from the REST call by clicking the Preview Script Usage
- Create a script that will use the REST call script usage to populate the table created in step one. You can also do any manipulation in here before populating or use the script sections in the transform map.
- Example:
try {
var r = new sn_ws.RESTMessageV2('name_of_your_outbound_rest_message', 'get');
r.setStringParameter('a_parameter', 'parameter_value');
var response = r.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
}
catch(ex) {
var message = ex.getMessage();
}
var results = responseBody.split(','); //This depends on what's in your response
var restGR = new GlideRecord('u_table_from_step_one');//Important
restGR.initialize();
restGR.u_assigned_to = result[0]; //this would depend on your response
restGR.u_assignment_group = result[1]; //this would depend on your response
restGR.u_short_description = result[2]; //this would depend on your response
restGR.u_description = result[3]; //this would depend on your response
restGR.sys_import_set = 'sys_id of the import set created in step 3'; //Important
restGR.insert();
- Example:
- Modify the Scheduled Job named "Asynchronous Import Set Transformer" to meet your requirements to run all Asynchronous import sets or just the one specific Rest Call import set if you haven't done your specific modifications in step 5 or in the Transform Map
I hope that is useful and what you're looking to do.
Chris,I see you are manually creating an import set here.Will it be retained in the system?.My understanding was that for each import a new one is created and it will be deleted after use in a few days.Is it done specifically to make it asynchronous?I am using standard import table(imp_location) and for me I don't see a need for the transformation to be asynchronous.So do I need the sys_import_set assignment? Why would somebody need asynchronous transformation?
Hi Tessy,
The answers to your questions are as follows.
Q. Will it be retained in the system?
A. Yes, it should. It is the same setup that follows when creating an Inbound Web Service. Import Sets and Import set rows are deleted per a Scheduled cleanup which can be modified by excluding the table you're using. Click the "Cleanup" module under "Import Set Tables"
Q. Is it done specifically to make it asynchronous?
A. No, not specifically. I did this just to separate it out from the normal Inbound Web Services. But you can use one of the OOB tables created or create a new Inbound Web Services to use.
Q. So do I need the sys_import_set assignment?
A. Yes because that is what tells what import set to add the Import Set Row to. Remember the very first table created is an Import Set Row not the import set itself. So it needs to know what import set to belong.
Q. Why would somebody need asynchronous transformation?
A. I used asynchronous because it was mentioned as a criteria from the originator of this post. They wanted control over the data before inserting it. Usually when using the inbound Web Services, although the holding table is populated first, the data gets inserted into the destination table right when the web services populates the holding table. I interpreted the post as they did not want the insertion to happen right away but to have it hold until some sort of verification or manipulation of data could be performed. Then have it insert into the destination table.
Thank you so much for your very clear answers for all of my questions. I have questions about using a MID server for a REST call.I intend to use the scheduled script execution job with two REST messages. The first call(to a Django/python application hosted on a linux server) will process the feed files, compare against application's( mirror of servicenow location table) Mysql database and put the create/update/delete decisions to a staging table, calculate the change percentage.If change is less than 10%, proceeds to the next REST message which will get the create/update/delete records from python application's staging table and updates import table(OOB imp_location table).Since the change is validated before importing and also since I want it to be completely automated, I can go for synchronous transformation to target table.
-In my situation what is the advantage of making the REST call through a MID server
-.Where will be the MID server hosted?
-Can I use any existing MID server for my purpose or do I have to create a new one ?
For the REST messages I perform, I don't specify the mid server and I supply the basic authentication by checking "Use basic auth" and supply the user id and password. I set up my REST messages in the REST Message section and then call this message from my script. In my case, I'm using the Airwatch API to pull data.
// Use Airwatch API to retrieve list of devices
var r = new RESTMessage('Airwatch Devices', 'get');
var response = r.execute();
I did try to use other methods to connect but found the basic auth was the only one that worked for me--the certificate wasn't working as expected. I would think you should be able to use your existing Mid Server for the REST call as long as everything is set up (i.e. if it needs a certificate rather than basic auth)...
Q-In my situation what is the advantage of making the REST call through a MID server?
A. In this case there probably isn't too much of an advantage using the MID Server other than security. From my experience MID Servers are used mostly for Discovery, RunBook automation, Security, and things that need to be done inside the company's firewall.
Q-Where will the MID server be hosted?
A. Usually anywhere accessible inside your company's firewall. The MID Server is actually a piece of software that installed on a computer or even on a VM. You can run multiple MID Servers on one machine or on different machines. Just be sure it can handle the load. Specs can be found here: system requirements:MID Server - ServiceNow Wiki
Load balancing: Deploying Multiple MID Servers - ServiceNow Wiki
Q-Can I use any existing MID server for my purpose or do I have to create a new one ?
A. You can do either. Again just make sure it can handle the load for each operation it's doing.
Thanks Statcey,I will be using Basic authentication
Thanks.
I wish I could mark Chris Burks answer as correct one since it worked for me.I had to use a MID server.I used OOB import table (Location) and a synchronous mode import set for transforming to target table
I'm just glad I could help out.
Thanks
Hey Bhupinder,
I been awhile since this post, wondering if you every found a good way of loading all together when using a script to insert into import set table.
Tony
Hey Chris,
First off, thanks for sharing this, it was incredibly helpful.
My question is: I've built everything out as you instructed and am successfully populating the extended import set rows with my REST get. But it's not automatically transforming when hitting the table. Am I missing something?
I have the import set in Mode = Asynchronous & state = Loading. I see the child import set rows in state = pending, but nothing happens (transform map is created and aligned to the u_import_set_row_location_api table.)
I'm considering just writing to the imp_location table if I can't get this to work.
I'll include my scheduled script to help others stuck on the json parsing (which was a pain in *badword*). This API converts the get to our cmn_location table.
try {
var r = new sn_ws.RESTMessageV2('Location API', 'get');
r.setStringParameter('Accept', 'application/json');
r.setStringParameter('key', 'private');
var response = r.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
gs.log("GET SERIOUS 1" + responseBody);
var parser = new JSONParser();
var parsed = parser.parse(responseBody);
gs.log("Parsed JSON" + parsed);
/*for (var n in parsed.Locations.Location[0]){ //use this to see your json objects
gs.log("Parsed[" + n +"]= " + parsed.Locations.Location[0][n]);
}*/
gs.log(parsed);
}catch(ex) {
var message = ex.getMessage();
gs.log("DAVE Catch Contents " + message);
}
var locs = parsed.Locations.Location;
gs.log("Parsed Array Size locs = " + locs.length);
var restGR = new GlideRecord('u_import_set_row_location_api'); //Extended import set row table
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;
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 = '0ce7417e13cee600c4513372e144b068'; //This is the import set in the extended table above
restGR.insert();
}
Sample of one REST json object returned from the GET for reference.
{
"Locations": {
"@count": 173,
"Location": [
{
"@locale": "en-US",
"ID": Private,
"Name": "Minneapolis Office",
"TypeCode": "Private",
"TypeDescription": "Hq (headquarters)",
"Address": {
"Latitude": Private,
"Longitude": -Private,
"AddressLine1": "Private",
"City": "Minneapolis",
"County": "Hennepin",
"Subdivision": "MN",
"PostalCode": "Private",
"CountryName": "United States",
"FormattedAddress": "Private"
},
"TimeZone": {
"TimeZoneCode": "CST",
"TimeZoneDescription": "Central Std Time",
"TimeZoneOffset": {
"OffsetCode": "UTC",
"OffsetHours": "-06"
}
},
"TelephoneNumber": {
"FunctionalTypeDescription": "Main",
"PhoneNumber": "Private"
},
"LocationMilestones": {
"OpenDate": "Private",
"LastRemodelDate": "null"
}
},
It is not tranforming automatically because it is an asynchronous import set. By default web service import sets are synchronous and transforms immediately.You need to create an asynchronous mode import set and provide its sys_id only if you want to do examine it , make sure it is correct and transform it manually( by clicking the reprocess link inside the import set) or let the system do it by nightly process.
If you want to process it immediately automatically , you can just comment out this line:
restGR.sys_import_set = '0ce7417e13cee600c4513372e144b068';
System will automatically create a synchronous import set for you each time and the transformation happens immediately
http://wiki.servicenow.com/index.php?title=Web_Service_Import_Sets#gsc.tab=0
Hi David,
I guess since this discussion became a little long it may have lost it's intended result. Normally working with import sets you wouldn't have to go through the process I wrote out. That process was brought about because my understanding of the original post was that the user did not want their data processed immediately but the process to be kicked off on a schedule.
@ tantony - Thanks for the reply
Haha, it's always something simple. I'm good to go!
Thanks for the quick response, both you and Chris!
Alright guys,
So here's my final product. I added the initialize to create a fresh new import set each time and load all the import set rows in it. Hopefully this will help others until ServiceNow gets us a better REST get web service.
To clarify, this is a scheduled script that will trigger daily and sync locations in the cmn_location table.
Thanks again for your help guys!
try {
//Send REST get to grab json blob
var r = new sn_ws.RESTMessageV2('Location API', 'get');
r.setStringParameter('Accept', 'application/json');
r.setStringParameter('key', 'Private');
var response = r.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
gs.log("Response Body " + responseBody);
var parser = new JSONParser();
var parsed = parser.parse(responseBody);
gs.log("Parsed JSON" + parsed);
/*for (var n in parsed.Locations.Location[0]){ //use this to see your json objects
gs.log("Parsed[" + n +"]= " + parsed.Locations.Location[0][n]);
}*/
gs.log(parsed);
}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.Locations.Location;
gs.log("Parsed Array Size locs = " + locs.length);
var restGR = new GlideRecord('u_location_api_import'); //Query extended import set rows table
//Loop through the json 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;
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 import set and its rows then the transform map does its thing automatically
When you open an import set entry, the upper table shows the transform history and lower table shows the import set rows.You will notice that for a web service import set by not specifying a hard coded sys_import_set, Transform History table(sys_import_set_run) will have as many entries as the import set rows. Which makes me think that each import set row is run separately. If you specify a import set sys_id, there will be only one row in the Transform History table(sys_import_set_run) table.
davidself3, were you able to change the status of the import set from 'loading' to 'processed' once the load from web service is complete?
Karthik, No, after a certain time period, the import set flips to "processed". I haven't paid close attention to the time, but I believe it's a day or two. In our instance it's not important to know when the import is complete, only that the locations are current with our corporate location API.
Also, I ended up having to flip this whole integration over to XML because json kept timing out. Here's the code base utilizing the XMLhelper script include.
try {
//Send REST get to grab XML blob
var r = new sn_ws.RESTMessageV2('Location API', 'get');
var response = r.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
var parsed = new XMLHelper().toObject(responseBody);
}catch(ex) {
var message = ex.getMessage();
}
//Create a new import set
var crImpSet = new GlideRecord('sys_import_set');
crImpSet.initialize();
crImpSet.mode = 'synchronous';
//crImpSet.mode = 'asynchronous'; //switch to this for testing import set will not trigger transform map
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_alternateidentifier = locs[i].AlternateIdentifier.ID;
restGR.u_name = locs[i].Name;
restGR.u_typedescription = 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.u_opendate = locs[i].LocationMilestones.OpenDate;
restGR.u_company_code = locs[i].CompanyCodeDescriptor.ID;
restGR.u_subtypecode = locs[i].SubTypeCode;
restGR.sys_import_set = crImpSet.sys_id; //This is the sys_id from import set above
restGR.insert();
}
Hope this helps folks building REST API's in ServiceNow!
Karthik, This is above my scripting ability, haven't done it before. But you could potentially add script in the xml loop that says when it reaches 0 go ahead and set the status of the sys_import_set record to "processed". I may end up having to do this if my team wants me to button it up. If that's the case, I'll certainly update this thread with my solution. Hope that helps!
try {
//Send REST get to grab XML blob
var r = new sn_ws.RESTMessageV2('Location API', 'get');
var response = r.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
var parsed = new XMLHelper().toObject(responseBody);
//Create a new import set
var crImpSet = new GlideRecord('sys_import_set');
crImpSet.initialize();
crImpSet.mode = 'synchronous';
//crImpSet.mode = 'asynchronous'; //switch to this for testing import set will not trigger transform map
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;
if (locs[i].AlternateIdentifier) {
restGR.u_opendate = locs[i].AlternateIdentifier.ID;
}
restGR.u_name = locs[i].Name;
restGR.u_typedescription = locs[i].TypeDescription;
if (locs[i].Address) {
restGR.u_latitude = locs[i].Address.Latitude;
}
if (locs[i].Address) {
restGR.u_subdivision = locs[i].Address.Subdivision;
}
restGR.u_typecode = locs[i].TypeCode;
if (locs[i].Address) {
restGR.u_countryname = locs[i].Address.CountryName;
}
if (locs[i].Address) {
restGR.u_addressline1 = locs[i].Address.AddressLine1;
}
if (locs[i].Address) {
restGR.u_postalcode = locs[i].Address.PostalCode;
}
if (locs[i].Address) {
restGR.u_formattedaddress = locs[i].Address.FormattedAddress;
}
if (locs[i].Address) {
restGR.u_city = locs[i].Address.City;
}
if (locs[i].Address) {
restGR.u_longitude = locs[i].Address.Longitude;
}
if (locs[i].TelephoneNumber) {
restGR.u_phonenumber = locs[i].TelephoneNumber.PhoneNumber;
}
restGR.u_county = locs[i].Address.County;
if (locs[i].TimeZone) {
restGR.u_timezonecode = locs[i].TimeZone.TimeZoneCode;
}
restGR.u_address = locs[i].Address.FormattedAddress;
if (locs[i].LocationMilestones) {
restGR.u_opendate = locs[i].LocationMilestones.OpenDate;
}
if (locs[i].CompanyCodeDescriptor) {
restGR.u_company_code = locs[i].CompanyCodeDescriptor.ID;
}
restGR.u_subtypecode = locs[i].SubTypeCode;
restGR.sys_import_set = crImpSet.sys_id; //This is the sys_id from import set above
restGR.insert();
}
}catch(ex) {
var message = ex.getMessage();
}
I've made some updates because we discovered that the post to the import set table was failing if the dot walked values in the array were empty. Figured I'd share this to help others.
Best,
David
Hi All,
I am a similar requirement - schedule import of data via REST call. I created a transform map and coalesce on a string field. When I run this transform manually using load data - it works perfectly. But when I run it using a script like the one above - it inserts all and coalesce does not work.
I am doing something wrong?
Thanks,
Nitya
This is really helpful. Thanks for sharing.
