LargeServiceNowExportwithPowerShell

SNPowerShellTableAPIExport.ps1

<#
Since ServiceNow will only allow 10,000 records to be exported by default we need to use pagingation for large exports.
This script will query the table api for the selected table and loop through blocks of 10,000 until all records are retrived.
It then converts the JSON payload to a CSV
#>

# Set ServiceNow credentials
$username = "admin"
$password = "pass"
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object System.Management.Automation.PSCredential($username, $securePassword)
$fields = "name,asset_tag,manufacturer,asset,company,serial_number,model_id,assigned_to,os_domain,os,os_version,os_service_pack,dns_domain,disk_space,ram,cpu_manufacturer,cpu_type,cpu_speed,cpu_count,cpu_core_count,short_description,sys_id"

# Convert the comma-separated fields to an array
$fieldArray = $fields -split ','

# ServiceNow API endpoint URL
$instanceName = "dev12345"
$tableName = "cmdb_ci_computer"
$apiUrl = "https://${instanceName}.service-now.com/api/now/table/$($([uri]::EscapeDataString($tableName)))"

# Number of records to retrieve per request
$batchSize = 10000

# Initialize an array to store all records
$allRecords = @()

# Flag to indicate if there are more records
$moreRecords = $true
$offset = 0

# Loop until there are no more records
while ($moreRecords) {
    # Construct the query URL with offset and limit parameters
    $queryUrl = $apiUrl + "?sysparm_offset=$offset&sysparm_limit=$batchSize&sysparm_fields=" + $fields + "&sysparm_display_value=true"

    # Make the API request using the provided credentials
    $response = Invoke-RestMethod -Uri $queryUrl -Method Get -Credential $credentials -ContentType "application/json"

    # Check if there are more records
    $moreRecords = $response.result.Length -eq $batchSize

    # Append records to the array
    $allRecords += $response.result

    # Output progress
    Write-Host "Retrieved $($allRecords.Count) records"

    # Increment offset for the next batch
    $offset += $batchSize
}

# Export the results to a CSV file with ordered columns
$csvFilePath = "C:\temp\" + $tableName + ".csv"
$allRecords | ForEach-Object { $_ | Select-Object $fieldArray } | Export-Csv -Path $csvFilePath -NoTypeInformation -Force

Write-Host "Export to CSV completed. Results saved to $csvFilePath"