question Re: Bulk Upload in Staging Table Though Scripted RestApi in ITSM forum

Bulk Upload in Staging Table Though Scripted RestApi

yadi — Fri, 15 May 2020 09:43:43 GMT

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

Re: Bulk Upload in Staging Table Though Scripted RestApi

Ankur Bawiskar — Fri, 15 May 2020 09:50:23 GMT

Hi,

If you use scripted rest API you will have to inform the 3rd party team about the change in endpoint;

in scripted rest API do this

1) accept the request body

2) parse the json body

3) for every json object in the array create record in import set using GlideRecord

It would trigger the transform map

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

Re: Bulk Upload in Staging Table Though Scripted RestApi

yadi — Fri, 15 May 2020 10:00:14 GMT

Hi Ankur,

Thanks for joining, yup i now that that need to inform 3rd party to change the endpoint but as i am new Scripted RestAPi so i need help on how to bulid logic in script section.

Suppose my Staging Table is CMDB_TEST now what logic i need to write in script section.Please help.

Regards

Yad

Re: Bulk Upload in Staging Table Though Scripted RestApi

Ankur Bawiskar — Fri, 15 May 2020 10:15:33 GMT

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

Re: Bulk Upload in Staging Table Though Scripted RestApi

yadi — Fri, 15 May 2020 10:31:55 GMT

Thanks Ankur let me try this logic and get back to you.

Re: Bulk Upload in Staging Table Though Scripted RestApi

Ankur Bawiskar — Fri, 15 May 2020 10:34:07 GMT

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

Re: Bulk Upload in Staging Table Though Scripted RestApi

Alex Perju — Wed, 28 Jul 2021 16:32:35 GMT

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.

Re: Bulk Upload in Staging Table Though Scripted RestApi

Ankur Bawiskar — Thu, 29 Jul 2021 05:25:08 GMT

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

Re: Bulk Upload in Staging Table Though Scripted RestApi

Alex Perju — Thu, 29 Jul 2021 08:42:02 GMT

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?

Re: Bulk Upload in Staging Table Though Scripted RestApi

Ankur Bawiskar — Thu, 29 Jul 2021 10:07:44 GMT

Hi,

check this

GlideImportSetTransformer - Scoped, Global

REST Import set mode

Regards
Ankur