Updating multiple records using the Import Set API

[Joey Alain1]

Hey everyone,

I need to perform a bulk update from a PowerShell script running on a server, against several hundred CMDB CI's within ServiceNow, updating the same date/time field on each record. I need to do this several times per day.

Currently I've accomplished this by writing a Scripted REST API Web Service. I pass this WS three variables - (1) a comma delimited list of hostnames as one single variable, (2) the target field to update, and (3) the new desired value for that target field. When the WS receives this call, it parses the list of hostnames and using GlideQueries and methods, it iterates through them, making the update as requested on each one. The entire thing takes less than 20 lines of code and accomplishes the bulk update (for roughly 300 updates) in about 6 seconds. This entire thing works exactly as intended.

I'm now being steered in the direction of using Import Set API's to accomplish this instead. I'm working through the bugs but I have two questions:

1) Natively, making multiple updates against multiple sys_ID's, in a single Import Set API call, doesn't seem to be supported. I'm either writing Transform Scripts or making multiple calls. Should this be possible and if so, how are people accomplishing this?

2) To the folks who are telling me it's best practice to use an Import Set API instead of a Scripted Web Service, is this correct as 'best practice' and why? Is it really more risky and/or maintenance-heavy to write a purpose-built specific Web Service than it is to cobble together Import Sets, Transform Maps, new ACLs to write to the new staging tables, etc, etc?

Thanks in advance,

[marcguegueniat · Accepted solution]

Hi,

On point 1:

Your import set will need to have one line per object updated. You cannot have 1 line with many targets. however you just need 1 import set (with 300 lines) for 1 update batch.

You have to put your unique key as a column and make it a coalesce in the transform map.

On point 2:

IMHO, I think Import Set API is more a common use, it is covered in trainings and does not requires advanced coding. If you tell a ServiceNow beginner you are using Data Source and Transform Map he will understand. If you tell him it is in a Scripted REST API, there is a chance he does not know about advanced javascript or even about web services.

I think the default choice would be import sets.

Some requirement may challenge/confirm this:

- may this service be called from other 3rd parties/processes?
- are there restriction on how your instance can be called (ex: Web service only ...)
- can the 300 line files be generated and saved to a shared directory?
- do you have any high performance requirement, or synchronous call requirement (REST better here)
- should the loading be triggered from the instance (ex: every 6h) or from the caller (not possible in import set by default)

Note: I'm not a ServiceNow Guru, this mainly comes from my experience

Regards,

Marc

[Joey Alain1]

Thanks Marc, if I may ask a few follow ups.

When you mention having more than one line on the import set, I'm not clear on that. My uploaded import set (xlsx file) only defines the headers, it doesn't have any actual rows. I'm making this call from a 3rd party service using the Import Set API. When making the call, I can only specify 'sys_ID' once, unless I'm to construct a bit of a JSON array and pass multiple sys_ID's through. 

My uploaded import set isn't defined in length and can't be, as each time I make this call to bulk update fields, I have no idea how many records I want or need to update. It could be 10 one time, and 1000 the next time. I can't have a static import set to represent that, or that many rows.

Again, I'm completely clear on how to do this if I were just using using an Import Set manually and driving data updates from an uploaded spreadsheet, but the Import Set API call specifically asks for a single 'SYS_ID', so unless I'm to stuff an array into that SYS_ID, I'm either making multiple calls using that same API, or I'm missing something.

[marcguegueniat]

> I'm completely clear on how to do this if I were just using using an Import Set manually and driving data updates from an uploaded spreadsheet

ok, that is what I was thinking of you could do, but I did not understand you were already clear on this

Now if I'm not mistaken, you're trying to import with REST API into your table.

But there is another way: you can SOAP call on your import set table.

When you do this you have to provide the fields of the import set table, not the sys_id.

Look at http://instance/your_importset_table.do?WSDL

You get a get a web service which will import into the import set table and trigger transform map.

More info : https://docs.servicenow.com/bundle/kingston-application-development/page/integrate/web-services-apis...

Regards,

[big-m0dem]

Joey,

This is exactly what I want to do, and looks like will have to do as the import has to be triggered by the external source as it is limited to the traffic/ports it can accept, i.e., can't be a WS provider. 

Did you engage the IRE at any point in processing?