Table API returning invalid record in response

[DarshanVarasani]

We are currently developing an integration that involves retrieving table records data from ServiceNow. To filter the data, we utilize the "sysparm_query" query parameter in our API calls. However, we've encountered instances where the records received in the response do not align with the specified filter criteria.

Our data retrieval conditions are as follows:

1. sys_updated_on=<value of sys_updated_on of the last collected record> and sys_id>value of sys_id of the last collected record OR
2. sys_updated_on><value of sys_updated_on of the last collected record>^sys_updated_on<current_time

As an example, the API call structure is as follows:

<servicenow-instance-url>/api/now/table/incident?sysparm_display_value=all&sysparm_limit=10000&sysparm_exclude_reference_link=true&sysparm_query=sys_updated_on=2024-01-23+19:01:42^sys_id>SYS_ID_OF_THE_INCIDENT^NQsys_updated_on>2024-01-23+19:01:42^sys_updated_on<2024-01-23+19:10:43^ORDERBYsys_updated_on,sys_id

Upon making this API call, we received a record in the response with the sys_updated_on value as "2024-01-23 21:03:06".

From our debug logs, we observed a 3-minute delay in receiving the response.

We are investigating whether, during the request initiation, the records were initially filtered based on the provided criteria, but before the response was sent, the record underwent an update, leading to its inclusion in the response with a sys_updated_on time outside the specified filter.

We would appreciate any insights or suggestions regarding this situation.

Thank you in advance!

[Tony Chatfield1]

Hi, the doc's state that sparm_query takes an encoded query and the query in your post does not match expected format when searching time stamps

Table API | ServiceNow Developers

You can create and example encoded query on your target table using the filters and then right click the filter and select 'Copy query'

and you can test in your instance API explorer
/now/nav/ui/classic/params/target/%24restapi.do

As an example a query smiliar to yours would look like

/api/now/table/incident?sysparm_query=sys_updated_onON2024-01-15%40javascript%3Ags.dateGenerate('2024-01-15'%2C'start')%40javascript%3Ags.dateGenerate('2024-01-15'%2C'end')%5Esys_id%3D0060e2ab97b73110fce4938de053af18%5ENQsys_updated_onBETWEENjavascript%3Ags.dateGenerate('2023-10-10'%2C'00%3A00%3A00')%40javascript%3Ags.dateGenerate('2024-01-14'%2C'23%3A59%3A59')%5EORDERBYsys_updated_on%2Csys_id&sysparm_display_value=all&sysparm_limit=10

the encoded query

sys_updated_onON2024-01-15@javascript&colon;gs.dateGenerate('2024-01-15','start')@javascript&colon;gs.dateGenerate('2024-01-15','end')^sys_id=0060e2ab97b73110fce4938de053af18^NQsys_updated_onBETWEENjavascript&colon;gs.dateGenerate('2023-10-10','00:00:00')@javascript&colon;gs.dateGenerate('2024-01-14','23:59:59')

[DarshanVarasani]

Hi,
Thanks for the reply.

I had one more query regarding the encoded sysparm_query you suggested.

So from the suggested encoded sysparm_query if I understand it correctly the first part

sys_updated_onON2024-01-15@javascript:gs.dateGenerate('2024-01-15','start')@javascript:gs.dateGenerate('2024-01-15','end')

This would match all the incidents having the sys_updated_on date as "2024-01-15" but it will not include the time to be matched.

Is there any way I can get all the records matching the particular time as well. As I can see time is included with the date while using BETWEEN operator on sys_updated_on but while using ON operator it only includes date.

[Tony Chatfield1]

Hi, if you open incident list view and create a filter query that returns the results you want,
you can then right click on the filter breadcrumb and select copy query which will provide the encoded query for you.

eg
sys_updated_onBETWEENjavascript&colon;gs.dateGenerate('2024-01-23','19:01:42')@javascript&colon;gs.dateGenerate('2024-01-23','19:10:43')

 

[DarshanVarasani]

Hi,

Thanks for providing this information but the issue here we face is that if we use gs.dateGenerate() function to create the date object then the servicenow converts it to the UTC first and then search for the incidents even though we pass the UTC time.

So, basically we make all the API call to the servicenow with the UTC time as our integration handles all the datetime in the UTC. But servicenow consider the time provided in the query as user configured timezone and hene it first converts it to the UTC time and then process the request.

Thus, We think that the encoded query won't help us.