How to retrieve 100K records via Scripted REST api

[Niamul Arifin]

Requirement: I need to pull 100K records from one of the table e.g. sys_user.

Solution Attempted: I know table API works through pagination technique using sysparm_limit, sysparm_offset, and sysparm_query parameter. However, I wanted to see if anyone in this community knows how to do this via Scripted REST API.

I used the below code in the script area but only getting a single user data as a response.

var gr = new GlideRecordSecure('sys_user');
    gr.orderBy('sys_created_on');
    gr.query();
    
    while(gr.next()){
        var body = {};
        body.email = gr.getDisplayValue('email');
        body.user_name = gr.getDisplayValue('user_name');
        body.active = gr.getDisplayValue('active');
        body.sys_id = gr.getUniqueValue();
        
        response.setBody(body);
    }

How can i get a list of all 100K users via Scripted REST api? Need Assistance.

[Sohail Khilji]

Hi,

By default, ServiceNow has the max limit of 10000 records which will be returned in any rest call and is being caused by the omission of the parameter sysparm_limit which default value is 10000. If you specify a higher value in the URL then you can get the desired amount of records.

Example: /api/now/table/incident?sysparm_limit=40000 

The best practice to retrieve large amounts of records is to by using pagination. That is, get sub-sets of records in different calls, for example, first, you get records from 1 to 10000 in a call, then you get from 10001 to 20000, then from 20001 to 30000, etc. 

To do so, you can make use of the parameters 

• sysparm_limit: to define how many records you get in one call (10000 by default) 
• sysparm_offset: to define the records to exclude from the query 
• sysparm_query=ORDERBYsys_created_on: to sort the list of records based on the created date/time 

In this case, the first call would be something like 

/api/now/table/incident?sysparm_limit=10000&sysparm_offset=0&sysparm_query=ORDERBYsys_created_on 

the second one 

/api/now/table/incident?sysparm_limit=10000&sysparm_offset=10000&sysparm_query=ORDERBYsys_created_on 

the third one 

/api/now/table/incident?sysparm_limit=10000&sysparm_offset=20000&sysparm_query=ORDERBYsys_created_on 

The below code will get you the result:

var gr = new GlideRecordSecure('sys_user');
    gr.orderBy('sys_created_on');
    gr.query();
    
    while(gr.next()){
        var body = {};
        body.email = gr.getDisplayValue('email');
        body.user_name = gr.getDisplayValue('user_name');
        body.active = gr.getDisplayValue('active');
        body.sys_id = gr.getUniqueValue();

var str = JSON.stringify(body);
var parser = new JSONParser();
var parsed = parser.parse(str);

gs.info(parsed.email);

    }

Kindly Mark ✅ Correct or ???? Helpful, if applicable.

MF Sohail Khilji.

LinkedIn - https://www.linkedin.com/in/mf-sohail-khilji/