ServiceNow REST API call pagination doesn't work.

[Saira Varghese]

I am developing an ADF pipeline to pull data from ServiceNow REST API. I have defined API call as shown here; My issue is that this doesn't paginate.  I should get 3 sets of 3000 rows each; but it just pulls first 3000 rows 3 times. 

Could someone please help me with this? I am assuming this doesn't like sysparm_offset parameter? 

Thank you. 

Here are the details :

- https://<Instance>.service-now.com/task_ci.do?JSONv2&sysparm_action=getRecords&sysparm_query=sys_updated_on>=FromDt^sys_updated_on<ToDate&sysparm_query=ORDERBYsys_updated_on&displayvalue=all&sysparm_record_count=3000&sysparm_offset=0

- https://<Instance>.service-now.com/task_ci.do?JSONv2&sysparm_action=getRecords&sysparm_query=sys_updated_on>=FromDt^sys_updated_on<ToDate&sysparm_query=ORDERBYsys_updated_on&displayvalue=all&sysparm_record_count=3000&sysparm_offset=3000

and so on. 

[Tony Chatfield1]

Hi, I believe that your primary issue is that your 'query' is not valid\does not use a defined REST API,
also within the query, your specific filtering\query conditions are also not valid.
I think you might need the table API?

Table API | ServiceNow Developers

Testing in a PDI using REST API explorer, with the table API
sysparm_limit and sysparm_offset both function as expected.

I would suggest you start by running a query directly in your instance on task_ci table that returns the data you require, then right click the breadcrumb and select copy query, then apply this as your sysparm_query value - a date range query with ODERBYDESC  would look something like

sys_updated_onBETWEENjavascript:gs.dateGenerate('2020-12-31','00:00:00')@javascript:gs.dateGenerate('2022-06-30','23:59:59')^ORDERBYDESCsys_updated_on

You can then test\validate your requirements in your instance REST API explorer

Introduction to the REST API Explorer | ServiceNow Developer

https://<instancename>.service-now.com/now/nav/ui/classic/params/target/%24restapi.do

which would provide a resulting URL like

https://<instancename>service-now.com/api/now/table/task_ci?sysparm_query=sys_updated_onBETWEENjavascript%3Ags.dateGenerate('2020-12-31'%2C'00%3A00%3A00')%40javascript%3Ags.dateGenerate('2022-06-30'%2C'23%3A59%3A59')%5EORDERBYDESCsys_updated_on&sysparm_limit=10&sysparm_offset=10

Finally if having issues reproducing the functionality in your third party toolset,
an application like Postman can be a good intermediate step for validating query formats.

Postman API Platform

[Saira Varghese]

Thank you for responding to me. 

Are you saying that I shouldn't use https://<Instance>.service-now.com/task_ci.do?JSONv2 ? This URI works well in Azure Data Factory - I want to get both display and actual values; pagination is the only thing that doesn't work. 

When I use https://<instancename>service-now.com/api/now/table/task_ci?  URI in Azure Data Factory, it returns only 1 row. 

Please help. I have been trying to make this work this entire week. 

Thanks in advance.  

[Tony Chatfield1]

Yes, you should be using the ServiceNow REST API's to query data from your instance, not browsing to URL's directly.

To understand why Azure returns only 1 record you would need to first check the target table in your instance and ensure there is more than 1 record that meets your query, confirm the query and  payload received by Azure, and then look at how Azure processes the payload.

I would recommend that you start by reviewing the free learning material available on the ServiceNow developer portal

REST Integrations | ServiceNow Developers

Then test with an external tool-set like Postman, this is a good second step (once you have confirmed your query in RE$ST API explorer) as Postman offers simple visibility of sent and received payloads.