Bulk Upload in Staging Table Though Scripted RestApi

yadi · ‎05-15-2020

Hi Folks,

I have requirement in which Bulk Upload is require through External source in CMDB , I have created a Import Set Table and now i need whenever External source hit the ServiceNow API the whole data is dumping in staging table and after that transform maps are triggered. Earlier Row by Row is coming from external source and through Import Set Api it was working fine but now if Bulk Data is coming the Import Set APi could not accept the data except 1st row as OOTB functionality.

I know this can be achieve by Scripted RestApi But i don't how to write exact logic so that whole bunch of data is dumping in Staging Table.

the data is coming like below JSON Format in one Array.

[{
"u_displayname": "abc123",
"u_vendor": "Cisco",
"u_primaryipv4address": "10.1.1.2",
"u_primaryosversion": "1.2.3.5",
"u_primaryostype": "Cisco IOS",
"u_osdescription": "Cisco IOS Software",
"u_physserialnumber": "abc123xyz",
"u_primarydnsname": "abc123.abc.com"
},
{
"u_displayname": "abc123",
"u_vendor": "Microsoft",
"u_primaryipv4address": "10.1.1.3",
"u_primaryosversion": "1.2.3.6",
"u_primaryostype": "Cisco IOS",
"u_osdescription": "Cisco IOS Software",
"u_physserialnumber": "abc123xyz",
"u_primarydnsname": "abc123.abc.com"
},
{

"u_displayname": "abc123",
"u_vendor": "HP",
"u_primaryipv4address": "10.1.1.4",
"u_primaryosversion": "1.2.3.7",
"u_primaryostype": "Cisco IOS",
"u_osdescription": "Cisco IOS Software",
"u_physserialnumber": "abc123xyz",
"u_primarydnsname": "abc123.abc.com"
}
]

If any one has solution of this requirement it would be great help

Regards

Yad

Ankur Bawiskar · ‎05-15-2020

Hi,

create new scripted rest api and new scripted rest resource

Rest Resource -> POST Method

Script below:

1) iterate over the body

2) parse the json

3) for every object from the array

Sample below: Ensure you are receiving the above json request; I have assumed the json key is the column name

Also use proper import set table in GlideRecord

(function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {
	
	var requestBody = request.body.dataString;
	var parser = new global.JSON();
	var parsedData = parser.decode(requestBody);
	
	for(var i=0;i<parsedData.length;i++){
		
		
		var gr = new GlideRecord('cmdb_test');
		gr.initialize();
		gr.u_displayname = parsedData[i].u_displayname;
		gr.u_vendor = parsedData[i].u_vendor;
		gr.u_primaryipv4address = parsedData[i].u_primaryipv4address;
		gr.u_primaryosversion = parsedData[i].u_primaryosversion;
		gr.u_primaryostype = parsedData[i].u_primaryostype;
		gr.u_osdescription = parsedData[i].u_osdescription;
		gr.u_physserialnumber = parsedData[i].u_physserialnumber;
		gr.u_primarydnsname = parsedData[i].u_primarydnsname;
		gr.insert();	
	}	
	var responseBody = {};
	responseBody.status = "Success";
	response.setBody(responseBody);
	
	
})(request, response);

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

Ankur Bawiskar · ‎05-15-2020

Sure,

Do let me know;

If that helped Kindly close the thread by marking Answer as ✅ Correct & 👍Helpful so that it does not appear in unanswered list.

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Alex Perju · ‎07-28-2021

Hi @Ankur Bawiskar, thanks for your help on the above, we have build something very similar starting from your code, however we have a performance related query.

When we use this method of inserting each record into the staging table, as soon as that record is inserted, it will trigger the transform map immediately just for that one single record.

So to illustrate this, lets say we receive a JSON payload containing 10k records from ThirdParty, our scripted REST API will parse and insert each record sequentially into the staging table. That ultimately means that the transform map will be triggered 10k times (we can see this in the transform history).

The issue is we have an integration where we are expected to receive payloads of up to 60k-100k records at a time, and it takes a couple of hours to process this because of the single record process/transform. Alternatively if we load the data manually via a DataSource(excel file), it only runs the transform map ONCE, and the execution time is 15-20 minutes.

Can you (or someone) please suggest a way in which we can insert all the records(similar to excel DataSource) into the staging table before it starts transforming each one individually?

Hope the attached image of a POC on my dev instance can help with visualizing the issue.

Ankur Bawiskar · ‎07-28-2021

Hi,

Not very much sure. but you can try to create data source, create csv file with all those records and attach to data source and then trigger the transform

So it would run for all records at once

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Alex Perju · ‎07-29-2021

Thank you @Ankur Bawiskar , do you know if there is a ServiceNow API that we can use to transform our REST payload from JSON into an EXCEL file or DataSource?

Ankur Bawiskar · ‎07-29-2021

Hi,

check this

GlideImportSetTransformer - Scoped, Global

REST Import set mode

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader