Pagination and REST API on ServiceNow

[lkleijn]

ServiceNow REST API has a default limit to 10.000 which I don't want to modify.

I would like to read 1 million records from CMDB and so I will use the sysparm_offset and sysparm_limit parameters. The script that will call the 100 API calls will take a long time and there might be operations done on the CMDB while my script is running.

My questions are : How does this affect my script ? Will it be inserted randomly in one of the loops ? Will I miss this data if I already went through its place in the query ?

Thanks

[MrMuhammad · Accepted solution]

Hi,

The response will fetch random records so to achieve correct pagination try using below three query parameters in combination. 

• 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 

See KB0727636 for detailed information.

Hope that helps!

Regards,

Muhammad

[JulianLeite]

Hi  MrMuhammad
How to know how many records the request is going to return? I need to know to set the variable sysparm_offset properly.

Example:

selecting records from incident table btw dates 2020-01-01 to 2020-06-30 

How many calls I need to sent to serviceNow in order to get all the records 
Thanks

[gilbs92]

Someone can answer this question, how we know when to stop without retrieve 404 status code

[anguspalmer]

You can get a count of all records first by querying the Aggregate API. Then divide the total count by the row limit to get the number of pages you need.

Something like this;

1) Get the count from 

https://{INSTANCE_NAME}.service-now.com/api/now/stats/{TABLE_NAME}?sysparm_count=true&sysparm_query=...

2) Divide the count by 10000 or whatever limit you choose if not using the default to get the number of pages

3) Get the records one page at a time using the Table API and the query, limit and offset parameters mentioned in the first post. Increment pagenum each time starting at zero.

https://{INSTANCE_NAME}.service-now.com/api/now/table/{TABLE_NAME}?sysparm_offset={PAGENUM*LIMIT}&sy...

Note, it is probably a good idea to explicitly enforce a sort by adding ORDERBY{FIELDNAME} to your query.