sysparm_limit=1000 return 0 records whereas sysparm_limit=10000 returns the records

[Shrinil]

I need to poll for Table records. Query that I use is: 

https://<instance>.service-now.com/api/now/v1/table/task?sysparm_limit=1000&sysparm_no_count=true&sysparm_query=sys_updated_on>2023-09-06 16:12:00^ORDERBYsys_updated_on

 This query is returning me 0 records.

But I changed the cursor value to `>16:18:00`, then it returned me 2 records.

I tried to remove all the params that I have used and found after removing `sysparm_limit`, I am getting more number of records.

For the query: 

https://<instance>.service-now.com/api/now/v1/table/task?sysparm_no_count=true&sysparm_query=sys_updated_on>2023-09-06 16:12:00^ORDERBYsys_updated_on

I got 700 records in response.

For query:

https://<instance>.service-now.com/api/now/v1/table/task?sysparm_no_count=true&sysparm_query=sys_updated_on>2023-09-06 16:18:00^ORDERBYsys_updated_on

I got 1300 records in response.

Now there are 2 issues here:

1. 'sysparm_limit' does not seem to be working correctly. It returns records sometimes and does not other times.

2. Even after removing 'sysparm_limit', number of records that I get for timestamp '>16:12:00' are less than '>16:18:00'. This behaviour is very weird. Number of records returned are alway different. FYI: The actual number of records that should have been returned are even higher.

NOTE:

1. I have tried the combination of sysparm_limit + sysparm_offset as someone suggested, it does not help. Still get 0 records in response.

Please suggest a way a good way to fix this  problem. Overall aim is toll poll for records at regular interval and support pagination if number of records to be fetched are very high.

@Gunjan Kiratkar @Ankur Bawiskar 

Thanks.

[jonsan09]

It looks like you are using the v1 version of the API have you tried using v2/latest?

[Shrinil]

No I haven't tried v2 yet because it mentions that the only difference between v1 and v2 is:

• In version v1, if a GET query to retrieve multiple records matches no records, the response is the error No Record Found with status code 404.
• In version v2, if a GET query to retrieve multiple records matches no records, the response is an empty array with status code 200.

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0551763

Does v2 fix the issues with sysparm_limit not working?

[Shrinil]

I tested with v2, no difference in response for any of the API calls. Problem still persists.

[alexcorvino]

Try running this script via background scripts and see if you get the same behavior. If you do then is an artifact of some post query evaluation on GlideRecord's part (ACLs, BRs) and is expected behavior that will need to be tolerated. Note that you'll need to supply the sys_id of the API user.

var count = 0;
var tableName = 'task';
var sysparm_query = 'sys_updated_on>2023-09-06 16:18:00^ORDERBYsys_updated_on';
var limit = 1000;
var currentUser = gs.getUserID();
var impUser = new GlideImpersonate();
impUser.impersonate('SYS_ID_OF_API_USER');
var gr = new GlideRecordSecure(tableName);

// add all query parameter that are added to the API call
gr.addEncodedQuery(sysparm_query)

// Pagination window specified
//gr.chooseWindow(sysparm_offset, sysparm_offset + limit);

// limit specified
gr.setLimit(limit);

//sysparm_no_count set
gr.setNoCount();

gr.query();
//verify results
while(gr.next()){
//get the field level ACLs to kick in
var grObj = {};
new GlideRecordUtil().populateFromGR(grObj, gr);
gs.log(JSON.stringify(grObj));
count++;
}
gs.log("Record count is: " + count);

impUser.impersonate(currentUser);