functions/Get-PpeOdata.ps1
|
<# .SYNOPSIS Queries a Dataverse OData entity from Power Platform. .DESCRIPTION Sends a GET request to the Dataverse Web API OData endpoint and returns the matching records. Handles authentication, query filters, nextLink pagination, and automatic retries for throttled requests. .PARAMETER EnvironmentId Environment name or environment GUID (PPAC). .PARAMETER Entity The OData entity collection name to query, e.g. "accounts" or "systemusers". .PARAMETER ODataQuery Optional query string without the leading "?", e.g. '$filter=statecode eq 0'. .PARAMETER TraverseNextLink Follows "@odata.nextLink" pagination and accumulates every page in the result. .PARAMETER ThrottleSeed Random delay in seconds, between 1 and this value, after each page request. .PARAMETER AsExcelOutput Exports the retrieved records to an Excel file instead of the pipeline. .EXAMPLE PS C:\> Get-PpeOdata -EnvironmentId "ContosoEnv" -Entity "accounts" Retrieves every record from one OData entity. It uses -EnvironmentId to select the "ContosoEnv" environment. It uses -Entity to select the "accounts" entity set. .EXAMPLE PS C:\> Get-PpeOdata -EnvironmentId "ContosoEnv" -Entity "systemusers" -ODataQuery "`$filter=isdisabled eq false&`$select=fullname,systemuserid" Retrieves only enabled system users with two selected fields. It uses -EnvironmentId to select the "ContosoEnv" environment. It uses -Entity to select the "systemusers" entity set. It uses -ODataQuery to send the filter and select expressions. .EXAMPLE PS C:\> Get-PpeOdata -EnvironmentId "ContosoEnv" -Entity "accounts" -TraverseNextLink Follows pagination links until every page is returned. It uses -EnvironmentId to select the "ContosoEnv" environment. It uses -Entity to select the "accounts" entity set. It uses -TraverseNextLink to request all pages. .EXAMPLE PS C:\> Get-PpeOdata -EnvironmentId "ContosoEnv" -Entity "accounts" -TraverseNextLink -ThrottleSeed 3 Pauses between pages to reduce the risk of throttling. It uses -EnvironmentId to select the "ContosoEnv" environment. It uses -Entity to select the "accounts" entity set. It uses -TraverseNextLink to request all pages. It uses -ThrottleSeed 3 to wait up to three seconds between pages. .EXAMPLE PS C:\> Get-PpeOdata -EnvironmentId "ContosoEnv" -Entity "accounts" -AsExcelOutput Exports every retrieved record to an Excel file. It uses -EnvironmentId to select the "ContosoEnv" environment. It uses -Entity to select the "accounts" entity set. It uses -AsExcelOutput to write the records to a workbook. .NOTES Author: Mötz Jensen (@Splaxi) #> function Get-PpeOdata { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns", "", Justification = "OData is the name of the protocol.")] [CmdletBinding(DefaultParameterSetName = 'Default')] [OutputType([PSCustomObject[]])] param ( [Parameter (Mandatory = $true)] [ValidateNotNullOrEmpty()] [string] $EnvironmentId, [Parameter(Mandatory = $true, ParameterSetName = "Default")] [Parameter(Mandatory = $true, ParameterSetName = "NextLink")] [ValidateNotNullOrEmpty()] [string] $Entity, [string] $ODataQuery, [Parameter(Mandatory = $true, ParameterSetName = "NextLink")] [switch] $TraverseNextLink, [Parameter(ParameterSetName = "NextLink")] [int] $ThrottleSeed, [switch] $AsExcelOutput ) begin { $envObj = Get-Environment -EnvironmentId $EnvironmentId | Select-Object -First 1 $baseUri = $envObj.PpacEnvUri.TrimEnd('/') $objSecureToken = Get-BapAccessToken -ResourceUrl $baseUri $tokenWebApiValue = ConvertFrom-SecureString -AsPlainText ` -SecureString $objSecureToken $hashHeadersWebApi = @{ "Authorization" = "Bearer $($tokenWebApiValue)" } } process { if (Test-PSFFunctionInterrupt) { return } [System.UriBuilder] $odataEndpoint = $baseUri $odataEndpoint.Path = "/api/data/v9.2/$Entity" if (-not ([string]::IsNullOrEmpty($ODataQuery))) { $odataEndpoint.Query = "$ODataQuery" } [System.Collections.Generic.List[System.Object]] $resArray = @() $localUri = $odataEndpoint.Uri.AbsoluteUri $429Attempts = 0 $maxRetries = 3 do { $429Retry = $false try { $resGet = Invoke-RestMethod -Method Get ` -Uri $localUri ` -Headers $hashHeadersWebApi $429Attempts = 0 $resArray.AddRange($resGet.Value) if ($resGet.'@odata.nextLink') { $localUri = $resGet.'@odata.nextLink' } if ($ThrottleSeed) { $tmpThrottleDelay = Get-Random -Minimum 1 ` -Maximum $ThrottleSeed Start-Sleep -Seconds $tmpThrottleDelay } } catch [System.Net.WebException] { if ($_.exception.response.statuscode -eq 429) { $429Retry = $true $429Attempts++ $retryWaitSec = $_.exception.response.Headers["Retry-After"] if (-not ($retryWaitSec -gt 0)) { $retryWaitSec = 10 } Write-PSFMessage -Level Host ` -Message "Hit a 429 status code. Will wait for: <c='em'>$retryWaitSec</c> seconds before trying again. Attempt (<c='em'>$429Attempts</c> of <c='em'>$maxRetries</c>)" if ($429Attempts -ge $maxRetries) { Write-PSFMessage -Level Warning ` -Message "Reached maximum of <c='em'>$maxRetries</c> retry attempts due to throttling. Returning <c='em'>$($resArray.Count)</c> collected records." break } Start-Sleep -Seconds $retryWaitSec } else { Throw } } } while ($429Retry -or ($TraverseNextLink -and $resGet.'@odata.nextLink')) if ($resArray.Count -gt 0) { $res = $resArray.ToArray() } if ($AsExcelOutput) { $res | Export-Excel -WorksheetName "Get-PpeOdata" return } $res } end { } } |