RestAPI data extraction - alm_asset table taking several hours. What options are there to improve?

[SB87]

I'm working on a project where we are extracting data from ServiceNow using REST.

We are using the following method:

1. ServiceNow views created exclusively for our team's needs
2. RestAPI is used to call the above views to extract data from source
3. We then use the data for our internal purposes

However, it seems that due to field customisation (around 50+ custom fields added) to the alm_asset table, our extractor tool (internal middleware that we've developed to support the above) prevents the record limit from being increased above 500 records per call, perhaps due to the table width.

In the meantime, we are looking to improve the middleware; but is there alternative options we can explore? Is batch API something we should be looking at? Could creating several views against the alm_asset table and extracting data by last modified date an option?

Would appreciate any advice.

[Tony Chatfield1]

Hi, unfortunately your post does not make your configuration or requirements clear.
'Extracting data by last modified date'; There is no value in extracted the same records over and over, so if you are currently not extracted based on the sys_updated_on time-stamp them this would be the first positive improvement that you could deliver. Run a delta query to get your baseline data, then in following queries only query for\extract records updated since the last query was run. 

ServiceNows REST implementation supports pagination, so it would be possible to implement a paginated solution,

and perhaps your best result would be combination of pagination and filtering on sys_updated_on

REST APIs (servicenow.com)

Otherwise the issue sounds like it is your middleware, you should be able to confirm this by running the same queries using postman or similar.

[SB87]

@Tony Chatfield1 - thank you for your informative reply. Apologies if I wasn't clear. The initial load would make use of the 'sys_created_on' to form a baseline and deltas will use the sys_updated_on field.

The problem we currently face is the extraction keeps falling over for the alm_asset table. It's probably a middleware problem but we are currently limited to 500 records per API request, making the extraction take close to 9 hours - which isn't acceptable. I'm looking for alternative ways to import the data in case the middleware problem isn't easily resolved or will take time for it to be delivered.

A possible solution that I've thought about is by creating several ServiceNow Database Views, all set against the alm_asset table. We could then make API requests against each of these views, concurrently, based on parameters that we set, e.g.

if (sys_created_on >= A && sys_created_on <= B  &&) {
  //Use view 1
} else if (sys_created_on >= C && sys_created_on <= D) {
  //Use view 2
} else if (sys_created_on >= E && sys_created_on <= F) {
  //Use view 3
} else {
  //Use view 4
}

, etc.