How to import data from Rest web service with XML response...

DrewW
Mega Sage
Mega Sage

‎09-28-2015 01:43 PM

So this is not really a question but more an offer of code to the community.   I recently needed to get data about mobile devices from AirWatch which conveniently has a rest interface that can be used to get all of the device data.   After searching the community and spending time looking around I found that there was no simple and easy call that I could do to get the data, then just pass it to an object and abracadabra imported data.   So since based on the community search results I decided to post my code for doing this.   My hope was that all of us would be able to make improvements to it.   It would be nice if the community had a mechanism for code sharing (not the share site) that would allow multiple people to contribute to and it would be managed by community members.   I'm sure that there are some 3rd party sites for this kind of thing so if ServiceNow could integrate one of them with the community that would be great.  

I would to thank John Anderson for his post here, it was helpful.

http://www.john-james-andersen.com/blog/service-now/converting-xml-to-a-record-in-servicenow.html

And ServiceNow for there JDBCProbeSoftwareInventory and ImportSetUtil Script includes that provided most of what I needed.  

Things I would hope we can add since I did not have time at the moment.

1 - Automatic creation of import set table based on XML file.

2 - Verification that XML file structure matches current structure of import table and update if needed.

If someone else has something better please contribute.

Hope people find the below help full.

So here is my Script include and a scheduled job to import the data.

To use is just create an import set table with column names that match the XML file and build your transform map.

var ImportSetUtilHFHS = Class.create();




ImportSetUtilHFHS.prototype = Object.extendsObject(ImportSetUtil, {


       initialize : function() {


               


       },


       


       createImportSetHFHS : function(import_set_table_name, import_set_description){


               var isgr = new GlideRecord("sys_import_set");


               isgr.initialize();


               isgr.mode = "asynchronous";


               isgr.state = "loading";


               //isgr.data_source = this.data_source_sysid;


               isgr.table_name = import_set_table_name;


               isgr.short_description = import_set_description;


               var is_id = isgr.insert();


               return isgr;


       },


       


       loadFromXML : function(xpath_root_node, xmlDoc, importSetGr){


               var tableName = importSetGr.getValue("table_name");


               var nodeList = xmlDoc.getNodes(xpath_root_node);


               


               var nodes = xmlDoc.getNodes(xpath_root_node);


               for(var i = 1; i <= nodes.getLength(); i++){


                       var gr = new GlideRecord(tableName);


                       gr.initialize();


                       if(typeof importSet != 'undefined')


                               gr.sys_import_set = importSetGr.getValue("sys_id");


                       var nodeList = xmlDoc.getNodes(xpath_root_node + "["+i+"]/*");


                       this._iterateNodeList(nodeList, "u", null, gr);


                       gr.insert();


               }


       },


       


       transforData : function(importSetGr){


               //Mark the import set as Loaded


               isGr.state = "loaded";


               isGr.update();


               


               // transform the import set


               var t = new GlideImportSetTransformer();


               t.transformAllMaps(isGr);


               


       },


       


});

Scheduled job, this is just to provide a sample.

