Pagination on a REST API call (Code)

[younes_sebti]

Objectif

Connect a microsoft REST API (HRTB) that returns only a fixed number of records at a time.

Do pagination

Code

Step 1 : Get the number of records

Step 2 : Loop and extract x records each time

The only trick there is to find which field on the table you are querying can be used for pagination.

In my case I'm using Id. The Id field has unique and incremental values.

try {

                              var sm = new sn_ws.RESTMessageV2();

                              var basic_url =   "the URL to get all your records without a filter";

                              sm.setEndpoint(basic_url);

                              var data = call(sm);

                              var user = new GlideRecord('sys_user');

                              var cost_center = new GlideRecord('cmn_cost_center');

                              var dep = new GlideRecord('cmn_department');

                              var bu = new GlideRecord('u_business_unit');

                              var update = false;

                              var total = data.length;

                              // Define how many records at a time you want to extract

                              var limit_rest = 5000;

                              var x = 1;

                              var midServerName = new GlideRecord('ecc_agent');

                              // Make sure the result contain data

                              if(!isNaN(parseInt(total))){

                                                            // Pagination extracting 5000 records a time at most

                                                            while(x<total){

                                                                                            // get all user Ids between x and x + 5000

                                                                                            sm.setEndpoint(basic_url+"&filter=Id%20Lt%20"+(x+limit_rest)+"%20and%20Id%20Gt%20"+x);

                                                                                            sm.setBasicAuth("xxxxxxxxxxxxx","xxxxxxxxxxxxxxxxxxxxxxxxxx");

                                                                                            data = call(sm);

                                                                                            x += limit_rest;

                                                                                            for (var i=0; i<data.length; i++){

                                                                                                                            // DO STUFF

                                                                                            }

                                                            }

                              }

}

catch(ex) {

}

// This function perform the API call and return the data.

function call(sm){

                              var response = sm.execute();

                              var responseBody = JSON.parse(response.getBody());

                              var httpStatus = response.getStatusCode();

                              var data = responseBody.Data;

                              var headers = sm.getRequestHeaders();

                              var getHeaders = "";

                              for(var h in headers){

                                                            getHeaders += h+":"+headers[h]+"\n";

                              }

                              return data;

}

[Y_12]

Thanks for this code.

But I want to pull 80,000 records using pagination as limit_rest = 5000; in your code on row number 4 in outer try block, you are calling:

var data = call(sm); //this will call URL without any filter & paging limit.

So I belive your code will fail at this row becasue of timeout on ServiceNow side.

I think best code will be to use sysparm_offset using below, but I don't know how to get rel="next" from header response. There may be some sample code or functions or library to get rel="next" from header response.

Link

REST message data can be split into multiple result sets rather than forcing the user to submit multiple requests. The header has different links available for the first set, previous set, next set, and the last set of records, where applicable. For example: https://<instance name>.service-now.com/api/now/table/cmdb_ci?sysparm_offset=40&sysparm_limit=10000>;rel="next", https://<instance name>.service-now.com/api/now/table/cmdb_ci?sysparm_offset=40&sysparm_limit=10000>;rel="prev", https://<instance name>.service-now.com/api/now/table/cmdb_ci?sysparm_offset=0&sysparm_limit=10000>;rel="first", https://<instance name>.service-now.com/api/now/table/cmdb_ci?sysparm_offset=2780&sysparm_limit=10000>;rel="last" Note: The limit parameter defaults to 10,000 records. This limit can be set to any value. Be aware, however, that an unusually large value can impact system performance.

[JulianLeite]

I'm having issue applying pagination, it isn't working in my case

I set 1000 items per page

First call

sysparm_display_value=false&sysparm_limit=1000&sysparm_offset=0&sysparm_query=sys_created_onBETWEENjavascript&colon;gs.dateGenerate('2018-02-28','00:00:00')@javascript&colon;gs.dateGenerate('2018-02-28','23:59:59')^ORDERBYsys_created_on&install_status=1&operational_status=1

Second call

sysparm_display_value=false&sysparm_limit=1000&sysparm_offset=1000&sysparm_query=sys_created_onBETWEENjavascript&colon;gs.dateGenerate('2018-02-28','00:00:00')@javascript&colon;gs.dateGenerate('2018-02-28','23:59:59')^ORDERBYsys_created_on&install_status=1&operational_status=1

Third call

sysparm_display_value=false&sysparm_limit=1000&sysparm_offset=2000&sysparm_query=sys_created_onBETWEENjavascript&colon;gs.dateGenerate('2018-02-28','00:00:00')@javascript&colon;gs.dateGenerate('2018-02-28','23:59:59')^ORDERBYsys_created_on&install_status=1&operational_status=1

Anyone knows why it isn't working?