Pull Table data from ServiceNow Instance using REST API

# ServiceNow Instance Configuration
$servicenowInstance = @{
    Name            = "your_instance_name" # ServiceNow Instance Name
    APIVersion      = "api/now/v1" # ServiceNow API Version
    ClientID        = $env:ServiceNowClientID # To be set in the environment
    ClientSecret    = $env:ServiceNowClientSecret # To be set in the environment
    Username        = $env:ServiceNowUsername # To be set in the environment
    Password        = $env:ServiceNowPassword # To be set in the environment
    ItemsPerAPICall = 100 # Number of items to be retrieved in a single API call
}

# Get Acccess Token from ServiceNow
Function GetAccessToken () {
    try {
        Write-Host "Getting Access Token" -ForegroundColor Green 
    
        $headers = @{
            "Content-Type" = "application/x-www-form-urlencoded"
            "Accept"       = "application/json"
        }

        $body = @{
            grant_type    = "password"
            username      = $servicenowInstance.Username 
            password      = $servicenowInstance.Password
            client_id     = $servicenowInstance.ClientID
            client_secret = $servicenowInstance.ClientSecret
        }
    
        $tokenEndpoint = "https://$($servicenowInstance.Name).service-now.com/oauth_token.do"
        $response = Invoke-RestMethod -Uri $tokenEndpoint -Method Post -Headers $headers -Body $body

        if ($response.access_token) {
            $accessToken = $response.access_token
            Write-Host "Here is the Access Token" -ForegroundColor Green
            Write-Host "$accessToken" -ForegroundColor Green -BackgroundColor Green
            return $accessToken
        }
        else {
            Write-Host "There is a problem in getting Access Token. Error: $($response.status) - $($response.error_description)" -ForegroundColor Red
            return $null
        }
    }
    catch {
        throw "There is a problem in getting Access Token. Error: $($_.Exception.Message)"
    }
}

# Get Table Data from ServiceNow
Function GetTableData () {
    [CmdletBinding()]
    param (
        [Parameter()][string]$AccessToken, [Parameter()][string]$TableName, [Parameter()][string]$Query
    )
    try {
        Write-Host "Getting $($TableName) Table Data" -ForegroundColor Green

        $headers = @{
            "Accept"        = "application/json"
            "Authorization" = "Bearer $($AccessToken)"
        }

        $statsEndpoint = "https://$($servicenowInstance.Name).service-now.com/$($servicenowInstance.APIVersion)/stats/$($TableName)?sysparm_query=$($Query)&sysparm_count=true"
        $response = Invoke-RestMethod -Uri $statsEndpoint -Method Get -Headers $headers
        [int]$numberOfRecords = $($response.result.stats.count)
        Write-Host "Number of Records found in $($TableName): $($numberOfRecords)" -ForegroundColor Green
        $iterations = [Math]::Ceiling($numberOfRecords / $($servicenowInstance.ItemsPerAPICall))
        Write-Host "Number of iterations required to get all the records from this table : $($iterations)" -ForegroundColor Green

        $tableData = $null

        for ($i = 0; $i -lt $iterations; $i++) {
            $offset = $($i * $($servicenowInstance.ItemsPerAPICall))
            $query = "$($Query)&sysparm_display_value=all&sysparm_limit=$($servicenowInstance.ItemsPerAPICall)&sysparm_offset=$($offset)"
            $tableDataEndpoint = "https://$($servicenowInstance.Name).service-now.com/$($servicenowInstance.APIVersion)/table/$($TableName)?sysparm_query=$($query)"
            $response = Invoke-RestMethod -Uri $tableDataEndpoint -Method Get -Headers $headers
            $tableData += $($response.result)
        }

        return $tableData
    }
    catch {
        Write-Host "There is a problem in getting $($TableName) Table Data. Error: $($_.Exception.Message)" -ForegroundColor Red
    }
}

# Print Table Data
Function PrintTableData () {
    
    param (
        $TableData
    )

    foreach ($record in $TableData) {
        $recordNumber = $record.number.display_value
        $recordSysId = $record.sys_id.display_value
        $first3CharsOfNumber = [string]$recordNumber.SubString(0, 3)
        
        switch ($first3CharsOfNumber) {
            "CHG" { $tableName = "change_request"; break }  
            "INC" { $tableName = "incident"; break }
            "TAS" { $tableName = "sc_task"; break }
            default { $tableName = $null; break }
        }

        if ($tableName) {
            $recordLink = "https://$($servicenowInstance.Name).service-now.com/nav_to.do?uri=$($tableName).do?sys_id=$($recordSysId)"
            Write-Host "Record Number: $($recordNumber) - Record Link: $($recordLink)" -ForegroundColor Green
        }
        else {
            Write-Host "Unable to create Record Link for $($recordNumber)" -ForegroundColor Red
        }
    }
}

# ****** Main Script Starts ******

try {
    $accessToken = GetAccessToken

    if ($accessToken) {
        $executionTime = [DateTime]::Now
        $oneYearBefore = $executionTime.AddYears(-1).ToString("yyyy-MM-dd HH:mm:ss")
        $tableData = GetTableData -AccessToken $accessToken -TableName "incident" -Query "sys_created_on>=$($oneYearBefore)^assigned_to=^ORDERBYsys_created_on"
        
        if ($tableData.Count -gt 0) {
            PrintTableData -TableData $tableData
            Write-Host "Successfully got the data" -ForegroundColor Green
        }
        else {
            Write-Host "There is no data in the table" -ForegroundColor Yellow
        }
    }
    else {
        Write-Host "Access Token is empty/null" -ForegroundColor Red
    }
}
catch {
    Write-Host "There is a problem in running this script. Error: $($_.Exception.Message)" -ForegroundColor Red    
}

# ****** Main Script Ends ******