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.

[Mike Hashemi]

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))