How can I export to a json file with more then 10000 records?

[Lennart Wallman]

I try to use a powershell script to get data from a couple of tables starting at Year 2016, Ive tryed a couple of things but still 10 000 records in the jsonfile, tryed with &sysparm_limit=30000 or &glide.ui.export.limit=100000?

$user = "userName"
$pass = "usersPassword"
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user, $pass)))
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add('Authorization',('Basic {0}' -f $base64AuthInfo))
$headers.Add('Accept','application/json')

$date = Get-Date -Date "2016-09-01 00:00:00"
$currentDate = Get-Date -format "yyyy-MM-dd hh:mm:ss"
while($date -le $currentDate) {
$date = $date.Addminutes(60)
$DateE = $date.Addminutes(-60)
$tempDate = "'" + $date.toString("yyyy-MM-dd") + "'"
$tempTimeEnd = "'" + $date.toString("HH:mm:ss") + "'"
$tempTimeStart = "'" + $dateE.toString("HH:mm:ss") + "'"
$tempDate2 = "'" + $date.toString("yyyy-MM-dd hh:mm:ss") + "'" 
$uri = "https://tellus.sll.se/api/now/table/sys_user?JSON&sysparm_display_value=true&sysparm_suppress_pagination_header=true&glide.ui.export.limit=100000&sysparm_query=sys_created_onBETWEENjavascript:gs.dateGenerate("+$tempDate +",+ $tempTime +)@javascript:gs.dateGenerate("+$tempDate+",$tempTimeEnd)"
$method = "get"
{request.body ? "$body = \"" :""}"}
$response = Invoke-WebRequest -Headers $headers -Method $method -Uri $uri
$enddate = ($date).tostring("yyyyMMdd_")
$Time = ($date).tostring("hh_mm")
$filename = 'D:\ImportFiler\Tellus\sys_user\'
$filename = $filename + $date.toString("yyyy/MM/dd HH mm ss")
$filename = $filename + '_sys_user.json'
#Write-Output $uri
#Write-Output $filename #$response.content | Out-File $filename
Write-Output $response.content | Out-File $filename
}

[mamann · Accepted solution]

You can look at the following link which discusses the Table API in ServiceNow. Table API

It looks like the sysparm_offset and sysparm_limit are the attributes you'll want to dig into to get the data.

[Lennart Wallman]

Tried that now but i just get 10000 first rows even when I set the offsett to 10000?

[Lennart Wallman]

The solution was way more simple than I thought:

$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user, $pass)))
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add('Authorization',('Basic {0}' -f $base64AuthInfo))
$headers.Add('Accept','application/json')
for ($i = 0; $i -lt 5000000; $i += 5000) {
$i

$uri = "https://tellus.sll.se/api/now/table/sys_user?sysparm_display_value=true&sysparm_suppress_pagination_header=true&sysparm_limit=5000&sysparm_offset=" +$i

$method = "get"
{request.body ? "$body = \"" :""}"}
$response = Invoke-WebRequest -Headers $headers -Method $method -Uri $uri
$enddate = (Get-Date).tostring("yyyyMMdd_")
$Time = (Get-Date).tostring("hh_mm")
$filename = 'D:\ImportFiler\Tellus\sys_user\'
$filename = $filename + $enddate + '_' + $i + '_sys_user.json'

Write-Output $response.content | Out-File $filename

$Path = "D:\ImportFiler\Tellus\sys_user"
Get-ChildItem $path -Filter *.json -recurse -file | ? {$_.length -lt 1024} | % {break}
}