(function(){


       gs.log("Starting... ", "Import AirWatch Devices");


       var pageNumber = 0;


       //Get page 0 of the data.


       var sgXMLDoc = new XMLDocument(getXMLData('AirWatchDeviceSearch', 'get', "pagenumber", pageNumber));


       var currentRowCount = (sgXMLDoc.getNodes("/DeviceSearchResult/Devices")).getLength();


       var totalRowCount = (sgXMLDoc.getNodes("/DeviceSearchResult/Devices")).getLength();


       //gs.log("totalRowCount: " + totalRowCount, "Import AirWatch Devices");


       


       //Create an import set for the "u_airwatch_device_search_import" set table.


       var iu = new ImportSetUtilHFHS();


       var isGr = iu.createImportSet("u_airwatch_device_search_import", "Import from Schedule Job: Import AirWatch Devices");


       //If there are rows to process process them


       while(currentRowCount > 0 && pageNumber < 100){


               //Load the data into the import set table.


               iu.loadFromXML("/DeviceSearchResult/Devices", sgXMLDoc, isGr);


               //Get the next page of data and if there are rows the loop will go around again.


               pageNumber += 1;


               sgXMLDoc = new XMLDocument(getXMLData('AirWatchDeviceSearch', 'get', "pagenumber", pageNumber));


               currentRowCount = (sgXMLDoc.getNodes("/DeviceSearchResult/Devices")).getLength();


               totalRowCount += currentRowCount;


               //gs.log("totalRowCount: " + totalRowCount, "Import AirWatch Devices");


       }




       //Transform the data that was loaded.


       iu.transforData(isGr);


       


       gs.log("Done... ", "Import AirWatch Devices");


       


       function getXMLData(restService, action, parmName, parmValue){


               try {


                       var rm = new sn_ws.RESTMessageV2(restService, action);


                       if(typeof parmName != "undefined" && parmName)


                               rm.setStringParameter(parmName, parmValue);


                       var response = rm.execute();


                       var responseBody = response.getBody();


                       var httpStatus = response.getStatusCode();


               } catch(ex) {


                       var message = ex.getMessage();


               }


               return responseBody;


       }


})();

Sample XML file.

<?xml version="1.0" encoding="utf-8"?>


<DeviceSearchResult xmlns="http://www.air-watch.com/servicemodel/resources" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


   <Page>0</Page>


   <PageSize>1000</PageSize>


   <Total>7396</Total>


   <Devices>


       <Id xmlns="">54908</Id>


       <Udid>20f3258997aa9f783d7093c07c4a3a038a0fa5a4</Udid>


       <SerialNumber>DLXFM46HDJHF</SerialNumber>


       <MacAddress>A46706E5CEE7</MacAddress>


       <Imei>A100001CBF25F6</Imei>


       <AssetNumber>20f3258997aa9f783d7093c07c4a3a038a0fa5a4</AssetNumber>


       <DeviceFriendlyName>corrys1 iPad iOS 4.3.2 DJHF</DeviceFriendlyName>


       <LocationGroupId title="HFMG">6307</LocationGroupId>


       <LocationGroupName>HFMG</LocationGroupName>


       <UserId title="">66367</UserId>


       <UserName>corrys1</UserName>


       <UserEmailAddress/>


       <Ownership>C</Ownership>


       <PlatformId title="Apple">2</PlatformId>


       <Platform>Apple</Platform>


       <ModelId title="iPad 2 CDMA (16 GB)">2</ModelId>


       <Model>iPad 2 CDMA (16 GB)</Model>


       <OperatingSystem>4.3.2</OperatingSystem>


       <PhoneNumber>0000007492</PhoneNumber>


       <LastSeen>2015-07-12T00:33:56.537</LastSeen>


       <EnrollmentStatus>Enrolled</EnrollmentStatus>


       <ComplianceStatus>Compliant</ComplianceStatus>


       <CompromisedStatus>false</CompromisedStatus>


       <LastEnrolledOn>2012-02-09T19:19:45.32</LastEnrolledOn>


       <LastComplianceCheckOn>0001-01-01T00:00:00</LastComplianceCheckOn>


       <LastCompromisedCheckOn>2013-05-01T02:31:29.663</LastCompromisedCheckOn>


       <IsSupervised>false</IsSupervised>


       <DeviceMCC>


           <SIMMCC>310</SIMMCC>


           <CurrentMCC>0</CurrentMCC>


       </DeviceMCC>


       <AcLineStatus>0</AcLineStatus>


       <VirtualMemory>0</VirtualMemory>


   </Devices>


</DeviceSearchResult>
0 Helpfuls
  • 2,888 Views
1 REPLY 1

DrewW
Mega Sage
Mega Sage

‎09-28-2015 01:48 PM

Just noticed an issue in the code above, the "createImportSetHFHS" should just be "createImportSet", for some reason I cannot edit the post to fix it.