Getting all records from a table

[Mike Hashemi]

I am trying to get all records from the customer_contact table, using the Table API. I am only getting 6-10k records (out of about 40k) before the command times out:

"error": {
    "message":"Transaction cancelled: maximum execution time exceeded","detail":"maximum execution time exceeded Check logs for error trace or enable glide.rest.debug property to verify REST request processing"
}

I have a meeting with one of our platform admins to discuss the timeout, but I wonder if there is another way I should be approaching this.

For the record, I am using PowerShell to retrieve the records:

$ServiceNowCredential = Get-Credential
$userTableName = 'customer_account'
$ServiceNowCompany = '<account sys_id>'
$snowUsers = [System.Collections.Generic.List[PSObject]]::new()
$webClient.Headers = $null
$webClient.Headers.Add('Authorization', ('Basic {0}' -f [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $ServiceNowCredential.UserName, [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($ServiceNowCredential.Password)))))))
$webClient.Headers.Add('Content-Type', 'application/json')
$webClient.Headers.Add('Accept', 'application/json')
$response = $webClient.DownloadString("https://<instance>.service-now.com/api/now/table/$userTableName`?sysparm_query=company%3D$ServiceNowCompany")

Foreach ($item in @($response | ConvertFrom-Json).result) { $snowUsers.Add($item) }

Thanks.

[Sainath N · Accepted solution]

@Mike Hashemi : As the record count is high, we should be handling these calls with sysparm_limit and sysparm_offset in the API. Please refer to the HI articles below that explain in detail the issue and the implementation.

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

Similar Issue Article: https://www.servicenow.com/community/developer-forum/need-to-understand-quot-sysparm-limit-and-syspa...

Please mark this as correct answer and helpful if it resolved, or mark this helpful if this help you to reach towards solution.

[Mike Hashemi]

@Sainath N, that seems like a useful solution, but is not working as expected.

With the following script, I am getting back 2486 records. It seems that ServiceNow is ignoring my 100-record limit:

$ServiceNowCredential = Get-Credential
$userTableName = 'customer_account'
$ServiceNowCompany = '<account sys_id>'

$snowUsers = [System.Collections.Generic.List[PSObject]]::new()
$limit = 100
$offset = 0

Write-Host ('Limit = {0} and offset = {1}' -f $limit, $offset)

$webClient.Headers = $null
$webClient.Headers.Add('Authorization', ('Basic {0}' -f [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $ServiceNowCredential.UserName, [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($ServiceNowCredential.Password)))))))
$webClient.Headers.Add('Content-Type', 'application/json')
$webClient.Headers.Add('Accept', 'application/json')

write-host ('URL = {0}' -f "https://<instance>.service-now.com/api/now/table/$userTableName`?sysparam_limit=$limit&sysparam_offset=$offset&sysparm_query=company%3D$ServiceNowCompany")

$response = $webClient.DownloadString("https://<instance>.service-now.com/api/now/table/$userTableName`?sysparam_limit=$limit&sysparam_offset=$offset&sysparm_query=company%3D$ServiceNowCompany")

Foreach ($item in @($response | ConvertFrom-Json).result) { $snowUsers.Add($item) }

$snowUsers.count

Since I am only running through the script once, I expect that $snowUsers.count would be 100, not 2486. Am I missing something?

[Sainath N]

@Mike Hashemi : Looking into your PowerShell script that you pasted, I see the typo in sysparm. I see that you are using sysparam instead of sysparm. Please correct them and test it.

Please mark this as correct answer and helpful if it resolved, or mark this helpful if this help you to reach towards solution.

[Mike Hashemi]

Oops, found a typo. It is "sysparm", not "sysparam".