UDTables don't paginate & can't export data

PowerShell Universal
1

Hey there, I’m having a problem with UDTable where tables don’t paginate, instead they show all rows on a single page.

The tables show the correct count of rows at the bottom of the table.

Additionally, trying to export the table to any format (csv, xml, xls) etc. does not work.

I think it has something to do with server side processing - this affects both version 1.5.8 and 2.6.2 (I tried upgrading to resolve).

The problem affects all of my tables with completely different data sources and approaches (hashtables vs arrays etc).

Pagination and export works fine as soon as I remove any processing and simply use static dummy data. I’ve also tried completely blank/new installs of UD after deleting the database.

Running on IIS.

This has been bugging me for years, can anyone help?

An example of one of my simpler tables is included below - refuses to export or paginate (usually returns around 500 rows which are all displayed on a single page).

Any guidance would be greatly appreciated.

$Pages += New-UDPage -Name "Device Manager" -Content {
    New-UdTable -Title 'Device Manager' -showsearch -pagesize 10 -export -columns @(
        New-UdTableColumn -Property Name -Title 'Device Name' -IncludeInSearch
        New-UdTableColumn -Property Description -Title 'Description' -IncludeInSearch
        New-UdTableColumn -Property ID -Title 'Device ID' -IncludeInSearch
        New-UdTableColumn -Property DeviceID -Title 'Device MAC' -IncludeInSearch        
    ) -LoadData {
        $TableData = ConvertFrom-Json $Body
        $user = 'admin'
        $pass = 'pass'
        $pair = "$($user):$($pass)"
        $encodedCreds = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes($pair))
        $basicAuthValue = "Basic $encodedCreds"
        $Headers = @{
            Authorization = $basicAuthValue
        }
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 
        $response = Invoke-WebRequest -Uri 'https://URLToSomeJSONData' -Headers $Headers
        
        $responseformatted = $response.Content | ConvertFrom-Json
        
        $responseformattedobjects = $responseformatted.objects
        
        $data = $(Foreach ($formattedobject in $responseformattedobjects) {
                    @(
                        @{
                            Name        = $formattedobject.Name
                            ID          = $formattedobject.Id
                            DeviceID    = $formattedobject | Select-Object -ExpandProperty Address
                            Description = $formattedobject.Description
                        }
                    )
            }
        )   

        $data | Out-UDTableData -Page $TableData.page -TotalCount $Data.Count -Properties $TableData.properties
    }
}
2

I’ll also mention that I’ve tried literally every combination of -pagination -pagesize etc.

3

With server-side processing, you need to handle processing the pagination yourself.

For example, from this blog post, we are paging using offsets and takes in SQL. Notice how in the SQL we are using OFFSET and ROWS FETCH NEXT to select a page of data.

    $PageSize = $TableData.PageSize 
    # Calculate the number of rows to skip
    $Offset = $TableData.Page * $PageSize

    $Count = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database 'podcasts' -Query "SELECT COUNT(*) as count FROM shows"

    $Data = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database 'podcasts' -Query "SELECT * FROM shows ORDER BY $orderBy $orderdirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY" | ForEach-Object {
        @{ 
            name = $_.name 
            host = $_.host
        }
    } 
    $Data | Out-UDTableData -Page $TableData.page -TotalCount $Count.Count -Properties $TableData.properties

Since you are limiting the data returned from the REST call, it won’t do any paging on the client side since it’s expecting the server to handle it.

Is there a reason to use this over a client-side table (e.g. -Data) that does automatic paging in the browser?

1 Like
4

Thanks so much @adam that makes sense. With client-side processing, is the actual PowerShell to get the data (i.e. that Invoke-Webrequest) still run on the server? Or would that processing be offloaded to the client?

For example would the web request appear to come from the server still, or the client accessing the UD webpage?

5

It’s still running on the server but return all the data down to the client. Even if it isn’t displaying all the data (because of paging etc), it will download all the data to the client and then JavaScript performs the paging, filtering and sorting. This is inefficient for backends like SQL where you can page, sort and filter within the database. It’s usually just a problem for very large data sets (over 100k records etc) Since you aren’t doing that with your REST call, I’d suggest avoiding server-side processing because it makes it much more complicated.

6

Awesome! So fundamental misunderstanding on my part. Will try with client side. Thanks again.

1 Like