question Re: Getting all records from a table in Developer forum

Getting all records from a table

mhashemi — Wed, 03 Jan 2024 22:04:17 GMT

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.

Re: Getting all records from a table

Sainath N — Wed, 03 Jan 2024 22:32:40 GMT

@mhashemi : 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

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

Re: Getting all records from a table

mhashemi — Thu, 04 Jan 2024 15:00:52 GMT

@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?

Re: Getting all records from a table

Sainath N — Thu, 04 Jan 2024 15:12:12 GMT

@mhashemi : 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.

Re: Getting all records from a table

mhashemi — Thu, 04 Jan 2024 15:21:49 GMT

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

Re: Getting all records from a table

mhashemi — Thu, 04 Jan 2024 15:45:18 GMT

For the record, since system.net.webclient is not returning any values in the response headers, I had to switch to Invoke-RestMethod in PowerShell 7 (which allows me to use the ResponseHeadersVariable parameter). Now I can do a loop to get all of the users:

$snowUsers = [System.Collections.Generic.List[PSObject]]::new() $limit = 100 $offset = 0 $headers = @{ '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)))))) 'Accept' = 'application/json' 'Content-Type' = 'application/json' } Do { $response = Invoke-RestMethod -ResponseHeadersVariable responseHeaders -Uri "https://<instance>.service-now.com/api/now/table/$userTableName`?sysparm_limit=$limit&sysparm_offset=$offset&sysparm_query=company%3D$ServiceNowCompany" -Headers $headers -Method Get Foreach ($item in $response.result) { $snowUsers.Add($item) } $offset += $limit } Until ($snowUsers.sys_id.Count -eq ($responseHeaders.'X-Total-Count' | Out-String))