Pagination trouble with REST API

[kcwong]

I am writing a PowerShell script to call /api/now/attachment to read attachments related to other tables.

In my first call, I used these query parameters: 
sysparm_query table_name=sc_req_item^ORtable_name=incident^ORtable_name=sc_request^ORtable_name=change_request^ORtable_name=problem^ORtable_name=sc_task^ORDERBYsys_created_on
sysparm_limit

1000

sysparm_offset
0

And log the results in a CSV file. In the first call I received 993 records. 
 
On my second call, I used these query parameters:  

sysparm_query 

table_name=sc_req_item^ORtable_name=incident^ORtable_name=sc_request^ORtable_name=change_request^ORtable_name=problem^ORtable_name=sc_task^ORDERBYsys_created_on
sysparm_limit

1000

sysparm_offset
993

I received 998 records this time, but I found two records with sys_id already found in my CSV, i.e. duplicate records despite the ordering.  

What's going on here? Besides duplicate records is it possible ServiceNow is skipping over some of them too?

[kcwong · Accepted solution]

figured it out... need to:
1. ORDERBYsys_created_on
2. For 2nd and later requests, add sys_created_on>[last sys_created_on in last batch]^ to the start of sysparm_query.

[kcwong]

Actually that still don't work.

It can return 0 records (filtered out because of some other reasons, after sysparm_query). In which case I have no new sys_created_on and my query is stuck.